How to put a comma for 60,000 contacts without formula

Question

How can I put a comma without a formula in 60k contacts. I need to separate the street number to street name. It so hard for me to do it manually. After I put a comma to the address I will separate them in column. Just like this

Answer 1

Assuming address starts with street number followed by space and then rest of the address, following should be helpful.

Sub Demo()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")  'change Sheet1 to your data sheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ws
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row    'get last row in Column A
        For i = 2 To lastrow
            .Range("B" & i).Formula = Evaluate("=SUBSTITUTE(A" & i & ","" "","", "",1)")
        Next i
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Answer 2

You could loop through the H column and in every cell replace the first space by a comma, using a vba routine.

Sub Addcomma()

Dim i As Integer

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
.Cells(i, 8) = Replace(.Cells(i, 8), " ", ",", , 1)
Next
End With

End Sub

After that you can use the text-to-columns function, using a comma as the delimiter.