Formula for pulling State from Cell

Craig92604

Well-known Member
Joined
Nov 7, 2002
Messages
505
I put together this formula to pull the state from a cell that contains City, State and Zip

=MID(B19,FIND(" ",B19,1),3)

Works fine for when the City is One word but when it is two words it doesn't work.

This formula works if there is two word city=MID(B19,FIND(" ",B19,FIND(" ",B19,C19+1)),3)

What I am looking for is a formula to count the number of spaces in a cell. Then I could build a nested if statment to handle cells with both 1 and two word cities.

PS- There is not a comma after the city
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
=LEN(B19)-LEN(SUBSTITUTE(B19," ","")) will tell you how many spaces in B19
 

Craig92604

Well-known Member
Joined
Nov 7, 2002
Messages
505
Worked Like a Charm! Thanks Andrew and NBVC!

=IF(LEN(B19)-LEN(SUBSTITUTE(B19," ",""))>2,MID(B19,FIND(" ",B19,FIND(" ",B19,FIND(" ",B19,1)+1)),3),MID(B19,FIND(" ",B19,1),3)
)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top