How to get defined format to carry over into CONCATENATE function

[davedmsu]

I have a cell format of '0.0.0' set for column A.


In column B I have a CONCATENATE function


=CONCATENATE('the number: ',A1')


If I type '2.1.2' into A1, then B1 shows 'the number: 2.1.2'


If I type '2' into A1 it is re-formatted (properly) to 2.0.0, but the concatenate function shows 'the number: 2'


Is there a way to force the format to show up in the concatenate function?

 

[oldchippy]

Hi,


I hope I've understood you correctly, you say column A is formatted "0.0.0" I take that to be a custom format as 0.0.0 and the underligning number in A1 is 212


And if "the number" you wish to CONCATENATE is in say F1, then try this in B1 copied down


=CONCATENATE($F$1&TEXT(IF(A1<10,TEXT(A1,"0.0.0"),IF(A1<100,"0."&LEFT(A1,1)&"."&RIGHT(A1,1),LEFT(A1,1)&"."&MID(A1,2,1)&"."&RIGHT(A1,1))),"0.0.0"))

 

[VaraK]

Perhaps:


=CONCATENATE("The number: ",TEXT(A1,"0.0.0"))


Hope this helps.