I need to give a cell a value based on finding specific text within a string of text

Instead of creating a monster formula I'm thinking you could build a lookup table and use some type of lookup formula to do this.

Post a few examples of what could be in cell B3.

ex 1: Cathedral Top Center Mullion Vinyl Shutters, w/Installation Shutter-Lok's| Shutter Color| 1-White;Shutter Height| 25";Shutter Width| 12"
ex 2: Cathedral Top Center Mullion Vinyl Shutters, w/Installation Shutter-Lok's| Shutter Color| 4-Wedgewood Blue;Shutter Height| 31";Shutter Width| 12"
ex 3: Cathedral Top Center Mullion Vinyl Shutters, w/Installation Shutter-Lok's| Shutter Color| 27-Burgundy Red;Shutter Height| 80";Shutter Width| 14-1/2"

I specifically need for the values that have the inches " symbol after it for example 25" to be identified and trigger the formula...I just noticed that I have an issue with the value of 36. In the example below, I need my formula to be triggered by the 25" value and not the 36. My current formula presents a problem with several of my strings as it also contains the value of 36. But I only need the value of 36" to trigger the formula and not 36. I hope that makes sense.

ex: Cathedral Top Center Mullion Vinyl Shutters, w/Installation Shutter-Lok's| Shutter Color| 36-Classic Blue;Shutter Height| 25";Shutter Width| 12"

Since some of the strings have multiple numbers in them this may not work 100% of the time.

Create a 2 column table like this...

Data Range

	B	C
5	Size	Price
6	25	14.20
7	31	16.73
8	36	17.74
9	39	19.54
10	43	20.29
11	48	21.99
12	52	23.52
13	55	24.91
14	60	26.61
15	64	29.07
16	67	30.76
17	72	32.45
18	75	33.31
19	80	34.16

Then, the formula would be:

=IFERROR(LOOKUP(1E100,FIND(B6:B19,B3),C6:C19),"")

Can you post a small sample file that contains only the data below?

Include the result(s) you expect.

Originally Posted by Ruski1106

Description Price
Shutter Height| 25";Shutter Width| 5-3/4" 40.9
Shutter Height| 31";Shutter Width| 5-3/4" 44.79
Shutter Height| 36";Shutter Width| 5-3/4" 48.7
Shutter Height| 39";Shutter Width| 5-3/4" 54.06
Shutter Height| 43";Shutter Width| 5-3/4" 58.01
Shutter Height| 48";Shutter Width| 5-3/4" 65.92
Shutter Height| 52";Shutter Width| 5-3/4" 69.89
Shutter Height| 55";Shutter Width| 5-3/4" 73.84
Shutter Height| 60";Shutter Width| 5-3/4" 81.75
Shutter Height| 64";Shutter Width| 5-3/4" 85.7
Shutter Height| 67";Shutter Width| 5-3/4" 89.65
Shutter Height| 72";Shutter Width| 5-3/4" 97.57
Shutter Height| 75";Shutter Width| 5-3/4" 101.52
Shutter Height| 80";Shutter Width| 5-3/4" 105.48

Originally Posted by Ruski1106

I specifically need for the values that have the inches " symbol after it for example 25" to be identified and trigger the formula...I just noticed that I have an issue with the value of 36. In the example below, I need my formula to be triggered by the 25" value and not the 36. My current formula presents a problem with several of my strings as it also contains the value of 36. But I only need the value of 36" to trigger the formula and not 36. I hope that makes sense.

ex: Cathedral Top Center Mullion Vinyl Shutters, w/Installation Shutter-Lok's| Shutter Color| 36-Classic Blue;Shutter Height| 25";Shutter Width| 12"

With this new info, disregard the suggestion in post #5.

Can we assume the number of interest will ALWAYS immediately follow the string "Height| " ?

On a side note...

Please don't edit a post after someone has already replied to that post.

This formula returns price by lookup given strings in price table in cell B8:

=LOOKUP(10^10,FIND('Description and Price'!$A$2:$A$15,'Main Spreadsheet'!B8),'Description and Price'!$B$2:$B$15)

Drag down.

This is a bit involved. A file is attached to follow along. Hope this helps.

