Excel Formula: Extract Street Number, City and Zip without Comma

[unknownymous]

Hi Gurus,

Do you have any idea on how I can easily extract the details for a French address?

Example is: SAINT LEONARD 56200 ST MARTIN SUR OUST

SAINT LEONARD

56200​

ST MARTIN SUR OUST

Any help will be much appreciated.

Regards!

 

[jtakw]

This one is working.

Also, i have this one last sample of address format:

10 AVENUE DU 1ER DPG 57260 DIEUZE

The postal code is located at the most end of the address.

This will work as shown in my samples.

Word of advice, you should post Various samples of all possible different Text strings in your First post, instead of "trickling" the information one piece at a time, so helpers have a Full picture of your requirements as not to need to keep spending time amending formulas.

NOTE: B Column formula Won't populate until C Column populates.

Book3.xlsx
	A	B	C	D
1	Address
2	SAINT LEONARD 56200 ST MARTIN SUR OUST	SAINT LEONARD	56200	ST MARTIN SUR OUST
3	1 BOULEVARD DE LA VICTOIRE 49300 CHOLET	1 BOULEVARD DE LA VICTOIRE	49300	CHOLET
4	12345 SAINT LEONARD 00009 ST MARTIN	12345 SAINT LEONARD	00009	ST MARTIN
5	10 AVENUE DU 1ER DPG 57260 DIEUZE	10 AVENUE DU 1ER DPG	57260	DIEUZE
6	123 AVENUE DU 234AB 90000 CHOLET	123 AVENUE DU 234AB	90000	CHOLET
7	124 AVENUE DU 78234AB 00120 ST MARTIN	124 AVENUE DU 78234AB	00120	ST MARTIN
Sheet1030

Cell Formulas
Range	Formula
B2:B7	B2	=LEFT(A2,FIND(C2,A2)-2)
C2:C7	C2	=TEXT(LOOKUP(9^9,1/(1/MID(A2,ROW($1:$99),7))),"00000")
D2:D7	D2	=MID(A2,LEN(B2)+LEN(C2)+3,99)