Formula to add two spaces into a number stored as text

  • Thread starter Thread starter michael
  • Start date Start date
M

michael

Hello. I'm trying to do as the title says. The numbers must be arranged "0000 000 0000" and I'm trying to get that format applied to all the numbers in a column. The only problem is there are about 3000 numbers in that column so I can't do it manually.

From searching around I've found the following formula =CONCATENATE(LEFT(H2,4)," ",RIGHT(H2,3))

This seems to be what I'm looking for but I'm having trouble adapting it for my purposes. Anyone who can help me out here?
 
Hi Micheal,

Am Fri, 17 May 2013 04:20:55 -0700 (PDT) schrieb (e-mail address removed):
Hello. I'm trying to do as the title says. The numbers must be arranged "0000 000 0000" and I'm trying to get that format applied to all the numbers in a column. The only problem is there are about 3000 numbers in that column so I can't do it manually.
Click to expand...

try custom numberformat:
0000 000 0000

Regards
Claus Busch
 
Hi Michael,

Am Fri, 17 May 2013 04:25:06 -0700 (PDT) schrieb (e-mail address removed):
I have tried that already, doesn't seem to do anything.
Click to expand...

click in column header => Format "General" => TextToColumns => Fixed
Width => Finish => Custom numberformat 0000 000 0000

If that not works try:
=LEFT(A1,4)&" "&MID(A1,5,3)&" "&RIGHT(A1,4)


Regards
Claus Busch
 
Back
Top