# FIND TEXT WITHIN A RANGE

This is a discussion on FIND TEXT WITHIN A RANGE within the Excel Questions forums, part of the Question Forums category; Hi There, I have a range A2:A60 (Text only) As a result of input only one cell within this range ...

1. ## FIND TEXT WITHIN A RANGE

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

2. ## Re: FIND TEXT WITHIN A RANGE

Originally Posted by ROBINSYN
...Anyone know of a formula...
I do...

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

3. ## Re: FIND TEXT WITHIN A RANGE

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:

4. ## Re: FIND TEXT WITHIN A RANGE

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?

5. ## Re: FIND TEXT WITHIN A RANGE

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.

6. ## Re: FIND TEXT WITHIN A RANGE

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?

7. ## Solved!FIND TEXT WITHIN A RANGE

I got it. Thanks for all the help.

8. ## Re: Solved!FIND TEXT WITHIN A RANGE

Originally Posted by ROBINSYN
I got it. Thanks for all the help.
What did you do?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•