help with isolating strings in an excel cell

[Luis939]

oh i really need help now..bascially all i want to do is search for part of a string, like lets say one spreadsheet has a cell new york, ny and the other file has new york, i basically want to take the cell from the first file and only isolate the string (city) before the comma and state initials, so that i can take that string (new york) and compare it with the other new york and i guess use the EXACT function to see if they are the same....now i dont know how to approach this..i know the mid function you can tell it to go to any character and display the string starting at that character at any length you want, but basically i'll have cells with muliple cities so i want to isolate the city only, which obviously can have any length, sorta saying isolate the string until you hit that comma separating the city and state initials, or maybe start at the end of the cell and skip 4 spaces--2 for the state intials, 1 for the comma and 1 for the space between the comma and city, perhaps then say copy the string starting at that point all the way into the beginning of the cell, but basically i need to transfer over the city and not state....also likewise...how could i take such a cell with the city and state and say keep the city, but delete the state, or delete the last 2 digits or delete everything past a certain character like a dash...okokok i know i dont have any code but all i have in my head or ideas not straight code....if anyone can help (most likely rob lol) then just spit some thoughts out..id really appreciate it...these ppl here think i can do any little thing they want me too, so we'll see, thanks--oh n sorry for the long question lol

 

[mikevh]

Here's a little bit of code that extracts city name from
"city,state" and stores it in another column on the same
worksheet. The key is using the Instr function to get the position of the comma, then using the Left function to extract everything before that. I tested this and it works. Perhaps this will give you some ideas -- I'm no
expert on Excel VBA, to put it mildly. (And anyone who
is can probably see that from the code.)

Sub GetCityNames()
'Extracts city names from "city,state" in col 1 of first 4 rows of
'active worksheet and places them in col 2, first 4 rows.

    Dim row As Integer, CommaPos As Integer
    Dim sh As Worksheet
    Dim cityName As String
    
    Set sh = ActiveSheet
    
    For row = 1 To 4
        CommaPos = InStr(sh.Cells(row, 1), ",")
        cityName = Left(sh.Cells(row, 1), CommaPos - 1)
        sh.Cells(row, 2) = cityName
    Next row

End Sub