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:

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

    Hope this will help

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