Extracting Word From String...

[Flipback]

Extracting Word From String and Placing that word on the cell to the left.

Column ?C? is going to have a sentence. There are 4 important words; if any of those 4 words are in the sentence, I need to have that word appear to the left of that cell on Column ?B?.

The words are as follow: Box, Gallon, Kit, Pack.

I would appreciate any help. Thank you.

[Simon Lloyd]

Xld can probably give you a formula solution to this which is quick fast and reliable, but that said MS have a solution here (http://support.microsoft.com/kb/152568)

[Flipback]

The prblem with the code is that for all lines the word will not always be the second. The location of the word will vary upon sentence.

Type the following formula in cell B1:
B1: =FindNthWord(A1,2)

A1: For a comparison of Dow Jones B1: a
A2: Industrial Averages and the price of this B2: Averages
A3: stock over the same quarter, refer to the B3: over
A4: next section of the worksheet. B4: section

Thanks for the Quick Reply

[Bob Phillips]

Here's an array formula

=IF(MAX(COUNTIF(A1,{"*Box*","*Gallon*","*Kit*","*Pack*"})*({1,2,3,4}))>0, INDEX({"Box","Gallon","Kit","Pack"},MAX(COUNTIF(A1,{"*Box*","*Gallon*","*Kit*","*Pack*"})*({1,2,3,4}))),"")

[Simon Lloyd]

Ah the great "El Xld" :bow:
Still having problems with your account there i see

[Bob Phillips]

Fraid so.

[lucas]

still an extra space in the last instance of Kit...will cause problems

[Flipback]

XLD this code is working great. Would you be able to comment the code so I can understand what each part is doing? I am interested in adding more words such as: CASE,BOX,GALLON,KIT,PACK,BAG,OZ,QUART,PINT

Some times words like Gallon will be abbreviated in the sentence as GAL, Quart as QT, Pint as PT.

Thank you very much. your help has been appreciated.

[Bob Phillips]

It is pretty straight-forward, not really a black art.

In essence, it is a simple test, logically

=IF(index_of_word_found>0,INDEX(words,index_of_word_dound,"")

words is simplay an array of the words to check, namely

{"*Box*","*Gallon*","*Kit*","*Pack*"}

index_of_word_found looks for that word by using COUNTIF

COUNTIF(A1,{"*Box*","*Gallon*","*Kit*","*Pack*"})

which returns an array of 0 and 1 valuea, 0 means that word is not found, 1 means it is.

I multiply that result by a simple array of incrementing numbers {1,2,3,4} to get the indexes of matched words.

I then take the max to so as to avoid all the zeros and get a real matching number.

So let's say that A1 contains the phrase 'A gallon of beer' (I'l share it with you later Simon :yes).
COUNTIF(A1,{"*Box*","*Gallon*","*Kit*","*Pack*"}) returns an array {0,1,0,0} (because Gallon is the second word in the checked for list
COUNTIF(A1,{"*Box*","*Gallon*","*Kit*","*Pack*"})*({1,2,3,4}) returns an array of {0,2,0,0}
which if we MAX, we get 2 which is the index of the foudn word in our list.

If the word is found, that is our number is greater than 0, we pass that same formula to the INDEX function to extract that found word from the list

INDEX({"Box","Gallon","Kit","Pack"},MAX(COUNTIF(A1,{"*Box*","*Gallon*","*Ki t*","*Pack*"})*({1,2,3,4})))

which is the same as

INDEX({"Box","Gallon","Kit","Pack"},2) in our example, which returns Gallon.

To extend it, just add more words to the word array, and add more indexes to the index array.

[Flipback]

Thank you it worked Lovely.

This is the modified Code:

=IF(MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PINT*"})*({1,2,3,4,5,6,7,8,9}))>0,INDEX({"BOX","GALLON","KIT","PACK","BAG","CASE","OZ","QUART","PINT"},MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PINT*"})*({1,2,3,4,5,6,7,8,9}))),"EACH")

Normally the word Pint is abbreviated in the sentence as PT and Quart as QT. How would I modifiy the code so that if PT or QT are present in the sentence for it to show The entire word Pint and Quart without modifing the word in the sentence.

I don't have any programming experience so this is a little more complicated for me then if I would have taken a programming class. Thank you for all the help you have given me. The way I would think of doing it is adding PT and QT to the list and having it appear in the list as the rest of them and then Using the replace function to Change all the PT and Qt to Pint and Quart, but surely since you have been able to create this fantastic formula. I am confident that you know a much easier way.

[lucas]

