Complicated Text String Question

[aus10fek]

I have a cell with the value Western Washington University - 516 High St. - Bellingham, WA 98225 ( in bold ). in this text string i have a university name, street address, city, state abv. and zip. I need to extract each individual value in to its own cell, the format of the text string will be the same every time university name followed by a space and a dash then another space then the street address then another space, dash, space, followed by the city and a comma, then the states 2 letter abbreviation and last is the zip.

I used the right and left formulas to get the university name and the zip code from the ends but i am having difficulty with the middle of this text string extract.

Help!!?

Austin

 

[shg]

Welcome to the board.

Try Data > Text to columns, with hyphen as the delimiter.

 

[aus10fek]

it needs to be a formula for each data extract.

 

[MikeWx]

With the entire string in A1, the following formula will extract the middle portion when it is bounded by a space, a dash, and a space.

=MID(A1,FIND(" - ",A1)+3,FIND("*",SUBSTITUTE(A1," - ","*",2))-FIND(" - ",A1)-3)