Excel 2002 – text extraction issue

robertsmyth100

Board Regular
Joined
Sep 25, 2006
Messages
94
Hello everybody,

I have a small issue that I was hoping for some help with. Basically I have a list of words which contain certain words that I wish to extract and put in another column. The words are in the text in different places (start, middle, end) and some are separated by a space and sometimes not. The list of words that I want to extract are in separate table. So for example my list of words are:

Acid nig
acid hep
hat acid
alki hed
alkitex
ttpf alki
pet dev
pethep
nig pet

And the list of words I want to extract and put in a column next to them are:
acid
alki
pet

So my final table would look a like this.
Acid nig acid
acid hep acid
hat acid acid
alki hed alki
alkitex alki
ttpf alki alki
pet dev pet
pethep pet
nig pet pet

this table didnt come out very well but bascially the third word in each row is the 2nd column.

Any help would be mucho appreciated…
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

=IF(ISNUMBER(SEARCH("acid",A1)),"acid",IF(ISNUMBER(SEARCH("alki",A1)),"alki",IF(ISNUMBER(SEARCH("pet",A1)),"pet","")))

copied down, where A1 houses 1st string.
 

robertsmyth100

Board Regular
Joined
Sep 25, 2006
Messages
94
cheers! That certainly does work. The list of words I want to extract totals about 40, so I’ll have to edit that formula adding each word, which will mean a rather large formula, is there an easier way to do it?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I was afraid you were going to ask that :wink:

Try this:
Book1 Sep25.xls
ABCDE
1Acid nigacidLIST
2acid hepacidacid
3hat acidacidalki
4alki hedalkipet
5alkitexalki
6ttpf alkialki
7pet devpet
8petheppet
9nig petpet
10
Sheet1


Formula in B1: =IF(OR(ISNUMBER(SEARCH($E$2:$E$4,A1))),INDEX($E$2:$E$4,SUM(IF(ISNUMBER(SEARCH($E$2:$E$4,A1)),ROW($E$2:$E$4)))-ROW($E$2)+1),"")

Where E2:E4 is the range housing your list. Adjust as necessary.

The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

You should see {} brackets around the formula if confirmed correctly.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi

Here's a VBA route which involves a User Defined Function (you'd need to copy the code into a module in the workbook holding this data):

Code:
Function MatchTxt(rCell As Range, rLkUpRng As Range) As String
Dim regex As Object, currMatches, c As Range
Set regex = CreateObject("VBScript.RegExp")
MatchTxt = ""
For Each c In rLkUpRng
        With regex
            .Pattern = c.Value
            .Global = True
        End With
    Set currMatches = regex.Execute(rCell.Value)
    If currMatches.Count > 0 Then MatchTxt = currMatches(0): Exit Function
Next c
End Function

You can then use it in your sheet thus:

=MatchTxt(A1,$C$1:$C$40)

where the string you want to search (such as "Acid nig") is in A1 and the values you want to look up are contained in the range B1:B40. Note that the function exits the moment it finds a match, therefore if you have two potential matches (such as "acid pet"), it will only retunr the first.

Please post back if you have any trouble.

Richard
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If your list of words to search for is in D1:D41 try this formula in B1 copied down

=LOOKUP(2,1/SEARCH(D$1:D$41,A1),D$1:D$41)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Yeah, I still don't have the hang of that Lookup(2,1/....) thing :cry:
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi Barry:


Very interesting!!!!

Could you briefly explain a bit on the "2,1/Search" portion of your formula =LOOKUP(2,1/SEARCH(D$1:D$41,A1),D$1:D$41)?

plettieri
 

Forum statistics

Threads
1,141,098
Messages
5,704,320
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top