Delete text from right to left until Comma

[MoreGone]

Hello,

I am trying to format some addresses. They are currently "Street Address, CITY" and I am trying to remove the City along with the Comma.
Example: 9062 Old Annapolis Rd, COLUMBIA Want: 9062 Old Annapolis Rd

I have tried flash fill and while that mostly worked it went wonky on many of the entries. In a variety of ways.

I would use LEFT or RIGHT formulas with FIND but there are often more than one Comma in the address so I don't know how to make that work.
Example: 50 Main St, 5th floor, WHITE PLAINS

Any tips how to delete the City name and the Comma would be appreciated.

Thanks

 

[jtakw]

Hi,

Assuming your addresses would not be longer than 99 characters:

Book1
	A	B
1	9062 Old Annapolis Rd, COLUMBIA	9062 Old Annapolis Rd
2	50 Main St, 5th floor, WHITE PLAINS	50 Main St, 5th floor
3	123 E Ave., 2nd floor, Apt.B, Small Town	123 E Ave., 2nd floor, Apt.B
Sheet489

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),99))

 

[MoreGone]

Hi,

Assuming your addresses would not be longer than 99 characters:

	A	B
1	9062 Old Annapolis Rd, COLUMBIA	9062 Old Annapolis Rd
2	50 Main St, 5th floor, WHITE PLAINS	50 Main St, 5th floor
3	123 E Ave., 2nd floor, Apt.B, Small Town	123 E Ave., 2nd floor, Apt.B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet489​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),99)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

That worked beautifully. Thank you! I will google to try and fully understand what is going on, but your brain is awesome. Thank you!

 

[jtakw]

You're very welcome, thanks for the feedback.