Tips & Tricks 154 – Insert a Space after Each Character

You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro. (Flash Fill also works in most of the cases. But Flash Fill will copy in a different range which you will have to copy back and if data is scattered throughout the sheet, it may not be convenient)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change "Sheet1" as per your requirement.
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

' *** Macro by Vijay A Verma (eforexcel.com) *****
Sub InsertSpace()
    Dim Ws As Worksheet
    Dim Rng As Range, Cell As Range
    Dim i As Long
    Dim Str As String
    Set Ws = Worksheets("Sheet1")
    On Error Resume Next
    Set Rng = Ws.Cells.SpecialCells(xlConstants)
    On Error GoTo 0
    If Rng Is Nothing Then Exit Sub
    For Each Cell In Rng
        Str = ""
        For i = 1 To Len(Cell)
            Str = Str & " " & Mid(Cell, i, 1)
        Next i
            Cell = Trim(Str)
    Next Cell
End Sub

Update: 18-Aug-20

Taking logic from Rick's comments, following is a shorter code which doesn't use loop within a cell

Sub InsertSpace()
Dim Ws As Worksheet
Dim Rng As Range, Cell As Range
Dim i As Long
Dim Str As String
Set Ws = ActiveSheet
On Error Resume Next
Set Rng = Ws.Cells.SpecialCells(xlConstants)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
For Each Cell In Rng
Cell = Trim(Replace(StrConv(Cell, vbUnicode), Chr(0), " "))
Next Cell
End Sub