How can I add a space between every digit in a cell?

HamWaggle

New Member
Joined
Apr 22, 2011
Messages
2
Basically I have one cell, call it A1, with over 1200 digits in it. All I need to do is put a space between each one so I can eventually use text to columns via delimited to get each individual number in its own cell.

I consider myself a master of excel and this simple thing stumped me and made me angry. Normally I would use a LEFT(A1, 1) and then string concatenate & " " & into it, but with so many digits this doesn't work. I have also tried a replace function with no luck....eventually I was able to solve my problem by using the find and replace and going though and doing

FIND: "1"

REPLACE WITH: "1 "
(note the parentheses are just to show you the space is added)

then...

FIND: "2"

REPLACE WITH: "2 "

all the way until I replaced all 10 digits.

This solved my problem, but I still can't believe how amateur I felt not knowing a quick and easy way to do this....any suggestions ? I'm sure the first answer is going to be so easy I cry at my stupidity....thx
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Perhaps a UDF: in a standard module

Code:
Function addspace(r As Range) As String
Dim s As String, i As Long
s = r.Value
For i = 1 To Len(s)
    addspace = addspace & Mid(s, i, 1) & " "
Next i
addspace = Trim(addspace)
End Function
To use:

=addspace(A1)
 

HamWaggle

New Member
Joined
Apr 22, 2011
Messages
2
very nice I copied this into Module1 and it worked perfectly, now only if I could learn to code in VBA...hmm....do you think there is any simple current function that can complete this or is my stupidity justified? Because I feel much more confident in my skills now if my MacGyver way of getting this done is the only non-coding way....
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
I don't know a formula way of doing this but formulas are not my strong point.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Perhaps:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">123456789012345678901234567890</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A2</th><td style="text-align:left">=MID(<font color="Blue">$A$1,ROWS(<font color="Red">$A$1:$A1</font>)-ROW(<font color="Red">$A$1</font>)+1,1</font>)</td></tr></tbody></table></td></tr></table><br />

Formula in A2 is copied down as far as required
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
A)
If you were to do it with a UDF, instead od looping through ever character in every cell I would consider this UDF by HOTPEPPER instead:
http://www.mrexcel.com/forum/showpost.php?p=1924281&postcount=4

B)
I don't know how this would go with so many digits but here is a direct formula approach instead of using Text to Columns. Formula copied across and down.

Excel Workbook
ABCDEFGH
1123
1
23
2123456123456
32356823568
Digits to Columns
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi

Another udf option:

Code:
Function addspace(s As String) As String
addspace = Trim(Replace(StrConv(s, vbUnicode), ChrW(0), " "))
End Function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
Hi

Another udf option:

Code:
Function addspace(s As String) As String
addspace = Trim(Replace(StrConv(s, vbUnicode), ChrW(0), " "))
End Function
pgc, you never cease to amaze me! Another Bookmarked post. :)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Thanks Peter. I should have added the limitations of the code. It works for the normal text where you use the characters in your ansi page (with codes <= 255). It will not work if you use Unicode characters with codes bigger than 255.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
Since we appear to be working with just one cell and we are only adding the spaces to subsequently use Text To Columns, could we skip the add spaces and do the Text To Columns instead? Perhaps this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Text_To_Cols()<br>    <SPAN style="color:#00007F">Dim</SPAN> ary()<br>    <SPAN style="color:#00007F">Dim</SPAN> Num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Num = Len(Range("A1").Value) - 1<br>    <SPAN style="color:#00007F">ReDim</SPAN> ary(0 <SPAN style="color:#00007F">To</SPAN> Num)<br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> Num<br>        ary(i) = Array(i, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br>    Range("A1").TextToColumns Destination:=Range("B1"), _<br>        DataType:=xlFixedWidth, FieldInfo:=ary<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,752
Members
406,496
Latest member
Arlind Elezi

This Week's Hot Topics

Top