Data to coloumns - separate after zip code

Because your addresses are quite variable I think that a UDF might be the easiest solution.

Option Explicit

Function FindDanishPC(rng As Range)
    Dim n As Integer
    Dim arrSplit As Variant

    arrSplit = Split(rng, " ")
    FindDanishPC = ""
    For n = LBound(arrSplit) To UBound(arrSplit)
        If Len(arrSplit(n)) = 4 And IsNumeric(arrSplit(n)) Then
            FindDanishPC = arrSplit(n) * 1
        End If
    Next
End Function

Enter this in D2

=FindDanishPC(B2)

In C2

=TRIM(LEFT(B2,SEARCH(D2,B2,1)-1))

in E2

=RIGHT(B2,LEN(B2)-SEARCH(D2,B2,1)-4)

Drag/Fill these down as required.


This will return the last group of 4 digits in the string.

Hope this helps