Extract text between commas

[thanksamillion101]

Good evening,
I am needing to extract text, using the end of the string and moving left. The information before the last two commas will always be the same . The information before the third to last comma, will not always be the same. Thank you in advance for your help!!!

The formula I need will extract and return in 3 different columns: Physical location in the first column and City in the the second column and ST in the third column.

String:
[Company Name], Physical Location, City, ST 99901, USA

A couple of the formulas I have tried.

MID(SUBSTITUTE(A2," ",""), FIND("USA",SUBSTITUTE(A2," ",""))-8,7)

TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",100)),200),100))

 

[Peter_SSs]

Like this?

22 01 28.xlsm
	A	B	C	D	E
1
2	Co name, Physical Location, City, ST 99901, USA		Physical Location	City	ST 99901
Extract Text

Cell Formulas
Range	Formula
C2:E2	C2	=TRIM(LEFT(RIGHT(SUBSTITUTE($A2,",",REPT(" ",100)),500-100*COLUMNS($C:C)),100))

 

[Peter_SSs]

Or for column E perhaps you want this?

22 01 28.xlsm
	A	B	C	D	E
1
2	Co name, Physical Location, City, ST 99901, USA		Physical Location	City	ST
Extract Text (2)

Cell Formulas
Range	Formula
C2:D2	C2	=TRIM(LEFT(RIGHT(SUBSTITUTE($A2,",",REPT(" ",100)),500-100*COLUMNS($C:C)),100))
E2	E2	=TRIM(LEFT(RIGHT(SUBSTITUTE($A2," ",REPT(" ",100)),300),100))

 

[thanksamillion101]

Like this?

22 01 28.xlsm
	A	B	C	D	E
1
2	Co name, Physical Location, City, ST 99901, USA		Physical Location	City	ST 99901
Extract Text

Cell Formulas
Range	Formula
C2:E2	C2	=TRIM(LEFT(RIGHT(SUBSTITUTE($A2,",",REPT(" ",100)),500-100*COLUMNS($C:C)),100))

THANK YOU!! Each formula worked perfectly!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.