Formula for pulling State from Cell

[Craig92604]

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

 

[NBVC]

=LEN(B19)-LEN(SUBSTITUTE(B19," ","")) will tell you how many spaces in B19

 

[Andrew Poulsom]

To count the number of spaces in A1:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

 

[Craig92604]

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)
)