Inserting Spaces

  • Thread starter Thread starter john
  • Start date Start date
J

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
 
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)
 
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
 
Back
Top