Help with CONCATENATE formula

Beddy Boy

New member
Joined
Feb 1, 2017
Messages
48
Reaction score
0
Points
0
Excel Version(s)
Excel 365
I wonder if anyone can help.

I have a table where a column of cells have a concatenate formula based on the value of 2 cells separated by a decimal point.

=CONCATENATE(B$5,".",Q3)

I wish to have the cells empty if there is no value in B5 and Q3. Obviously at present the columns are full of decimal points.

Is this possible?

Thanks in advance!
 
Sorry. What I really meant was that when the cell Q3 is empty the columns contain the value in B5 and a decimal point. Is there a way to have these cells blank when the cell in Q3 is empty?

Thanks again
 
Please see attached file
 

Attachments

  • concatenate.xlsx
    9.6 KB · Views: 5
Try this instead:

=B$5&IF(Q3="","","."&Q3)

To ensure that Excel reads the result as a number:

=VALUE(B$5&IF(Q3="","","."&Q3))
 
Thanks Ali.

However, it still shows numbers ie 6 (from B5) when I populate down...
 
Yes, and? If that's not what you want, then you need to be more explicit.

Maybe this?

=VALUE(IF(AND(B$5="",Q3=""),"",B$5&"."&Q3))
 
Thanks again Ali.

Sorry I may not have been clear enough.

I have attached another file.

I want the cells from A10 to A21 to be blank. If more numbers are added in col Q then this is then reflected in col A.

Thanks,

Wayne
 

Attachments

  • concatenate.xlsx
    9.6 KB · Views: 11
The attachment is useless - you need to mock up what you want. Showing us what you don’t want again (it’s the sane as before) does not move us forward.
 
Sorry for the confusion Ali.

Cell B5 is an patient ID number.

Q3 downwards are student ID numbers.

What I wish to do is simply enter the patient ID in cell B5 and a list of student IDs from cells Q3 downwards. The results appear in cells in A7 downwards giving me the patent and student IDs separated by a full stop.

However, when there are less than the full allocation of students, as in the attached sheet, I am left with cells that show 6.0

In simplistic terms I am after a formula that says "if there is no number after the "." then show the cell as blank..

Hope this clarifies things.

Thanks again.

Wayne
 
Last edited:
Mock it up, please. Until you do, no more guessing from me.

In simplistic terms I am after a formula that says "if there is no number after the "." then show the cell as blank..

That's what I gave you in post #6.
 
Last guess:

=IFERROR(VALUE(IF(AND(B$5="",Q3=""),"",IF(Q3="","",B$5&"."&Q3))),"")
 

Attachments

  • Concatenate AliGW.xlsx
    9.4 KB · Views: 5
Brilliant Ali!

Thanks very much that appears to work.

One last question though if I may..

Is there anyway to format the numbers in col A. As you can see if I format as a number to 1 decimal place the double digit numbers are incorrect - ie 6.1 instead of 6.12
Or if I format to 2 decimal places I get 6.30 instead of 6.3
Or if I format as text, it fixes the above but I then get 6.1 instead of 6.10

Any ideas??

Thanks again for your time.
 

Attachments

  • Concatenate AliGW.xlsx
    9.4 KB · Views: 5
Format as a custom format 0.###
 
Further to Bob's suggestion, set the column formatting to GENERAL, then use this formula:

=IFERROR(VALUE(TEXT(IF(AND(B$5="",Q3=""),"",IF(Q3="","",B$5&"."&Q3)),"0.###")),"")

or just remove the VALUE statement:

=IFERROR(IF(AND(B$5="",Q3=""),"",IF(Q3="","",B$5&"."&Q3)),"")
 
Thank you so much.

Exactly what I was after.

Do I need to mark this thread as resolved or anything now?
 
Back
Top