FIND TEXT WITHIN A RANGE

**ROBINSYN** · May 24th, 2003, 06:20 PM

Hi There,

I have a range A2:A60 (Text only)

As a result of input only one cell within this range will contain text.
I need to extract the text and place it in V26.

Anyone know of a formula. I thought maybe "Max" but it only works with numbers.

Many thanks

**Aladin Akyurek** · May 24th, 2003, 06:28 PM

Originally Posted by ROBINSYN

...Anyone know of a formula...

I do...

=LOOKUP(REPT("z",90),A2:A60)

**ROBINSYN** · May 24th, 2003, 06:45 PM

What does the "z" represent. And the "90"

It works with a text string like Winter Bonus, but I need it to return "WO"
from a corresponding range depending on the type of leave the user selects. The are over 30 codes and all are two letters only.

Does it matter that these codes are a result of a formula.

Thanks :pary:

**Aladin Akyurek** · May 24th, 2003, 06:51 PM

Originally Posted by ROBINSYN

What does the "z" represent. And the "90"

It works with a text string like Winter Bonus, but I need it to return "WO"
from a corresponding range depending on the type of leave the user selects. The are over 30 codes and all are two letters only.

Does it matter that these codes are a result of a formula.

Thanks :pary:

REPT("z",90) creates a string of 90 "z", a big string that is used as match string.

Let A27 have a text value. It seems you want to return the value that corresponds to it in column B, more precisely the value B27. Right? And, i don't understand that "WO"bit?

**ROBINSYN** · May 24th, 2003, 07:35 PM

Ok, I have two drop down list, using data valdation.

Category = Leave Cells E2:E22

Examples of categories:
Sick
Special
Union Business
Leave wwithout Pay

The second list is dependent on the first( Category ) B2:B60

Examples of Leave:

Jury Duty (Code=JD)
Witness(Code=WT)
Without Pay(Code=WI)
With Pay(Code=WP)
Injury on Duty(Code=DI)
Personal LWOP < months(Code=LP)
Relocation of Spouse(Code=SR)
Winter Bonus (Code=WO)

The codes are listed in C2:C60

Once the user selects the category, the secnd list contains only leave associated with that leave. The user then selects the leave that apply.

In doing so the code if placed in Column I as a result of an if formula.
=IF(D27=H27,C27," ")

So the will only ever be one code in this range.
I need to take the code range I2:I60 and have it copied to S22.

So I need to search that range and have any text found copied to S22.

Hope that clears things up.

**Aladin Akyurek** · May 25th, 2003, 05:10 AM

Originally Posted by ROBINSYN

...Hope that clears things up.

Not exactly...

You have 2 lists. Categories and Codes (leave codes)

What is the range that houses Categories?

What is the range that houses Codes?

What is the range or the cell where you have set up a dropdown list for Category choice?

What is the range or the cell where you have set up a dropdown list for Code choice?

You state that the Code choice is dependent on the Category choice? How is this dependancy arranged -- by the If formula, =IF(D27=H27,C27," "), that you posted?