How do I delete text to right of a comma in a range of cells?

[Guest]

I have a long list of addresses and need to separate out the city and state,
which got merged all into one cell. Need to import data into Streets and
Trips and want to delete all city and state info out of the address cells.
Please assist.

Mike

 

[Ian]

Select the column with your data in and ensure that ythe next column is
empty.
Go to Data>Text to Columns and choose delimited, then comma.
Assuming your data is in column A you should get city in A and state in B.

 

[JMay]

Here's a simple example using VBA -- Assumes there are NO BLANK cells in your
Range..

Select (or Highlight) the range of cells (say B5:g20)
then run this macro: (you've placed in a standard module).

Sub RemoveText()
Set MyRange = Selection
For Each c In MyRange
pos = WorksheetFunction.Find(",", c)
c.Value = Mid(c, 1, pos - 1)
Next c
End Sub

HTH,