Formula to remove a list of specific words from a cell

L

langleyd

New Member
Joined
Aug 28, 2009
Messages
9
Hi there,

I have a column filled with company names. I need to remove specific words from the cells in the column such as 'the', 'of', 'university' etc. Does anyone know if it is possible to do this using a formula in Excel 2003? I thought I could maybe record a macro and use find/replace but it was removing those strings of letters where they occured within a word too (e.g. 'the' was removed from the word 'southern' leaving the mangled word 'sourn').

A bit of context in case this helps... I have data which I want to match with this list of company names using a lookup function. At present I'm only getting a result where the cell contents match exactly. So, I want to remove as many extraneous words as possible to increase the possibility of getting a match.

Many thanks in advance for any help people can offer.

All the best

Doug
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
You can use wild card in VLOOKUP to look for part of cell, like this

Code: 
=VLOOKUP("*"&A1&"*",E1:F100,2,)

There are few other methods to look for partial cell contents,

Also, have a look at <a href="http://www.mrexcel.com/forum/showthread.php?p=955462#post955462">
Alan's Fuzzy lookup functions</a>

If you still need some macro solution, post some sample data
 
Upvote 0
Hi Doug

There's no general solution to your problem using a formula. You could do it in special cases with a lot of substitutes and helper cells but it would be much easier if you use vba.

Is it ok a vba solution?

If it's ok post the location of the data and the location of the list of "unwanted" words/signs (I guess you also want to get rid of dots and commas, or even to consider only the letters).

You may also want to look into Alan's Fuzzy Match:

http://www.mrexcel.com/forum/showthread.php?t=195635
 
Upvote 0
Regardless of the method you use, before doing the search/find, place a space before the first character of the cell and after the last character. Then rather than searching for of, search for XofX where X is a space.
 
Upvote 0
pgc01 said:
Hi Doug

There's no general solution to your problem using a formula. You could do it in special cases with a lot of substitutes and helper cells but it would be much easier if you use vba.

Is it ok a vba solution?

If it's ok post the location of the data and the location of the list of "unwanted" words/signs (I guess you also want to get rid of dots and commas, or even to consider only the letters).

You may also want to look into Alan's Fuzzy Match:

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html
Click to expand...

I Know this has been a long time but it's still relevant today. Can someone have a solution for this? VBA is OK. It would be great if the list of words to be removed are stored somewhere in the workbook so it's user-editable.
 
Upvote 0
nvu1991 said:
I Know this has been a long time but it's still relevant today. Can someone have a solution for this? VBA is OK. It would be great if the list of words to be removed are stored somewhere in the workbook so it's user-editable.
Click to expand...
Welcome to the MrExcel board!

Could you give the following?
a) a small varied sample of your original data (say 8-10 rows)
b) a small list of the 'words' to be removed (say 3 words)
c) the expected results for the data from a) after the macro has been run
 
Upvote 0
I post this straight from Excel since I cannot upload files or pictures. Hope this is clear enough

InputResultRemoval
Apple computerApplecomputer
Apple Co. Ltd.Appleco.
Orange universityOrange universityltd.
Everest trading and mining companyEverest trading mining company
Fedex transportation co.Fedextransportation
Fedex inc.Fedexinc.
Fedex International transportation co.Fedex Internationaland

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Nobody can upload files to the forum but for the future you might investigate the link in my signature block below so that you can post small screen shots like I have in this post. makes it much easier to see where data is (& can also show formulas etc)

Anyway, thanks for the sample data.

I think this user-defined function might help. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I have made the assumption that the list of words to be replaced is in a column with no blank cells among the list.

Code: 
Function ClearWords(s As String, rWords As Range) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\b" & Replace(Join(Application.Transpose(rWords), "|"), ".", "\.") & "\b"
  ClearWords = Application.Trim(RX.Replace(s, ""))
End Function

Excel Workbook
ABCD
1InputResultRemoval
2Apple computerApplecomputer
3Apple Co. Ltd.Appleco.
4Orange universityOrange universityltd.
5Everest trading and mining companyEverest trading miningcompany
6Fedex transportation co.Fedextransportation
7Fedex inc.Fedexinc.
8Fedex International transportation co.Fedex Internationaland
Sheet1
 
Last edited:
Upvote 0
First, thanks very much for your help. Second, WOW, stunning. It works, it's versatile, easily examinable, undo-able, clean. Such a perfect solution. Again, stunning :eek:.
 
Upvote 0
You must log in or register to reply here.

Similar threads

dcofer
  • Solved
Locating Words With Specific Letters in a Column
Replies
5
Views
141
dcofer
dcofer
M
  • Solved
Formula to find words in list of text data
Replies
9
Views
292
AhoyNC
A
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
120
DanteAmor
DanteAmor
S
  • Question
Open and print a section of word document as pdf
Replies
6
Views
177
StephenBart
S
A
  • Solved
Lookup cell address of cell containing specific word, then next occurrence and so on.
Replies
8
Views
212
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

Share this page

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