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 :

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Hi Michael,

Am Fri, 17 May 2013 04:25:06 -0700 (PDT) schrieb :

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Fri, 17 May 2013 04:20:55 -0700 (PDT), wrote:

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.