How do I write a script to add space between numbers in a column?

[Guest]

I have a file with a column that contains numbers that have six digits. I
need to add a space between the last two digits in this very long column of
numbers. Is there an efficient way of doing this? Example: 120100 needs to
be 1201 00

 

[Dave Peterson]

As long as they're really numbers, you could use a custom format:

Select the column:
format|Cells|number tab|Custom category
0000 00

The value that you see in the formula bar will still be the number--only the
display has changed.

 

[Guest]

The easiest way I know is to enter a formula in the next column to reformat
the data. Assume the data is in cell A1. The formula would look like this.
Copy the formula to all remaining cells in the new out put column

=LEFT(A1,4) & " " & RIGHT(A1,2)

 

[David Biddulph]

Format cells/ custom/ 0 00
Or, if you want to use the result somewhere else in that format, =TEXT(A1,"0
00")

 

[Gord Dibben]

You could custom format to create the look of 1201 00

Custom> 0000 00

To change them to text.

=LEFT(A1,4) & " " & RIGHT(A1,2) entered in B1

Double-click to copy down.

When happy, copy>paste special>values>OK>Esc and delete column A


Gord Dibben MS Excel MVP