CODEWITHSUNDEEP
excelvba
Alex Rivalo
Alex Rivalo

Reputation: 13

Parsing Excel Cell to get info I need

I am doing a project and currently I am doing it manually, but I think there is a way I can automate this.

My data set looks like this: Each Cell has a city, a state, a zip code (all in the format listed below)

Stony Brook, New York 11794-0001 United States

I figured out how to pull out the City, but I cant figure out how to pull the state. For single state names I can do it no problem. But for the example up there it only pulls New not new york, since im parsing from the , to the next space. Is there a way I can pull from the , to a number? Using mid maybe. Please let me know, all the data looks like the example I provided. Can't wait to hear back!

Upvotes: 1

Views: 29

Answers (1)

Tripp Kinetics
Tripp Kinetics

Reputation: 5439

To find the first digit in the cell,

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))

To extract the name of the state,

=MID(A1, SEARCH(",", A1) + 2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789")) - SEARCH(",", A1) - 3)

Upvotes: 1

Related Questions