Add Customize Spacing in a cell text value

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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:


    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

    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!

Register Yourself Login