Finding 1 of 3 different strings in a string

[MarkMcG]

I have various text strings in a column called Errors:

Error 4.4.5, too many people
Error 500: you aren't allowed to do that
Error code is in the code

I need a single formula to evaluate which of the 3 strings I am working with
to pull information out of the string. Find() will tell me if "4.4.5" exists
in string1, but returns #VALUE for string2 and string3. I don't want to get
have to read errors. It would nice if Find() returned 0 if the string was
not found.

Here is what I would like:

If(String1(), dostring1, If(String2(), dostring2, dostring3))

Is there an elegant way to do this without a UDF?

 

[Rick Rothstein \(MVP - VB\)]

I doubt if anyone would call this "elegant", but it should do what you
want...

=IF(LEFT(A1,5)="Error",CHOOSE(MATCH(MID(A1,7,4),{"4.4.","500:","code"},0),SUM(I$1:I$3),SUM(J$1:J$3),SUM(K$1:K$3)),"")

Note the absolute cell addresses used in the example formulas being executed
inside the CHOOSE function in response to your 3 error code messages...
these may be necessary when you copy the above formula down to protect their
referenced ranges... just something for you to keep in mind.

Rick

 

[Ron Rosenfeld]

I have various text strings in a column called Errors:

Error 4.4.5, too many people
Error 500: you aren't allowed to do that
Error code is in the code

I need a single formula to evaluate which of the 3 strings I am working with
to pull information out of the string. Find() will tell me if "4.4.5" exists
in string1, but returns #VALUE for string2 and string3. I don't want to get
have to read errors. It would nice if Find() returned 0 if the string was
not found.

Here is what I would like:

If(String1(), dostring1, If(String2(), dostring2, dostring3))

Is there an elegant way to do this without a UDF?

I'm not quite sure what you want to do, but perhaps this will help:

StringToFind is the string you are searching for.
ListOfStrings is a column of strings you are searching. In your example they
might be entered in A2:A4

To return the string number of the matched string:

=MATCH(TRUE,ISNUMBER(FIND(StringToFind,ListOfStrings)),0)

This formula must be entered as an **array formula** -- Hold down <ctrl><shift>
while hitting <enter>. Excel will place braces {...} around the formula.

So in the above, if StringToFind=500, then the formula would return the number
"2".

To simplify, put the MATCH formula in B1

Then you could do, for example:

If(b1=1, dostring1, If(b1=2, dostring2, dostring3))

To return the actual string in which the substring was found, enter this
**array formula** ( entered with <ctrl><shift><enter> )


=INDEX(ListOfStrings,MATCH(TRUE,ISNUMBER(FIND(StringToFind,ListOfStrings)),0))

--ron

 

[T. Valko]

It would nice if Find() returned 0 if the string was not found.

Wrap it inside of a COUNT function:

=IF(COUNT(FIND(.....)),do_something,do_something_else)

FIND is case sensitive when looking for TEXT.

"ERROR" and "error" will not match using FIND.

Consider using SEARCH which is not case sensitive.