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
9062 Old Annapolis Rd, COLUMBIA	9062 Old Annapolis Rd
50 Main St, 5th floor, WHITE PLAINS	50 Main St, 5th floor
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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet489​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),99))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.