Find Specific Text in Cell and Return a Specific Value

Topic

New Reply

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

Reply | Quote

Replies

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

45509-Mitch1

Reply | Quote

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&”0123456789″)),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.

Reply | Quote

Kw,

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

Reply | Quote

It was BEFORE I had wine with dinner. I couldn’t do it NOW…LOL.

Reply | Quote

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(120,D3)),120,85))
and copy down.
Or in E2:
=IFERROR(LOOKUP(1E+100,FIND({100,120},D2),{100,120}),85)

Reply | Quote

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

Reply | Quote

Is there a case if there’s NO NUMBER, then return nothing or is it still 85?

Reply | Quote