Split number and text into new column from address field

[Ganesh Jagtap]

I need help to get below:

In excel column "A" i have address which i want to split into text and number into column "B" & "C".

Example : cell "A1" has data "404 Thin land 62 Street Germany 421301" Then

In cell "B1" i want data - "Thin land Street Germany" and
In cell "C1" i want data - "404,62,421301"

Thanks in Advance........

 

[tygrrboi]

How does this work?

I wasn't sure what you wanted to do with commas or other types of punctuation. In the code below they are turned into just spaces.

Sub SeparateAddress()
Dim lastrow As Integer, i As Integer, j As Integer
Dim OriginalString As String, Part As String, NumberString As String, LetterString As String


Application.ScreenUpdating = False


lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row


For i = 1 To lastrow
    OriginalString = ActiveSheet.Range("A" & i).Value
    For j = 1 To Len(OriginalString)
        Part = Mid(OriginalString, j, 1)
        Select Case Asc(LCase(Part))
        Case 95 To 122 'is a letter
            LetterString = LetterString & Part
        Case 48 To 57  'is a number
            NumberString = NumberString & Part
        Case Else 'space or comma or other character
            LetterString = LetterString & " "
            NumberString = NumberString & " "
        End Select
    Next j
    With Application.WorksheetFunction
        ActiveSheet.Range("B" & i).Value = .Trim(LetterString)
        ActiveSheet.Range("C" & i).Value = .Substitute(.Trim(NumberString), " ", ",")
    End With
    LetterString = vbNullString
    NumberString = vbNullString
Next i


Application.ScreenUpdating = True


End Sub

 

[Ganesh Jagtap]

Thanks a lot it works fine. Have a nice time......

 

[Rick Rothstein]

I wasn't sure what you wanted to do with commas or other types of punctuation. In the code below they are turned into just spaces.

I shortened your code slightly and, because I process everything in memory, my code should execute slightly faster as well. As for the non-numbers non-letters, I did what you did with the exception of the dash which I would expect could occur naturally within street names.

[table="width: 500"]
[tr]
	[td]Sub SeparateAddress()
  Dim X As Long, Z As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 2)
  For X = 1 To UBound(Data)
    For Z = 1 To Len(Data(X, 1))
      If Mid(Data(X, 1), Z, 1) Like "[A-Za-z -]" Then
        Result(X, 1) = Result(X, 1) & Mid(Data(X, 1), Z, 1)
      ElseIf Mid(Data(X, 1), Z, 1) Like "[0-9 ]" Then
        Result(X, 2) = Result(X, 2) & Mid(Data(X, 1), Z, 1)
      Else
        Result(X, 1) = Result(X, 1) & " "
        Result(X, 2) = Result(X, 2) & " "
      End If
    Next
    Result(X, 1) = Application.Trim(Result(X, 1))
    Result(X, 2) = Replace(Application.Trim(Result(X, 2)), " ", ",")
  Next
  Range("B1:C" & UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

 

[tygrrboi]

Thank you Rick.

I still do not have a handle on arrays... Try as I might. That was a good lesson for me as well.