Extract text from string

[westend9876]

I copy and paste data into my spreadsheet in column A. The data in column A will have an address someplace in the column. Sometimes the address will appear like this:

11 Somewhere Ave
Anywhere, TX 79382

Where the first line (11 Somewhere Ave) is on one row. And Somewhere, TX 79382 is on the next row.

Other time the address will appear in the same row like:

11 Somewhere Ave, Anywhere, TX 79382

I need to extract the two letter state (TX) into cell B1 regardless of how the address appears in column A.

 

[eFms]

Hello,

You can use this formula to fetch the characters, assuming that last portion of the text is always digit be it in same line or split in two rows.

=MID(IF(ISNUMBER(RIGHT(A2,1)+0)=TRUE,RIGHT(A2,8),""),1,2)

Please check attachment and an explanation using fiddle.

 

[bosco_yip]

Maybe…….

=IF(ISNUMBER(-RIGHT(A1)),LEFT(RIGHT(A1,8),2),"")

Or,

=MID(A1,FIND(",",A1&",")+2,2)

Regards
Bosco

 

[shrivallabha]

You can invoke SEARCH function's wildcard functionality (limited and will fail if there are more than 1 matching pattern).
=MID(A1,SEARCH(" ?? ",A1)+1,2)
Since you are looking for USA states, better approach would be to use their list.
e.g. using Name Manager add a named range usaState which holds list of states and then apply following formula.
=LOOKUP(2^15,SEARCH(" "&usaStates&" ",A1),usaStates)