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]

OK, Can you post a small example of before and after. I'd rather try finding a solution to fit than 'best guess'. If you need to mask the company names that's fine but please try to make say a set of 10-20 that are at least fairly representative of your needs.

 

[Abbas999]

OK, Can you post a small example of before and after. I'd rather try finding a solution to fit than 'best guess'. If you need to mask the company names that's fine but please try to make say a set of 10-20 that are at least fairly representative of your needs.

Hi @dave3009

I have created a list of companies. This is most likely extra names of each company.

4Life Inc.

4Life Inc

4Life (inc.)

4Life (inc)

4Life Corporation.

4Life Corporation

4Life Corp.

4Life Corp

4Life Limited.

4Life Limited

4Life Ltd.

4Life Ltd

4Life LLC.

4Life LLC

4Life L.L.C

4Life PLC

4Life PLLC.

4Life PLLC

4Life L.L.p.

4Life LLp.

4Life Lp.

4Life N.V.

4Life N.V

4Life A.G

4Life S.A.

4Life S.A

4Life & Co.

4Life & Co

4Life & Company

4Life Company

4Life Holdings

4Life Holding

4Life Consulting

4Life GmbH.

4Life GmbH

4Life Group.

4Life Group

4Life P.A.

4Life P.A

4Life Pty Limited.

4Life Pty Limited

4Life Pty Ltd.

4Life Pty Ltd

4Life.com

4Life.net

4Life.org

4Life Cpa P.A.

4Life Cpa P.A

4Life Cpa PA

4Life Private Limited.

4Life Private Limited

4Life Private Ltd.

4Life Private Ltd

4Life P.C.

4Life P.C

4Life Consultant

4Life Consultants

<colgroup><col></colgroup><tbody>
</tbody>

Let me know if you can help me.

 

[dave3009]

Hi Abbas999,

This will look at the value and see if any of the exclusions (I didn't do all of them) exist and remove them, it will be fair more exacting than Find/Replace.

Can you try it on a small segment of a COPY of your data and let me know how it goes. If this is close then we can build a bit further.

Sub abbas999()
Dim coName As Variant
Dim part As Variant
Dim exclusions As Variant
Dim newName As String
Dim lstRw As Long
Dim curRw As Long


exclusions = Array("LLC", "LLC.", "P.A", "P.A.", "Ltd", "Ltd.", "Limited", "Limited.", "Co", "Co.")


lstRw = Range("A" & Rows.Count).End(xlUp).Row
For curRw = 1 To lstRw
    coName = Split(Range("A" & curRw), " ")
    For part = 0 To UBound(coName)
        If UBound(Filter(exclusions, coName(part))) >= 0 Then
            'do nothing
        Else
            newName = newName & " " & coName(part)
        End If
    Next part
    Range("A" & curRw) = Trim(newName)
    newName = ""
Next curRw


End Sub