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

[HamWaggle]

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

 

[VoG]

Perhaps a UDF: in a standard module

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]

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]

I don't know a formula way of doing this but formulas are not my strong point.

 

[Richard Schollar]

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]

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
	A	B	C	D	E	F	G	H
1	123	1	2	3
2	123456	1	2	3	4	5	6
3	23568	2	3	5	6	8
Digits to Columns

 

[pgc01]

Hi

Another udf option:

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

 

[Peter_SSs]

Hi

Another udf option:

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]

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]

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?

Sub Text_To_Cols()
  Dim ary()
  Dim Num As Long, i As Long
  
  Num = Len(Range("A1").Value) - 1
  ReDim ary(0 To Num)
  For i = 0 To Num
    ary(i) = Array(i, 1)
  Next i
  Range("A1").TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, FieldInfo:=ary
End Sub