# Thread: Find Specific Text in Cell and Return a Specific Value

1. ## 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

2. 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

3. 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.

4. Kw,

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

5. It was BEFORE I had wine with dinner. I couldn't do it NOW...LOL.

6. 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)

7. Awesome. They all work. Thanks. Last one (Rory) is simple and even I can understand. Again, thanks!

8. 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
•