Delete everything 2nd comma from right

[attv]

Hi Everyone! I need help with creating a function.

Below is what I have in column A. Now in column B I want a function that will delete everything, counting the second comma from the right side. So basically I only want city names and states

NOW: Abilene, TX5211 Bonita Ave, Allen, TX
WHAT I WANT: Allen, TX

Does anyone know what will work?

LOCATION​

Abilene, TX​

5211 Bonita Ave, Allen, TX​

1002 Raintree Circle, Allen, TX​

955 Hwy 121, Suite 130, Allen, TX​

5701 Time Square Blvd. Suite 190, Amarillo, TX​

643 Frenchtown Rd, Argyle, TX​

18477 West Lake Houston Parkway, Atascocita, TX​

3355 Bee Cave Road, Suite 502, Austin, TX​

12515 Research Blvd #100, Austin, TX​

1801 S. Mopac Suite 100, Austin, TX​

10309 Hansa Cove, Austin, TX​

609 Castle Ridge Rd #440, Austin, TX​

1908 McCloskey St, Austin, TX​

10526 W. Parmer Lane, Austin, TX​

1801 S Mopac Suite 100, Austin, TX​

14010 US Hwy 183, Austin, TX​

3801 Berkman Dr Apt A, Austin, TX​

​

​

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1	LOCATION
2	Abilene, TX	Abilene, TX
3	5211 Bonita Ave, Allen, TX	Allen, TX
4	1002 Raintree Circle, Allen, TX	Allen, TX
5	955 Hwy 121, Suite 130, Allen, TX	Allen, TX
6	5701 Time Square Blvd. Suite 190, Amarillo, TX	Amarillo, TX
7	643 Frenchtown Rd, Argyle, TX	Argyle, TX
8	18477 West Lake, Houston Parkway, Atascocita, TX	Atascocita, TX
9	3355 Bee Cave Road, Suite 502, Austin, TX	Austin, TX
10	12515 Research Blvd #100, Austin, TX	Austin, TX
11	1801 S. Mopac Suite 100, Austin, TX	Austin, TX
12	10309 Hansa Cove, Austin, TX	Austin, TX
13	609 Castle Ridge Rd #440, Austin, TX	Austin, TX
14	1908 McCloskey St, Austin, TX	Austin, TX
15	10526 W. Parmer Lane, Austin, TX	Austin, TX
16	1801 S Mopac Suite 100, Austin, TX	Austin, TX
17	14010 US Hwy 183, Austin, TX	Austin, TX
18	3801 Berkman Dr Apt A, Austin, TX	Austin, TX
Master

Cell Formulas
Range	Formula
B2:B18	B2	=TRIM(MID(A2,FIND("^",SUBSTITUTE(","&A2,",","^",LEN(","&A2)-LEN(SUBSTITUTE(","&A2,",",""))-1)),100))

 

[attv]

Thank you! It worked. It's going to save me much time

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

You could possibly also have used this.

21 03 11.xlsm
	A	B
1	LOCATION
2	Abilene, TX	Abilene, TX
3	5211 Bonita Ave, Allen, TX	Allen, TX
4	1002 Raintree Circle, Allen, TX	Allen, TX
5	955 Hwy 121, Suite 130, Allen, TX	Allen, TX
6	5701 Time Square Blvd. Suite 190, Amarillo, TX	Amarillo, TX
7	643 Frenchtown Rd, Argyle, TX	Argyle, TX
8	18477 West Lake, Houston Parkway, Atascocita, TX	Atascocita, TX
9	3355 Bee Cave Road, Suite 502, Austin, TX	Austin, TX
10	12515 Research Blvd #100, Austin, TX	Austin, TX
11	1801 S. Mopac Suite 100, Austin, TX	Austin, TX
12	10309 Hansa Cove, Austin, TX	Austin, TX
13	609 Castle Ridge Rd #440, Austin, TX	Austin, TX
14	1908 McCloskey St, Austin, TX	Austin, TX
15	10526 W. Parmer Lane, Austin, TX	Austin, TX
16	1801 S Mopac Suite 100, Austin, TX	Austin, TX
17	14010 US Hwy 183, Austin, TX	Austin, TX
18	3801 Berkman Dr Apt A, Austin, TX	Austin, TX
City State

Cell Formulas
Range	Formula
B2:B18	B2	=TRIM(RIGHT(SUBSTITUTE(", "&A2,", ",","&REPT(" ",100)),200))