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.

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


"A.S." wrote:

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.

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")

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

Vaya con Dios,
Chuck, CABGx3



"A.S." wrote:

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.