Anonymous asked in Computers & InternetSoftware · 9 years ago

In Microsoft Excel, how do I insert a space between every character in a cell?

I want to do this with many cells. Stuff in cells varies in length.

1 Answer

  • 9 years ago
    Favorite Answer

    You don't state whether the cells you wish to 'do this with' are in a column, columns, row, rows, or randomly.

    The following example will do as you ask for all populated cells in column A, no matter how many there are or how many characters are contained in each string.

    Copy the following macro to the clipboard:

    Sub Space_Out()

    Dim i, j, LastRow, tmpVar

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow

    For j = 1 To Len(Cells(i, "A"))

    tmpVar = tmpVar & Mid(Cells(i, "A"), j, 1) & " "

    Next j

    Cells(i, "A").Value = Left(tmpVar, Len(tmpVar) - 1)

    Next i


    End Sub

    Press ALT + F11

    In the menus at the top of the VBE, select INSERT > MODULE

    Paste the macro into the editing area to the right.

    Close the VBE and return to the worksheet.

    Press ALT + F8

    When the Macros window opens, select this macro and click 'RUN'.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.