Results 1 to 8 of 8

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. #1
    Board Regular ROBINSYN's Avatar
    Join Date
    Aug 2002
    Posts
    188

    Default 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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,988

    Default Re: FIND TEXT WITHIN A RANGE

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

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

  3. #3
    Board Regular ROBINSYN's Avatar
    Join Date
    Aug 2002
    Posts
    188

    Default 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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,988

    Default Re: FIND TEXT WITHIN A RANGE

    Quote 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. #5
    Board Regular ROBINSYN's Avatar
    Join Date
    Aug 2002
    Posts
    188

    Default 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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,988

    Default Re: FIND TEXT WITHIN A RANGE

    Quote 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. #7
    Board Regular ROBINSYN's Avatar
    Join Date
    Aug 2002
    Posts
    188

    Default Solved!FIND TEXT WITHIN A RANGE

    I got it. Thanks for all the help.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,988

    Default Re: Solved!FIND TEXT WITHIN A RANGE

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com