Word Extraction Issues

**tklee411** · 10-01-2012, 05:02 PM

Hi everyone,

I'm having a few issues here with my if function, any help appreciated.

My function is

=IF(OR(E21="",E21="n/a"),G21, LEFT(E21, FIND("-", E21)-2))

The function works great except when it encounters a word in cell E21 that has no hyphens ("-"). I tried to use the extract word before space as well but sometimes E21 has one word with a space.

How would I write this function to call Cheer both from a cell that reads Cheer - AW222 and Cheer?

Thank you in advance!

**Ace_XL** · 10-01-2012, 05:05 PM

Perahps..

=IF(OR(E21="",E21="n/a"),G21, IF(ISNUMBER(FIND("-", E21)),LEFT(E21, FIND("-", E21)-2),E21))

**tklee411** · 10-01-2012, 05:15 PM

Thank you Ace!

This solves my initial problem perfectly but for some reason its not returning G21 when the E21 cell is N/a or empty...

What do you think? Maybe some sort of circular thing going on here?

**Ace_XL** · 10-01-2012, 05:37 PM

The formula looks good to me..perhaps upload a sample workbook?

**tklee411** · 10-01-2012, 05:39 PM

Fixed, thanks ACE!

Just for learning sake can you explain what your isnumber function does?

Thank you

**Ace_XL** · 10-01-2012, 05:46 PM

The ISNUMBER function returns TRUE or FALSE depending on whether it finds a number or not.

Hence, the ISNUMBER(FIND("-", E21)), basically checks whether "-" is found within E21 or not. If it finds a match it returns a number (the posiiton of "-" in the string) and ISNUMBER returns TRUE else FALSE.

Trust this helps!