| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formula and text search
Normally you set up as A1 = 1, 'value if true', 'value if false'.
Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
*Maybe* this:
=IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Or, if the text may be somewhere in the middle of a sentence,
=if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Thanks for the info, that worked. Also, what if I want to find multiple
words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Is the text you're looking for at random spots of the string, like this:
this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Depends on if you want to find where both are in the cell or only need 1.
If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for Text within a Formula Result Q | Excel Worksheet Functions | |||
| vlookup formula fails to return data search item is text | Excel Worksheet Functions | |||
| Text search within a string using formula | Excel Worksheet Functions | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
| Search column and move text formula | Excel Discussion (Misc queries) | |||
Hybrid Mode
