Separating inconsistent address information into columns

[bornsteij78]

Hi Everyone,

I need to separate address information into 5 columns. Some of the addresses have 4 separators, and some have 3.

Address

1 Main St, Watertown, MA, 02472

8 Main St, #2, Watertown, MA, 02472

When I do text to columns, using "," as a separator, this is what happens:

How can I most efficiently get all these addresses to do this:

Address 1	Address 2	City	State	Zip
1 Main St		Watertown	MA	02472
8 Main St	#2	Watertown	MA	02472

Any help/guidance would be much appreciated.

Thanks!

 

[JamesCanale]

Working with addresses is always hard.

This uses filterxml until textsplit comes out. If there are four items, it will assume there is no Line 2 of the address.

MrExcelPlayground12.xlsx
	A	B	C	D	E	F
1	Address	Line 1	Line 2	City	State	Zip
2	1 Main St, Watertown, MA, 02472	1 Main St		Watertown	MA	02472
3	8 Main St, #2, Watertown, MA, 02472	8 Main St	#2	Watertown	MA	02472
Sheet12

Cell Formulas
Range	Formula
B2:F3	B2	=LET(a,FILTERXML("<a><s>" &SUBSTITUTE(A2,",","</s><s>") & "</s></a>","//s"),b,ROWS(a),zip,INDEX(a,b),state,INDEX(a,b-1),city,INDEX(a,b-2),L,INDEX(a,1),LL,IF(b=4,"",INDEX(a,2)),CHOOSE(SEQUENCE(1,5),L,LL,city,state,zip))
Dynamic array formulas.

 

[bornsteij78]

=LET(a,FILTERXML("<a><s>" &SUBSTITUTE(A2,",","</s><s>") & "</s></a>","//s"),b,ROWS(a),zip,INDEX(a,b),state,INDEX(a,b-1),city,INDEX(a,b-2),L,INDEX(a,1),LL,IF(b=4,"",INDEX(a,2)),CHOOSE(SEQUENCE(1,5),L,LL,city,state,zip))

You rock, thanks so much!!

 

[Fluff]

If you have the new functions, another option

Fluff.xlsm
	A	B	C	D	E	F
1	Address	Line 1	Line 2	City	State	Zip
2	1 Main St, Watertown, MA, 02472	1 Main St		Watertown	MA	02472
3	8 Main St, #2, Watertown, MA, 02472	8 Main St	#2	Watertown	MA	02472
Main

Cell Formulas
Range	Formula
B2:F3	B2	=LET(a,TEXTSPLIT(A2,","),IF(COLUMNS(a)=4,CHOOSECOLS(EXPAND(a,,5,""),1,-1,2,3,4),a))
Dynamic array formulas.