Add Customize Spacing in a cell text value

  • 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.

  • Hello,

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

    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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!