How to add a space after each character

[falcios]

How do I add space after each character and digit in Excel 2010.

For example
ABC -> A B C
123 -> 1 2 3

Thanks in advance.

 

[alansidman]

Will there always be three characters?

 

[Rick Rothstein]

Give this UDF (user defined function)...

Function InsertSpaces(S As String) As String
  InsertSpaces = Trim(Replace(StrConv(S, vbUnicode), Chr(0), " "))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use InsertSpaces just like it was a built-in Excel function. For example,

=InsertSpaces(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[hiker95]

falcios,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider (based on your flat text display), that will run in the active worksheet, that is based on Rick Rothstein's function.

Sample raw data:

Excel 2007
	A
1	ABC
2	123
3	A1B2C3
4	1A2B3C
5
Sheet1

And, after the macro:

Excel 2007
	A
1	A B C
2	1 2 3
3	A 1 B 2 C 3
4	1 A 2 B 3 C
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub AddSpaces()
' hiker95, 02/07/2016, ME919631
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
  r = Trim(Replace(StrConv(r, vbUnicode), Chr(0), " "))
Next r
Columns("A").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the AddSpaces macro.

 

[pgc01]

Hi

A remark: excel, like most of the applications nowadays, uses Unicode that has tens of thousands of characters.
Both solutions will only work for the first 256 characters which can work for the English language (with no special symbols) but not much more than that.

 

[Rick Rothstein]

A remark: excel, like most of the applications nowadays, uses Unicode that has tens of thousands of characters.
Both solutions will only work for the first 256 characters which can work for the English language (with no special symbols) but not much more than that.

A fair point. I do not have much experience (actually, almost none) working with Unicode characters; however, I was able to "dummy up" the following UDF which appears to work correctly no matter what Unicode characters are in the text argument passed into it (though it breaks my heart that it is not a one-liner)...

Function InsertSpaces(S As String) As String
  Dim X As Long, Y As Long, Z As Long, Bin() As Byte, Bout() As Byte
  If Len(S) Then
    Bin = StrConv(S, vbUnicode)
    ReDim Bout(LBound(Bin) To 4 * UBound(Bin))
    Z = LBound(Bin)
    For X = LBound(Bin) To UBound(Bin) - 1 Step 4
      For Y = 0 To 7
        If Y < 4 Then
          Bout(Z + Y) = Bin(X + Y)
        ElseIf Y > 4 Then
          Bout(Z + Y) = 0
        Else
          Bout(Z + Y) = 32
        End If
      Next
      Z = Z + 8
    Next
    InsertSpaces = Trim(Replace(StrConv(Bout, vbFromUnicode), Chr(0), " "))
  End If
End Function

 

[pgc01]

Hi Rick

You could also loop through the characters adding spaces in between.

 

[Peter_SSs]

.. or another udf:

This to insert a space after every character.
If the requirement was to add a space after every character except spaces then use the first alternative pattern.
If the requirement is, as the original wording could be interpreted, to only insert spaces after letters or digits (ie exclude punctuation etc) then use the second alternative pattern.

Function SpaceOut(S As String)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.)" 'or "(\S)"  or  "([A-Za-z0-9])"
    SpaceOut = RTrim(.Replace(S, "$1 "))
  End With
End Function

 

[Rick Rothstein]

Hi Rick

You could also loop through the characters adding spaces in between.

When I first tried getting straight VB string functions to manipulate a cell with Unicode characters in it, I had problems (if I remember correctly, ASCII 63 characters kept being returned). Can you post the code you had in mind? Anyway, I figured instead of battling VB over this, that using Byte arrays should work... and besides, Byte arrays handle strings quite quickly (see my response to Peter below).

.. or another udf:

This to insert a space after every character.
If the requirement was to add a space after every character except spaces then use the first alternative pattern.
If the requirement is, as the original wording could be interpreted, to only insert spaces after letters or digits (ie exclude punctuation etc) then use the second alternative pattern.

Function SpaceOut(S As String)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.)" 'or "(\S)"  or  "([A-Za-z0-9])"
    SpaceOut = RTrim(.Replace(S, "$1 "))
  End With
End Function

I think your code is much slower than the code I posted. Here is how I tested the speed... I put ABC in cell A1 and an 8-character all Unicode string in cell A2 and I left cell A3 blank... I then copied A1:A3 down to A5005 and ran the following code (several times with your function and then several times with my function where the red text is).

Sub Test()
  Dim X As Long, Dummy As String

Debug.Print "? Abs(" & Timer & " - ";
  
  For X = 1 To 5005
    Dummy = [B][COLOR="#FF0000"]InsertSpaces[/COLOR][/B](Cells(X, "A").Value)
  Next
  
Debug.Print Timer & ")"

End Sub

For your code, I got an average of 1.17 seconds execution time... for my code, I got an average of 0.04 seconds.

 

[Peter_SSs]

I think your code is much slower than the code I posted.

You are certainly correct Rick. With only 2 samples & no indication of the data size, I certainly didn't focus on speed. I posted because I thought that there was an issue around unicode characters, though on looking back I see that wasn't really related to this thread.

If the data was largish, the RegExp speed could be improved considerably as follows ..

Function SpaceOut(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "(.)"  'or "(\S)"  or  "([A-Za-z0-9])"
  SpaceOut = RTrim(RX.Replace(s, "$1 "))
End Function

.. though I'm sure that would still be considerably slower than your code.

If speed became an issue, & a udf was the desired option, I would also consider also

Function SpaceIt(s As String) As String
  Dim i As Long
  Dim tmp As String

  If Len(s) Then
    tmp = Space(Len(s) * 2 - 1)
    For i = 1 To Len(s)
      Mid(tmp, i * 2 - 1) = Mid(s, i, 1)
    Next i
    SpaceIt = tmp
  End If
End Function

For me, this tested 20-25% faster than your code, though I won't be surprised if you don't agree, as the time difference between our original codes was not nearly as marked when I tested as the difference you reported.