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


"MarkMcG" wrote in message
...

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?

On Tue, 10 Jun 2008 08:15:00 -0700, MarkMcG
wrote:


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,ListOfStrin gs)),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(Stri ngToFind,ListOfStrings)),0))

--ron

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_e lse)

FIND is case sensitive when looking for TEXT.

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

Consider using SEARCH which is not case sensitive.


--
Biff
Microsoft Excel MVP


"MarkMcG" wrote in message
...

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?