Extract word from text

Hi

Try this formula:

=TRIM(MID(A1,FIND(":",A1)+1,FIND(" -",A1)-4))

i am going with the assumption that you may or may not have ":" and "-", but certainly will have " " on either side of the second word in the string. if ":" and "-" are constants, then you are better off using @jraj's formula.

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),FIND("|",SUBSTITUTE(A1," ","|",2))-FIND("|",SUBSTITUTE(A1," ","|",1)))

EDIT:

use the following monstrosity instead for best results.

=MID(TRIM(A1),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",1)),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",2))-FIND("|",SUBSTITUTE(TRIM(A1)," ","|",1)))

TRIM function reduces to 1 any number of contiguous white spaces greater than (or equal to) 1 within the string (not extremities - those are always zeroed out). in the earlier formula, if it encountered 2 contiguous white spaces, the result would be erroneous. due to TRIM, all such instances would be reduced to just a single instance. however, not all "white-space" induced errors, i will admit, can be countered with this strategy (as zbor has pointed out below).

Or, to be more clear, those results work for different cases to extract word:

jraj-> ABC DEF: word will return word
icestationzbra -> ABC: word will return word
icestationzbra -> ABC DEF: word will return DEF:
jraj-> ABC DEF; word will return error
icestationzbra -> ABC; word will return word
icestationzbra -> ABC DEF; word will return DEF;

Aland2929: what possible inputs you expect of above?

see post #4 for updated formula.