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!

 

[kweaver]

Is there any consistency or pattern?

Maybe?

Book32
	A	B	C	D
2	SAINT LEONARD 56200 ST MARTIN SUR OUST	SAINT LEONARD	56200	ST MARTIN SUR OUST
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,FIND(C2,A2)-1)
C2	C2	=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
D2	D2	=RIGHT(A2,LEN(A2)-FIND(C2,A2)-LEN(C2))

 

[unknownymous]

For some reason, i'm getting an error in extracting the postal code.

Sometimes, there's a street number in the name. Example: 1 BOULEVARD DE LA VICTOIRE 49300 CHOLET

By the way, I'm using Excel 2016..

 

[kweaver]

That leading 1 will cause a problem with my code.

You need to post (using XL2BB) a variety of examples to see if this is possible.

 

[kweaver]

Book32
	A	B	C	D	E
1					helper
2	SAINT LEONARD 56200 ST MARTIN SUR OUST	SAINT LEONARD	56200	ST MARTIN SUR OUST	56200
3	1 BOULEVARD DE LA VICTOIRE 49300 CHOLET	1 BOULEVARD DE LA VICTOIRE	49300	CHOLET	BOULEVARD DE LA VICTOIRE 49300 CHOLET
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,FIND(C2,A2)-1)
C2:C3	C2	=IF(ISERR(LEFT(E2,1)*1),TEXTJOIN("",TRUE,IFERROR((MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1)*1),"")),E2)
D2:D3	D2	=RIGHT(A2,LEN(A2)-FIND(C2,A2)-LEN(C2))
E2:E3	E2	=IF(ISERR(LEFT(A2,1)*1),TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")),RIGHT(A2,LEN(A2)-FIND(" ",A2)))

 

[Fluff]

@kweaver the OP is using 2016 & so does not have textjoin.

 

[kweaver]

@kweaver the OP is using 2016 & so does not have textjoin.

I thought TEXTJOIN was added in 2016. I stand corrected.

 

[Fluff]

Nope, 2019/365

 

[jtakw]

Hi,

May be these will work, based on 2 samples provided:

Book3.xlsx
	A	B	C	D
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
Sheet1030

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17,FIND(" ",A2)))-1))
C2:C4	C2	=TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(B2)+2,99)," ",REPT(" ",99)),99))
D2:D4	D2	=MID(A2,LEN(B2)+LEN(C2)+3,99)

 

[unknownymous]

Hi,

May be these will work, based on 2 samples provided:

Book3.xlsx
	A	B	C	D
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
Sheet1030

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17,FIND(" ",A2)))-1))
C2:C4	C2	=TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(B2)+2,99)," ",REPT(" ",99)),99))
D2:D4	D2	=MID(A2,LEN(B2)+LEN(C2)+3,99)

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.

Appreciate your help.