Extracting text from cells
 

I am trying to create a formula that looks at the contents of a cell and
tries to match several (only one per cell) text strings, and print that in
the cell.

For example I have cell A1, A2 containing the following:
yada yada Ham yada yada
bobblede bobblede Turkey bobblede bobblde

I need a formula that says: search a1, find "Ham", print "Ham", find
"Turkey", print "Turkey".

Find function doesnt work with more than one argument, and combining Search
with iserror with multiple arguments isnt working either. I have tried the
following fromulas with only partial success:

=IF(FIND("HAM",A19),"HAM","") - only works with one argument
=IF(ISERROR(SEARCH("TURKEY",A9,1)),"","Turkey") - also can only get one
argument to work.

Thanks for any help you can give...

Robert

Extracting text from cells
 

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

"frosterrj" wrote in message
...
I am trying to create a formula that looks at the contents of a cell and
tries to match several (only one per cell) text strings, and print that in
the cell.

For example I have cell A1, A2 containing the following:
yada yada Ham yada yada
bobblede bobblede Turkey bobblede bobblde

I need a formula that says: search a1, find "Ham", print "Ham", find
"Turkey", print "Turkey".

Find function doesnt work with more than one argument, and combining
Search
with iserror with multiple arguments isnt working either. I have tried
the
following fromulas with only partial success:

=IF(FIND("HAM",A19),"HAM","") - only works with one argument
=IF(ISERROR(SEARCH("TURKEY",A9,1)),"","Turkey") - also can only get one
argument to work.

Thanks for any help you can give...

Robert

Extracting text from cells
 

Biff,
thanks for the help. I used the second version with the lookup table.

Can you explain what the 32768 is for? I've never seen that used before.

Thanks,
Robert

"T. Valko" wrote:

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

"frosterrj" wrote in message
...
I am trying to create a formula that looks at the contents of a cell and
tries to match several (only one per cell) text strings, and print that in
the cell.

For example I have cell A1, A2 containing the following:
yada yada Ham yada yada
bobblede bobblede Turkey bobblede bobblde

I need a formula that says: search a1, find "Ham", print "Ham", find
"Turkey", print "Turkey".

Find function doesnt work with more than one argument, and combining
Search
with iserror with multiple arguments isnt working either. I have tried
the
following fromulas with only partial success:

=IF(FIND("HAM",A19),"HAM","") - only works with one argument
=IF(ISERROR(SEARCH("TURKEY",A9,1)),"","Turkey") - also can only get one
argument to work.

Thanks for any help you can give...

Robert

Extracting text from cells
 

Robert

Used because that's 1 more than maximum characters possible in a single
cell (2^15 -1 or 32,767). The Search returns a position number where
the string starts (assuming it finds it) - so this returned number will
always be less than 32768. The lookup will therefore match against the
next lowest number returned.

Hope this helps!

Richard

frosterrj wrote:
Biff,
thanks for the help. I used the second version with the lookup table.

Can you explain what the 32768 is for? I've never seen that used before.

Thanks,
Robert

"T. Valko" wrote:

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

Extracting text from cells
 

Thanks, That answer's my question!

Robert

"RichardSchollar" wrote:

Robert

Used because that's 1 more than maximum characters possible in a single
cell (2^15 -1 or 32,767). The Search returns a position number where
the string starts (assuming it finds it) - so this returned number will
always be less than 32768. The lookup will therefore match against the
next lowest number returned.

Hope this helps!

Richard

frosterrj wrote:
Biff,
thanks for the help. I used the second version with the lookup table.

Can you explain what the 32768 is for? I've never seen that used before.

Thanks,
Robert

"T. Valko" wrote:

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

Extracting text from cells
 

Robert

Used because that's 1 more than maximum characters possible in a single
cell (2^15 -1 or 32,767). The Search returns a position number where
the string starts (assuming it finds it) - so this returned number will
always be less than 32768. The lookup will therefore match against the
next lowest number returned.

Hope this helps!

Richard



frosterrj wrote:
Biff,
thanks for the help. I used the second version with the lookup table.

Can you explain what the 32768 is for? I've never seen that used before.

Thanks,
Robert

"T. Valko" wrote:

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

"frosterrj" wrote in message
...
I am trying to create a formula that looks at the contents of a cell and
tries to match several (only one per cell) text strings, and print that in
the cell.

For example I have cell A1, A2 containing the following:
yada yada Ham yada yada
bobblede bobblede Turkey bobblede bobblde

I need a formula that says: search a1, find "Ham", print "Ham", find
"Turkey", print "Turkey".

Find function doesnt work with more than one argument, and combining
Search
with iserror with multiple arguments isnt working either. I have tried
the
following fromulas with only partial success:

=IF(FIND("HAM",A19),"HAM","") - only works with one argument
=IF(ISERROR(SEARCH("TURKEY",A9,1)),"","Turkey") - also can only get one
argument to work.

Thanks for any help you can give...

Robert

Extracting text from cells
 

Robert

Used because that's 1 more than maximum characters possible in a single
cell (2^15 -1 or 32,767). The Search returns a position number where
the string starts (assuming it finds it) - so this returned number will
always be less than 32768. The lookup will therefore match against the
next lowest number returned.

Hope this helps!

Richard



frosterrj wrote:
Biff,
thanks for the help. I used the second version with the lookup table.

Can you explain what the 32768 is for? I've never seen that used before.

Thanks,
Robert

"T. Valko" wrote:

Try this:

=LOOKUP(32768,SEARCH({"ham","turkey"},A1),{"ham"," turkey"})

If you have more than a couple of words to lookup make a list of those words
in a range of cells and then refer to that range:

J1 = ham
J2 = turkey

=LOOKUP(32768,SEARCH(J$1:J$2,A1),J$1:J$2)

Biff

"frosterrj" wrote in message
...
I am trying to create a formula that looks at the contents of a cell and
tries to match several (only one per cell) text strings, and print that in
the cell.

For example I have cell A1, A2 containing the following:
yada yada Ham yada yada
bobblede bobblede Turkey bobblede bobblde

I need a formula that says: search a1, find "Ham", print "Ham", find
"Turkey", print "Turkey".

Find function doesnt work with more than one argument, and combining
Search
with iserror with multiple arguments isnt working either. I have tried
the
following fromulas with only partial success:

=IF(FIND("HAM",A19),"HAM","") - only works with one argument
=IF(ISERROR(SEARCH("TURKEY",A9,1)),"","Turkey") - also can only get one
argument to work.

Thanks for any help you can give...

Robert