Excel 2002 – text extraction issue

robertsmyth100

Board Regular
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

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?

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

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

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

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

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)

That's really, really clever

Yeah, I still don't have the hang of that Lookup(2,1/....) thing

Could do

=LOOKUP(9.999+E307,SEARCH(\$D\$1:\$D\$41,A1),\$D\$1:\$D\$41)

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

Replies
5
Views
492
Replies
3
Views
711
Replies
3
Views
630
Replies
2
Views
820
Replies
10
Views
1K

1,220,966
Messages
6,157,126
Members
451,399
Latest member
alchavar

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.

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

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