Inserting Spaces

[john]

Hi all, I need to insert a Space between the last number and the second last
number in a column of about 1000 rows. Example 1234560 needs to be 123456 0.
Any help would be appreciated. TIA

 

[FinController]

John

Supposing your value is in cell A2, try this in cell B2
=LEFT(A2;LEN(A2)-1)&" "&RIGHT(A2;1)
It takes all characters starting from the left apart from one, inserts a
space " "and the ands the last 1 char at the right.
It should work for any length of string, but it will physically duplicate
your entries.

Alternatively, you can use the following custom format for your cells: #" "0
in the Format > Cells > Number dialog (Custom is the last choice in the
categories listbox).
It should also work for any number, but it does not really add a space, it
just show the original number as if it had a space...

hth
Robin
email: (e-mail address removed)

 

[J.E. McGimpsey]

One way:

in an empty column (say column C), enter


C1: =TEXT(A2,"#"" ""0")

Copy down to C1000. Select C1:C1000 and paste special/Values on to
A1:A1000. Delete C1:C1000

 

[friend]

hint: use your text to columns option

 

[J.E. McGimpsey]

Okay, I'll bite:

How would data/text to columns help here?