Here is an example from your post...type pt instead of pint and it will return Pint.
=IF(MAX(COUNTIF(C10,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*"," *OZ*","*QUART*","*pt*"})*({1,2,3,4,5,6,7,8,9}))>0,INDEX({"BOX","GALLON"," KIT","PACK","BAG","CASE","OZ","QUART","PINT"},MAX(COUNTIF(C10,{"*BOX*","*GA LLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*pt*"})*({1,2,3,4,5,6,7,8,9}))),"EACH")

[Flipback]

Thank you it worked Exellent!

This is the final Code for this project:

=IF(MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13}))>0,INDEX({"BOX","GALLON","KIT","PACK","BAG","CASE","OZ","QUART","PINT","PINT","QUART","GALLON","PACK"},MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13}))),"EACH")

Thank you once again.

[Simon Lloyd]

Bob, very nicely explained!........so where is it?, i know, don't tell me, it doesnt travel well...or....i had my half first its just it was the bottom half!

Typical southerners, promise you the earth.....!

[Bob Phillips]

Thank you it worked Exellent!

This is the final Code for this project:

=IF(MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13}))>0,INDEX({"BOX","GALLON","KIT","PACK","BAG","CASE","OZ","QUART","PINT","PINT","QUART","GALLON","PACK"},MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13}))),"EACH")

Thank you once again.

Quite honestly, with a list this large, and to cater for additions, I would put the match list (*BOX*, *QT*, etc.) in a range and the display list (BOX, GALLON etc.) in another, and use these ranges in the formula

=IF(MAX(COUNTIF(C1,$M$1:$M$13)*ROW(INDIRECT("1:"&COUNTA($M$1:$M$13))))>0,
INDEX($N$1:$N$13,MAX(COUNTIF(C1,$M$1:$M$13)*ROW(INDIRECT("1:"&COUNTA($M$1:$M$13))))),"EACH")

YOu could even use dynamic ranges to save you having to update it at all

[Bob Phillips]

Bob, very nicely explained!........so where is it?, i know, don't tell me, it doesnt travel well...or....i had my half first its just it was the bottom half!

Typical southerners, promise you the earth.....!

Well I poured it out, and you didn't turn up, so what could I do? I drank it.

[Simon Lloyd]

Hey!, you know i have a bad back so it takes me a little longer to get going!

Give me a head start next time!

[lucas]

OP stated that this was not working in v 2003 in a deleted post:

It works for me in 2003....there are a couple of spaces in the code to resolve probably from posting but it works ok after you remove them....oz is an example.

[lucas]

or....are you using the method suggested by Bob in post #14

[lucas]

Bob has suggested in post #14 that a better way to do this is to use the formula he provided and replace the ranges with dynamic named ranges. This makes it much easier to add, remove, modify the items in both lists....see attached. Works in 2003

[Flipback]

I did not understand:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim r As Long

n = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
Me.Range("A1:A" & n).Name = "MyList"
r = Me.Range("B" & Me.Rows.Count).End(xlUp).Row
Me.Range("B1:B" & r).Name = "MyList2"

End Sub

Since I do not program and Have no idea what Bob was trying to explained. I read that the $ represented an absolute cell but I am not even clear on that. If I were to see one an example working I would probably understand better.

[lucas]

It just creates the named range MyList and MyList2 when you add items to col A and B on that sheet.

[Flipback]

=IF(MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13,14}))>0,INDEX({"BOX","GALLON","KIT","PACK","BAG","CASE","OZ","QUART","PINT","PINT","QUART","GALLON","PACK","SET"},MAX(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","*OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13,14}))),"EACH")

PROBLEM:
LABELED AS PINT INSTEAD OF OZ: SENTENCE: 20 OZ BOTTLE 30W 4 CYCLE OIL OPTI-4 43024

I think this is the part things get tricky.

[Bob Phillips]

If there is a pecking order, you need to put the words in that order.

[Bob Phillips]

ACtually, this will do it without changing the list

=IF(MIN(IF(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","* OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})0,COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","* OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13,14})))>0, INDEX({"BOX","GALLON","KIT","PACK","BAG","CASE","OZ","QUART","PINT ","PINT","QUART","GALLON","PACK","SET"},MIN(IF(COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","* OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})0,COUNTIF(C1,{"*BOX*","*GALLON*","*KIT*","*PACK*","*BAG*","*CASE*","* OZ*","*QUART*","*PT*","*PINT*","*QT*","*GAL*","*PK*","*SET*"})*({1,2,3,4,5,6,7,8,9,10,11,12,13,14})))),"EACH")

but my suggestion of building a list is looking better.

[Flipback]

LABELED AS PINT INSTEAD OF OZ: SENTENCE: 20 OZ BOTTLE 30W 4 CYCLE OIL OPTI-4 43024.

Notice that the word opti is not reffering Pint.
XLD what was the modification on that code? I could not tell.

[Flipback]

I did not understand bobs example. That excel file did not make any sence to me.

[Bob Phillips]

What's the question?