Formula to add two spaces into a number stored as text

[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?

 

[Claus Busch]

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.

try custom numberformat:
0000 000 0000

Regards
Claus Busch

 

[michael]

I have tried that already, doesn't seem to do anything.

 

[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 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

 

[Ron Rosenfeld]

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?

Number formatting of the cell won't work as the values, you write, are text.
The Data/ Text-to-columns wizard should convert these to numbers as Hans wrote.

If it doesn't, there are probably some hidden, non-numeric characters in the value. The most common, especially if the data comes from the Web or an HTML document, is the no-break backspace (nbsp)
You can remove this with

=substitute(h2,char(160),"")
And then work on those values.