Forum Discussion

birken4115's avatar
birken4115
Copper Contributor
Jun 07, 2021

CAN SOMEBODY HELP ME WITH SPLITTING CELL INFORMATION

I need to splitt the information i cell C.

Into business name - gateaddress - gate nr. - zipcode - city.

Like i shows in row 4 collum I - J - K - L - M.

Mail address 

Frank Olsen

mailto:olsen3269@gmail.com

or mailto:olsen3269@outlook.com

 

 

excel
office 365

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 07, 2021

    birken4115 

    Here is a macro:

    Sub SplitData()
    Dim r As Long
    Dim m As Long
    Dim p() As String
    Dim pos As Long
    Application.ScreenUpdating = False
    m = Range("C" & Rows.Count).End(xlUp).Row
    Range("I2:M" & m).Clear
    For r = 2 To m
        p = Split(Range("C" & r).Value, vbLf)
        Range("I" & r).Value = p(0)
        If UBound(p) >= 1 Then
            pos = InStrRev(p(1), " ")
            If pos Then
                Range("J" & r).Value = Left(p(1), pos - 1)
                Range("K" & r).Value = Mid(p(1), pos + 1)
            Else
                Range("J" & r).Value = p(1)
            End If
        End If
        If UBound(p) >= 3 Then
            pos = InStr(p(3), " ")
            If pos Then
                Range("L" & r).Value = Left(p(3), pos - 1)
                Range("M" & r).Value = Mid(p(3), pos + 1)
            Else
                Range("M" & r).Value = p(3)
            End If
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Resources