1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add space between digits

Discussion in 'Excel Challenges' started by bines53, Jan 27, 2018.

  1. bines53

    bines53 Active Member

    Messages:
    705
    Hello friends ,

    This time something light and good.

    Data in column A, cells with numbers without a space, in column C, should change that will be with a space.

    For example ,

    182024 Should be 1 8 2 0 2 4 ,

    The size of the characters is different,

    In Excel 2016, has the TEXTJOIN function, it has of course an advantage, but not necessarily especially in this example, In my opinion.

    Conditioning is, without volatile functions ,And NO UDF !

    Good luck!

    David

    Attached Files:

  2. Belleke

    Belleke Active Member

    Messages:
    486
    Is this what you want?
    Code (vb):

    Sub AddSpaces()
    Dim r As Range
    Application.ScreenUpdating = False
    For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
      r = Trim(Replace(StrConv(r, vbUnicode), Chr(0), " "))
    Next r
    Columns("A").AutoFit
    Application.ScreenUpdating = True
    End Sub
     
  3. bines53

    bines53 Active Member

    Messages:
    705
    Hi @ Belleke,

    Sorry, I was probably not clear enough, I mean only the formula.

    You can convert the macro to a standard formula ?, just so I can understand.


    Thank you !

    David
    Last edited: Jan 27, 2018
  4. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,971
    Hi David,

    This seems to work:

    =TEXT(A1,REPT("0 ",LEN(A1)))

    Regards,
  5. bines53

    bines53 Active Member

    Messages:
    705
    Hi khalid NGO ,

    perfect !

    Just like mine !

    These characters can be placed,? , #, Instead of 0.

    Until about a month ago, I did not understand what is unique about the function

    REPT, only recently I started to use it.


    Regards,

    David
    Last edited: Jan 28, 2018
  6. Hareesh_KLD

    Hareesh_KLD Member

    Messages:
    74
    Hi @ Belleke,

    i saw u r code and tried it in another way

    Code (vb):
    Sub AddSpacesMySkill()

        Dim i As Integer
        Dim r As Range
     
        Application.ScreenUpdating = False
     
        For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
         
            For i = 1 To (Len(r) * 2) - 1 Step 2
         
                r = WorksheetFunction.Substitute(r, Left(r, i), Left(r, i) & " ")
           
            Next i
     
        Next r
     
        Columns("A").AutoFit
     
        Application.ScreenUpdating = True
     
    End Sub
    Last edited by a moderator: Feb 17, 2018
  7. NPOKALA

    NPOKALA New Member

    Messages:
    8
    Hi There,

    Here is the solution for your requirement in the attached file. note, logic is shown in the separate columns (H - R) and hidden.

    any comments / suggestions...do write to me: itsnaveen.pokala@gmail.com

    Good Day,
    Naveen





    Attached Files:

Share This Page