Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Specific Text in Cell and Return a Specific Value

    Hey, I am wanting to find specific text in a column and return a specific value depending upon what is found in the cell. See the text box inside the attached for a detailed explanation on what I am looking for.

    Many thanks!

    Mitch
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Mitch,

    Here is a UDF to achieve what you want:

    In a standard module:
    Code:
    Public Function DESC(rng As Range) As Integer
    If InStr(1, rng, "83", vbTextCompare) > 0 Then DESC = 85:: Exit Function
    If InStr(1, rng, "85", vbTextCompare) > 0 Then DESC = 85:: Exit Function
    If InStr(1, rng, "100", vbTextCompare) > 0 Then DESC = 100:: Exit Function
    If InStr(1, rng, "120", vbTextCompare) > 0 Then DESC = 120:: Exit Function
    DESC = 85
    End Function
    In Cell F2 =DESC(D2) then copy down

    HTH,
    Maud

    Mitch1.png
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud's UDF is best, but this ugly formula will extract the number from the cell (fill down).

    =IFERROR(--MID(D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"01234567 89")),FIND(" ",D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789 ")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789"))), "")

    This also assumes that if there's a number, there is a space after it.
    Last edited by kweaver; 2016-09-07 at 20:44.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Kw,

    How did you muster the fortitude to write that formula and then understand it to boot?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    It was BEFORE I had wine with dinner. I couldn't do it NOW...LOL.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    As far as I can see, the logic boils down to:
    If there's 100, return 100; if there's 120, return 120; otherwise return 85. In which case, E2 could contain:
    =IF(ISNUMBER(FIND(100,D3)),100,IF(ISNUMBER(FIND(12 0,D3)),120,85))
    and copy down.
    Or in E2:
    =IFERROR(LOOKUP(1E+100,FIND({100,120},D2),{100,120 }),85)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Awesome. They all work. Thanks. Last one (Rory) is simple and even I can understand. Again, thanks!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is there a case if there's NO NUMBER, then return nothing or is it still 85?

Posting Permissions

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