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

**Ruski1106** · 09-30-2015, 08:41 PM

I have strings of text with words and numbers in column A that contain a specific numbers (25,31,36.....80). I want to assign a numeric value in a column B based on the specific value found in the aforementioned string of text in column A. I came up with the function below but it only seems to return the value of "14.20" or the first value based on finding the value of "25" in the selected string. Once it gets to the other values in the list, like "31", and even if the string contains it, I get #Value!. Thank you

=IF(FIND("25",B3),"14.20",IF(FIND("31",B3),"16.73",IF(FIND("36",B3),"17.74",IF(FIND("39",B3),"19.54",IF(FIND("43",B3),"20.29",IF(FIND("48",B3),"21.99",IF(FIND("52",B3),"23.52",IF(FIND("55",B3),"24.91",IF(FIND("60",B3),"26.61",IF(FIND("64",B3),"29.07",IF(FIND("67",B3),"30.76",IF(FIND("72",B3),"32.45",IF(FIND("75",B3),"33.31",IF(FIND("80",B3),"34.16"))))))))))))))

**Pete_UK** · 09-30-2015, 08:49 PM

You should wrap ISNUMBER(...) around each FIND term, like this:

=IF(ISNUMBER(FIND("25",B3)),"14.20",IF(ISNUMBER(FIND("31",B3)),"16.73",IF(ISNUMBER(FIND("36",B3))," ...

and so on.

Hope this helps.

Pete

**Tony Valko** · 09-30-2015, 08:55 PM

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.

**Ruski1106** · 09-30-2015, 09:07 PM

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"

**Tony Valko** · 09-30-2015, 09:20 PM

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),"")

**Ruski1106** · 09-30-2015, 09:24 PM

Thanks Pete...this generated values for everything but when checking some strings, they triggered the wrong value in column B. Below I explain another issue with the value of 36.

**Tony Valko** · 09-30-2015, 09:36 PM

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.

**Ruski1106** · 09-30-2015, 09:45 PM

I'm sorry about the edit..I'm new to this and didn't know that a reply had posted. And, yes, the values of interest are height values and will always follow the text "Height| ". thank you

**Ruski1106** · 09-30-2015, 10:10 PM

I figured it out thanks to both of you Tony and Pete...thank you very much for the help!!!!

**Tony Valko** · 10-01-2015, 05:34 AM

Good deal. Thanks for the feedback!

**Ruski1106** · 10-14-2015, 08:02 PM

Tony,

I have thousands of prices that I need to enter so I was thinking maybe your idea would work best; however, I am having trouble understanding what the "1E100" and the "B3" items represent. I've started making my table and so far I have this. I'm using "desription" instead of "size" like you have in column B, which I assume is okay but could be wrong. And then I'm using "price" in column C as you have. Would you be able to explain your formula in terms of my chart below. Please keep in mind that I will be adding many more values to "description" and "price" as I have thousands of prices to populate. Thank you for your help.

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

**Tony Valko** · 10-15-2015, 11:55 AM

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

Include the result(s) you expect.

Originally Posted by Ruski1106

**Ruski1106** · 10-15-2015, 10:21 PM

Current Custom Louver spreadsheet 6 items.xlsx

In "main spreadsheet (tab 1)" tab I have all of my data except "StoreCost (column: M, tab 1)" which I hope this formula will populate. In "description and price (tab 2)" tab is where I started making your table suggestion. I want the formula to take the content in "Name (column: B, tab 1)" and determine if that string of text contains the text identified in the first column of your table suggestion or in my case "Description (column: A, tab 2)" and then if the string of text in "Name (column: B, tab 1)" does contain the string of text given in the table, to associate the "Price (column B, tab 2) which is actually cost and enter that value into the "StoreCost (column: M,tab 1)", where the formula would be entered.

**Tony Valko** · 10-16-2015, 06:05 AM

Sorry, your file is too big.

I have download size limits!

**Ruski1106** · 10-29-2015, 02:37 PM

Current Custom Louver spreadsheet 6 items smaller size version.xlsx

I deleted many rows to make smaller. Please keep in mind I will have thousands of rows of data in tab 1 and tab 2 is partially completed as well.

thanks again

**JohnTopley** · 10-29-2015, 03:09 PM

I added an extra column to your price table to contain the height (Column B) using formula

=MID($A2,17,2).

Using this formula in Column M ..

=IFERROR(VLOOKUP(MID(B7,FIND("Height",B7,1)+8,2),'Description and Price'!$B$2:$C$15,2,0),"")

to find the price

Will this work for you?

**Ruski1106** · 11-08-2015, 10:01 PM

No, price/cost is a combination of height and width. For example, for a shutter that has a height of 36" and width of either (5-3/4", 8-1/2", 9-1/4", 11", 12", 13", and 14-1/2"), the price or cost is $48.70. However, for a height of 36" and width of 16-1/2", the cost is $50.75. And, for a height of 36" and width of 18", the cost is $52.81. Basically, depending on the height of the shutter, there are 3 cost groups based on width: 5-3/4" to 14-1/2" is the first cost group, 16-1/2" is the second, and 18" is the 3rd cost group based on width. Keep in mind that cost goes up if height changes as well. My spreadsheet doesn't show that because I didn't complete the table. Thank you for your help.

**bebo021999** · 11-08-2015, 10:36 PM

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

Please Login or Register  to view this content.

Drag down.

**Ruski1106** · 11-08-2015, 11:51 PM

this formula didn't seem to return the correct values for cost.

**Ruski1106** · 11-09-2015, 12:17 AM

smaller-size-version.xlsx

I have completed the "description and price" table so there isn't any confusion. Notice that all WIDTHS 5-3/4" through 14-1/2" have the same cost if they share the same shutter height. The 16-1/2" width (highlighted in red) has a completely new cost structure. Similarly, the 18" width (highlighted in blue) has a unique cost structure as well. I need to somehow assign cost values from the "description and price" tab to column "M" (store cost) in "main spreadsheet" based on the description in column B of the main spreadsheet which has the various widths and heights that correspond to the price table in tab 2.

**FlameRetired** · 11-09-2015, 01:54 AM

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:

Please Login or Register  to view this content.

in column D

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

and in D2 this formula.

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Hope this helps.

**FlameRetired** · 11-09-2015, 06:05 PM

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.

**xladept** · 11-09-2015, 08:11 PM

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

Please Login or Register  to view this content.

* 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

**Ruski1106** · 11-10-2015, 07:25 PM

This worked perfectly. Thank you so much!!

**xladept** · 11-10-2015, 07:27 PM

You're welcome and thanks for the rep!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Need Formula to lookup for specific text in a string and give desired output.

[SOLVED] Need to search cell for text string and paste text in based on finding

[SOLVED] Finding Specific Text within a String of Text

Conditional formating based on specific text in string in another cell

[SOLVED] Finding specific text in string - Part II

[SOLVED] Finding specific text in a string

[SOLVED] Finding Specific Text in a Text String

Bookmarks

Bookmarks

Posting Permissions