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

  • 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

bines53

Active Member
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
 

Attachments

Belleke

Active Member
Is this what you want?
Code:
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
 

bines53

Active Member
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:

bines53

Active Member
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:
Hi @ Belleke,

i saw u r code and tried it in another way

Code:
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:

NPOKALA

New Member
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





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
 

Attachments

Top