# Formula for pulling State from Cell

#### Craig92604

##### Well-known Member
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

### 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
=LEN(B19)-LEN(SUBSTITUTE(B19," ","")) will tell you how many spaces in B19

#### Andrew Poulsom

##### MrExcel MVP
To count the number of spaces in A1:

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

#### Craig92604

##### Well-known Member
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)
)

Replies
2
Views
55
Replies
9
Views
567
Replies
4
Views
474
Replies
12
Views
823
Replies
7
Views
95