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.
- garbo7441Lv 79 years agoFavorite 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:
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) & " "
Cells(i, "A").Value = Left(tmpVar, Len(tmpVar) - 1)
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'.