Excel Concatenate formula problem

[Deano Casino]

Hi,

Im trying what i believe should be a simple formula, i have a list o
500 numbers e.g

25452820
25452821
25452822 and so on....

I need to insert a "0" infront of all the numbers for which i am usin
the formula =CONCATENATE(0,A1:A500) and this works fine.

My problem being i also need all the numbers to have a space after th
3rd digit????

e.g
025 452820
025 452821 and so on....

 

[routeram]

hi,

Easier solutions may exist but this worked for me.

Assuming your concatenated result is in C8.
=CONCATENATE(LEFT(C8,LEN(C8)-3)," ",RIGHT(C8,3))

Infact you can concatenate your "0" and do this in a single statemen
(C8 now contains number without leading zero)
=CONCATENATE("0",LEFT(C8,LEN(C8)-3)," ",RIGHT(C8,3))

Regards,
Ra

 

[Myrna Larson]

Do you have to change the cell contents for some reason? If not, you can
achieve both the leading 0 and the embedded space with a custom format:

000 000000

Select the cells, go to Format/Cells, select Custom in the Category dropdown
and enter the above text in the Type box.

 

[Guest]

Try this
=TEXT(CONCATENATE(0,A5),"000 000000")