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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Yeah, I still don't have the hang of that Lookup(2,1/....) thing :cry:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top