Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count specific text that occurs in a range of cells | Excel Discussion (Misc queries) | |||
How can I fill a series in Nonadjacent cells eg Text 1, Text 2 | Excel Discussion (Misc queries) | |||
Can't see text in cells | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Cells formated as text do not always display properly | Excel Discussion (Misc queries) |