Adding Spaces into a column

[snoopsterg]

Guys,

You will probably know this and maybe think its easy, but i just cant think how to do this.

I have a column with sequential numbers twelve digits long, what i need is to either add a space after each 2 digits OR i need to move each set of 2 digits to its own column.

Am i just a bungling idiot or is this as difficult as i am making it for myself


Cheers for the help

Steve

 

[erik.van.geit]

Hi, snoopsterg
Welcome to the Board !!!!!

move each set of 2 digits to its own column

see menu DATA / Text To Columns

in first box select option "fixed width" (don't know if this is correct english)
from there you can follow the instructions

kind regards,
Erik

 

[JamieDuncan]

You could use these formulae in seperate cells

=MID(A1,1,2)
=MID(A1,3,2)
=MID(A1,5,2)
=MID(A1,7,2)
=MID(A1,9,2)
=MID(A1,11,2)

=MID('Cell containing 12 digits','starting digit','number of digits to take')

You could then concatenate the cells back together with spaces between.

 

[snoopsterg]

Thanks, will try that but it looks good, never thought of that simple is always best, and people like to call me simple


Steve

 

[erik.van.geit]

Hi, Jamie,

if you want to concatenate them again, you could use a single formula

=MID(A1,1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,2)&" "&MID(A1,7,2)&" "&MID(A1,9,2)&" "&MID(A1,11,2)

 

[snoopsterg]

Thanks for the reply both of you,

Both ways work except the first two columns that have got 01 and 03 in them, they both lose 0.

I have at the moment column A = 010312213041

when i use the either the text to columns or the =MID formula i have a problem to overcome.

with Text to columns i get

A B C D E F
1 3 12 21 30 41

so when i concatenate to add the spaces it misses 0 from 01 and the 0 from 03.

what i am doing is a mailshot with sequential lottery numbers for a draw so needs to be formatted with the zero

 

[JamieDuncan]

format destination cells as text cells

 

[erik.van.geit]

Hi,

the "straight" option to get the zeros is to "follow the instructions" in the Text To Columns Dialog: in the last box there is an option "datatype per column"
choose "Text"

best regards,
Erik