Add Customize Spacing in a cell text value

luzmen · Dec 15th 2017

Hi everyone,

Is there any formulas to add spaces in a cell text value?

Example: ABCDE result to: 1.) A B C D E (1 space in between) or 2. A B C D E (customize space in between).

Or in short i can judge how many spaces between text value. I've googled it and found only numeric value can customize spaces in between through Format Cells > Number > Custom, can't find it with text value.

I already thought of using =MID(B1,1,1)&" "&MID(B1,2,1)&" "&MID(B1,3,1)&" "&MID(B1,4,1)&" "&MID(B1,5,1) , but the cell value will not permanently have the same numbers of characters.

Is this possible with formula?

Thanks in advance.

**Carim** · Dec 15th 2017

Hello,

You could design your own UDF ... :wink:

Code

Function AddSpace(Str As String, n As Long) As String
' Example = AddSpace(B1,2)
    Dim i As Long
    For i = 1 To Len(Str)
        AddSpace = AddSpace & Mid(Str, i, 1) & Application.WorksheetFunction.Rept(" ", n)
    Next i
    AddSpace = Trim(AddSpace)
End Function

Hope this will help

Add Customize Spacing in a cell text value

Participate now!

Share