In sheet Description and Price build a lookup table. Start by extracting the heights and widths from column A into columns C and D with these formulas. In column C

Formula:

 =--MID($A2,FIND("|",$A2)+1,3) 

in column D

Formula:

 =MID($A2,LOOKUP(25^25,FIND("|",$A2,ROW($1:$50)))+2,99) 

Then to make row and column headers for the lookup table array-enter this formula in F2 and fill down to F15.

Formula:

=SMALL(IF(FREQUENCY($C$2:$C$127,$C$2:$C$127),$C$2:$C$127),ROWS($1:1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
That will extract the unique height values. Then to extract unique column headers enter this array-entered formula in G1 and fill across to O1. (Again committing with Ctrl + Shift + Enter.)

Formula:

 =INDEX($D$2:$D$127,SMALL(IF(FREQUENCY(MATCH($D$2:$D$127,$D$2:$D$127,0),ROW($A$2:$A$127)-1),ROW($A$2:$A$127)-1),COLUMNS($A:A))) 

Now that the headers are in place enter this formula in the regular way into G2:O15.

Formula:

 =SUMIFS($B$2:$B$127,$C$2:$C$127,$F2,$D$2:$D$127,G$1) 

That will put prices at the intersections of height and width.

With that in place go to the Main Spreadsheet and insert two helper columns that will be the new columns C and D. (BTW: this will also make the StoreCost column M now StoreCost column O.) In C2 enter this formula and fill down.

Formula:

 =IFERROR(--MID(B2,SEARCH(""""&";",B2)-2,2),"no size") 

and in D2 this formula.

Formula:

 =TRIM(RIGHT(IF(ISNUMBER(C2),SUBSTITUTE(B2," ",REPT(" ",20)),""),20)) 

These extract height and width from the strings in column B and will be your lookup values for the final formula in the StoreCost column (now column O).

In O2 enter this formula and fill down.

Formula:

 =IF(ISNUMBER($C2),INDEX('Description and Price'!$G$2:$O$15,MATCH('Main Spreadsheet'!$C2,'Description and Price'!$F$2:$F$15,0),MATCH('Main Spreadsheet'!$D2,'Description and Price'!$G$1:$O$1,0)),"no size") 

Hope this helps.

BTW I failed to mention that those array formulas to return the unique headers for the lookup table could also be done by copying the helper column results / pasting values into temporary columns. Then applying Data > remove duplicates. Then copy / paste those results into the lookup table header positions. Column headers would have to be pasted as transposed.

It's simpler.

Hi Ruski,
This is in response to your post #20:

Sub Ruski(): Dim n As Long, H As String, W As String, HW As String
Dim wm As Worksheet, wd As Worksheet, Z, P, Q, r As Long
Set wm = Sheets("Main Spreadsheet"): Q = wm.UsedRange
Set wd = Sheets("Description and Price"): P = wd.UsedRange

'From Description And Price- only the height and width
    With CreateObject("Scripting.Dictionary")
For r = 2 To UBound(P): Z = Split(P(r, 1), "|")
H = Mid(Z(1), 2, InStr(1, (Z(1)), Chr(34)) - 1)
W = Mid(Z(2), 2, InStr(1, (Z(2)), Chr(34)) - 1)
H = Replace(H, Chr(34), ""): W = Replace(W, Chr(34), "")
HW = H & "|" & W: .Item(HW) = P(r, 2): Next r 'The Cost

        'From Main Spreadsheet-again the height and width
For r = 2 To UBound(Q): Z = Split(Q(r, 2), "|"): n = UBound(Z)
            If n Then
W = Mid(Z(n), 2, InStr(1, (Z(n)), Chr(34)) - 1)
H = Mid(Z(n - 1), 2, InStr(1, (Z(n - 1)), Chr(34)) - 1)
H = Replace(H, Chr(34), ""): W = Replace(W, Chr(34), "")
                HW = H & "|" & W
        If .Exists(HW) Then Q(r, 13) = .Item(HW) 'The Cost
           End If:     Next r
wm.Range("A1").Resize(UBound(Q, 1), UBound(Q, 2)) = Q
End With: End Sub

* This should run quickly for a large sheet

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

You're welcome and thanks for the rep!