FIND TEXT WITHIN A RANGE

ROBINSYN

Board Regular
Joined
Aug 19, 2002
Messages
188
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 :rolleyes:
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:
 
Upvote 0
ROBINSYN said:
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?
 
Upvote 0
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. :oops:
 
Upvote 0
ROBINSYN said:
...Hope that clears things up. :oops:

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?
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top