Jun 07 2021 05:28 AM
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
Jun 07 2021 06:19 AM
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