Data to coloumns - separate after zip code

Try this workbook

Hope this helps

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

It worked like a CHARM! Thank you very much!! :-)

I really apriciate your help, and it troubles me that I'm asking you yet another question.
I thought I could do this my self, but it doesn't work.

I want to delete everything after the company name, from and with "V/"...
Ex.:
Arcasa ApS Arkitekter Par
arkitekter V/ Thomas Andersen
M arkitekter A/S
0205 I/S v/ Peter Eriksen Og Birgitte Neergaard
1:1 Arkitekter ApS
1:1 Landskab ApS
1:1 V/ Sigurd Elling

Will be:
Arcasa ApS Arkitekter Par
arkitekter
M arkitekter A/S
0205 I/S
1:1 Arkitekter ApS
1:1 Landskab ApS
1:1


How is that possible?

Thank you in advance...

Try this

=TRIM(IF(ISERROR(SEARCH("v/",A2,1)),A2,LEFT(A2,SEARCH("v/",A2,1)-1)))

Hope this helps

Happy to have been of some help.