Concatenating NUMBERS only

[Ellenisobel]

Hi Guys,

I am stuck trying to concatenate 2 numbers cells, retaining the number format - is there anyway to do this or does excel always convert the concat to text?

Example of what I am trying to do:

In one column I have a mixture of 4 digit and 5 digit numbers. All of these need to be 5 digits, so I filter out the existing 5 digits and am then trying to add a 9 in front of the 4 digiters.
One way of doing this is to just add 90000, but ideally would like the concatenate to work.

Then in the new column with the concatenate, I do a simple IF statement to reference the original cell if blank.


Sorry if that doesn't make sense!!!

Thanks!

p.s. I am operating on Excel 2013

 

[oldbrewer]

if 9 and 1234 are the numbers concatenate gives 91234 as text so you need =value(concatenate(...........

 

[Ellenisobel]

THANK YOU!!! oldbrewer, that nailed it.
Amazing what you forget when you don't use it regularly

Cheers.

 

[oldbrewer]

this 70 year old brain thanks you too...........