Word Extraction

[A.S.]

anyone know how i can write a formula that would extract part of a word from
a cell, but if doesn't have the word it returns anything but a #VALUE!?

Ex: Afghanistan
Afghanistan - Cellular
So the formula would return a 1 or 0 or anything but #Value! and the second
would return the wor cellular. The list would would go down so next would be
let's say:
American Samoa
American Samoa - Cellular

Again, 1st would return anything and second would return cellular

Thanks.

 

[Ron Coderre]

Perhaps something like this:

For text in A1
B1: =IF(COUNTIF(A1,"*cellular*"),"Cellular","Other")

Copy that formula down as far as needed.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[jim]

To add to Ron's comment, you could wrap whatever formula you're using
to extra the word in an ISERROR function.

Something like: =IF(ISERROR(extraction_formula),"Value you'd rather
see",extraction_formula).

Alternatively, you could specify the error (here using RIGHT instead of
COUNTIF):

=IF(RIGHT(cell,8)="Cellular","Cellular","What you'd rather see")

 

[CLR]

=IF(RIGHT(A1,8)="cellular","Cellular","No")

Vaya con Dios,
Chuck, CABGx3