Solution required for a repeating Excel Work

[Abbas999]

Hi All,

I regularly have some excel sheets to filter. From each sheet i have to do two things.

First Thing
Removing companies lables i.e

1. inc.
2. ltd.
3. Corp.
4. LLC, PLLC, LP, P.C., N.V, S.A and similar.
5. Consultting, Holding, Group, Company, GMBPH and similar.

Second Thing
There is column with Full names. I have to split them into First and last names at the end (it's possible with text to columns feature) . But before that I have to remove,

Middle names, Prefix, CFA, MBA, CPC, PH.D Esq. and many more.
Also Middle names i.e:
Q. W. E. R. and so on to M. (all keyboard letters)


I was doing all this find and replace. But this is becoming boring as i have to regularly do this. I am hoping for a one click solution. Like two VBA codes. One for Names and Second code for companies.

Feeling very hopeful because i got many excel problems solved here. Thanks

 

[dave3009]

Hi Abbas999,

My first suggestion would be to power up the macro recorder and see what Excel spits out for the changes you make, you can usually trim out some of the recorded code but with say 100 replacements it wouldn't matter a great deal, compared to manually doing it.

My next suggestion would be to have a list of search values you wish to replace, loop through this in your code making the changes. Benefit there being you can just add any new values to the list and it'd run through OK.

If you want a bit of help with the latter then let me know and I'll put together a small example for you to expand.

HTH

Dave

 

[Abbas999]

Hi Abbas999,

My first suggestion would be to power up the macro recorder and see what Excel spits out for the changes you make, you can usually trim out some of the recorded code but with say 100 replacements it wouldn't matter a great deal, compared to manually doing it.

My next suggestion would be to have a list of search values you wish to replace, loop through this in your code making the changes. Benefit there being you can just add any new values to the list and it'd run through OK.

If you want a bit of help with the latter then let me know and I'll put together a small example for you to expand.

HTH

Dave

Thank you for reply and suggestions Dave. But I never used macro option and never created a VBA code and never edit a code. I just know basic Excel. basically I am in Learning period.

before that issue I was having another issue of removing all html tags from a sheet except some tags " and
and more minimal tags.
While searching at Google. I found a interesting solution that works about 90%. It was a VBA code and In the code i can easily see the exception tags line (i just understand this line in the whole code ). Some tags were already there so i did add more that i want to remove.

I am looking for similar solution so that if i found anything new to remove in the sheets i would add the word to the VBA code.

 

[Abbas999]

Hi Abbas999,

My first suggestion would be to power up the macro recorder and see what Excel spits out for the changes you make, you can usually trim out some of the recorded code but with say 100 replacements it wouldn't matter a great deal, compared to manually doing it.

My next suggestion would be to have a list of search values you wish to replace, loop through this in your code making the changes. Benefit there being you can just add any new values to the list and it'd run through OK.

If you want a bit of help with the latter then let me know and I'll put together a small example for you to expand.

HTH

Dave

@dave3009

I have tried macro on names and it is working good. In some sheets i don't have a word to remove but in next sheet it does have. So my question is if for the first time i put all the required words myself into a "Sheet 1" and Click on "Record Macro" and doing all the replacements using find and replace and Done!

but as i said all sheets are different I mean If first sheet has a word "MBA" to be removed then Sheet 2 May not have this word. I will manually add all the words that i want to remove in a sample sheet and Record the macro. But i am worried that Will the macro be working fine if other sheets have less words?

 

[dave3009]

Hi Abbas999,

Here is a solution for you, I have added some comments so you know what each bit is doing. Just add more values to the array.

Test on a copy of your workbook

Press Alt+F11 to enter VBEditor
Go to Insert | New | Module
Paste in the code
Press Alt+Q to close VBEditor

Press Alt+F8 select Abbas999 macro and click Run.

Option Explicit


Sub Abbas999()
Dim searches As Variant
Dim i As Long




'Make an array to hold values that you want to search for
searches = Array("inc.", "ltd.", "corp.", "....") 'Add more values as necessary


'Loop through the array
For i = 1 To UBound(searches)
    'Replace any occurrences of the array values with nothing
    Cells.Replace What:=searches(i - 1), Replacement:=""
Next i
'End the routine
End Sub

HTH

Dave

 

[Abbas999]

Hi Abbas999,

Here is a solution for you, I have added some comments so you know what each bit is doing. Just add more values to the array.

Test on a copy of your workbook

Press Alt+F11 to enter VBEditor
Go to Insert | New | Module
Paste in the code
Press Alt+Q to close VBEditor

Press Alt+F8 select Abbas999 macro and click Run.

Option Explicit


Sub Abbas999()
Dim searches As Variant
Dim i As Long




'Make an array to hold values that you want to search for
searches = Array("inc.", "ltd.", "corp.", "....") 'Add more values as necessary


'Loop through the array
For i = 1 To UBound(searches)
    'Replace any occurrences of the array values with nothing
    Cells.Replace What:=searches(i - 1), Replacement:=""
Next i
'End the routine
End Sub

HTH

Dave

Putting all words into the code at the moment. Will test in a few minutes. One quick Question. Can this also be applied to Name? I mean for removing Middle names and preffix?

 

[dave3009]

If you currently do this using find/replace in Excel then yes, the same method will apply. You could do it all at once IF it is the case that you use find/replace to do ALL of it currently.

If the Array becomes to long to view just put an underscore _ and press enter to begin a new line, this way VBA will know you are continuing.

Alternatively you can make a sheet with all the search values and just add to it as you go along, instead of looping through the array we would just loop through that range.

 

[Abbas999]

If you currently do this using find/replace in Excel then yes, the same method will apply. You could do it all at once IF it is the case that you use find/replace to do ALL of it currently.

If the Array becomes to long to view just put an underscore _ and press enter to begin a new line, this way VBA will know you are continuing.

Alternatively you can make a sheet with all the search values and just add to it as you go along, instead of looping through the array we would just loop through that range.

Hi Dave It almost went great. But Something is wrong there.
can you add something in code to look for words that comes to the end of each word.
I mean Boes Corp.
So i am adding "Corp." to code.
Code should only look for only last words of companies.

Because it doing some damage For example.

Company name before code = Alvin & Company
Company Name after code = Alvinmpany

because i had alot of names to remove, Like i have added & Co "Co" "Co." " Co. Inc." and also "Company"
So it has only counted it as & Co and removed the word from half of company name.

lot's of company names are damaged so you if you make the vba code understand to look for only last names of Cell. That would be great.

 

[dave3009]

How would you expect a name like

J W Drilling Company Corp Inc Collieries Ltd

To look?

You would probably be better using text to columns then searching for the exact values of cell to remove the extraneous parts, then concatenate them back together.

 

[Abbas999]

How would you expect a name like

J W Drilling Company Corp Inc Collieries Ltd

To look?

You would probably be better using text to columns then searching for the exact values of cell to remove the extraneous parts, then concatenate them back together.

Dave I tried that and It is almost 85% Accurate. All it needs now is just a command to only look for these words at the end of each company name. Is that possible? I think doing this will give me at least 99% accurate results.