Split adresses

[AndersNOR9]

Hi, I have some addresses that need to be split into three different cells: Adress, Zip, and City. I tried =TRIM(LEFT(SUBSTITUTE(D2;",";REPT(" ";LEN(D2)));LEN(D2))) to get the address, worked fine for those with the form: xyzroad 16A, 1400 townXYZ. But when I have it in the form XYZ, XYZroad 18, 1473 XYZtown only the XYZ part gets extracted.

Any idea?

 

[steve the fish]

Try this:

=TRIM(MID(SUBSTITUTE($D2,",",REPT(" ",LEN($D2))),(COLUMNS($A$1:A1)-1)*LEN($D2)+1,LEN($D2)))

 

[steve the fish]

Or this if dragging down:

=TRIM(MID(SUBSTITUTE(D$2,",",REPT(" ",LEN(D$2))),(ROWS($A$1:A1)-1)*LEN(D$2)+1,LEN(D$2)))

 

[AndersNOR9]

Thanks, still have the problem. See screenshot. And how should I get the zip and city to their own cells?

https://imgur.com/a/lxF3BSw

 

[steve the fish]

You drag that formula i gave initially across until you recieve no more 'hits'