Cleaning up company names?

J

jpbertram

New Member
Joined
Jul 18, 2013
Messages
4
I'm trying to clean up company names in a large spreadsheet. For example, get rid of the "Inc.", "Corp.", etc.

Is there an formula to automatically normalize all entries in a single column?

Thanks!

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { color: black; }</style>
Old Republic Title Company
American Reprographics Company, L.L.C.
Symphony Teleca Corporation
American Baptist Homes of The West
Svb Financial Group
Symphony Teleca Corporation
Wilson Sonsini Goodrich & Rosati, Professional Corporation
Osisoft, LLC
Risk Management Solutions, Inc.
Epicor Software Corporation
Marin Health Care District
Prologis, Inc.
Fenwick & West LLP
Symphony Technology Group, L.L.C.
Universal Building Services and Supply Co.
Woodruff-Sawyer & Co.
American Baptist Homes of The West
Tectura Corporation
Hcl America Inc.
Eah Inc.
Healthright 360
La Clinica De La Raza, Inc.
Abbott Vascular Inc.
Trinet Group, Inc.
Marklogic Corporation
Wested
Workday, Inc.
Genesys Telecommunications Laboratories, Inc.
Extend Health, Inc.
Linkedin Corporation
Fair Isaac Corporation
Exigen (usa), Inc.
Tiburon, Inc.
Cisco Webex LLC
Charles Schwab & Co., Inc
Sonicwall, Inc.
Epicor Software Corporation
Bingham McCutchen LLP
Successfactors, Inc.
Orrick, Herrington, Sutcliffe LLP
Linkedin Corporation
Genesys Telecommunications Laboratories, Inc.
California Physicians' Service
Genesys Telecommunications Laboratories, Inc.
Sequoia Equities, Incorporated
Cooley LLP
El Camino Hospital
Risk Management Solutions, Inc.
Cypress Security, LLC
Essex Property Trust, Inc.
Good Fellow Top Grade Construction, LLC
Public Health Institute
The PMI Group Inc
RCM Capital Management LLC
Hanson Bridgett LLP
Advantis Global, Inc.
Braden Partners, L.P., A California Limited Partnership
Taos Mountain, Inc.
Systems Integration Solutions Inc
Symphony Technology Group, L.L.C.
Quinstreet, Inc.
Cmg Mortgage, Inc.
Risk Management Solutions, Inc.
Svb Financial Group
Paypal, Inc.
Astreya Partners, Inc.
Wageworks, Inc.
Netsuite, Inc.
E2open, Inc.
Responsys, Inc.
Bonhams & Butterfields Auctioneers Corporation
American Baptist Homes of The West
Hanna, Brophy, Mac Lean, Mc Aleer & Jensen, LLP
Triage Consulting Group
McGrath Rentcorp
Oil Changer, Inc.
Douglas Parking Company LLC
Telecare Corporation
Sonicwall, Inc.
Saba Software, Inc.
Risk Management Solutions, Inc.
Prologis, Inc.
Freeborders, Inc.
Cooley LLP
E4e, Inc.
Linkedin Corporation
Visa International Service Association
Actian Corporation
It Convergence
Fair Isaac Corporation
Symphony Teleca Corporation
Linkedin Corporation
Check Point Software Technologies, Inc.
Check Point Software Technologies, Inc.
Visa International Service Association
Trulia, Inc.
M. Arthur Gensler Jr. & Associates, Inc.
Selectquote Insurance Services
Kinetic Systems, Inc
Hcl America Inc.
Exponent, Inc.
American Cancer Society California Division, Inc
Blood Centers of The Pacific
Risk Management Solutions, Inc.
Essex Property Trust, Inc.
Osisoft, LLC
Vm Services, Inc.
American Baptist Homes of The West
First American Title Guaranty Holding Company
Blood Centers of The Pacific
American Cancer Society California Division, Inc
La Clinica De La Raza, Inc.
Taleo Corporation
Orrick, Herrington, Sutcliffe LLP
Qualys, Inc.
Actuate Corporation
CBS Interactive Inc.
Armanino McKenna LLP
Splunk Inc.
It Convergence
Trinet Group, Inc.
Tibco Software Inc.
Trinet Group, Inc.
Delta Dental of California
Visa International Service Association
Informatica Corporation
Paypal, Inc.
Linkedin Corporation
Trianz, Inc.
Trulia, Inc.
Delta Dental of California
Visa International Service Association
Fair Isaac Corporation
Trinet Group, Inc.
M. Arthur Gensler Jr. & Associates, Inc.
Burr Pilger Mayer, Inc.
Lucasfilm Ltd., Llc.
Harris & Associates, Inc.
Proofpoint, Inc.
Orrick, Herrington, Sutcliffe LLP
Responsys, Inc.
IAC Search & Media, Inc.
Essex Property Trust, Inc.
Wested
Epocrates, Inc.
Trinet Group, Inc.
Akqa, Inc.
Trianz, Inc.
Imperva, Inc.
Woodruff-Sawyer & Co.
Electric Power Research Institute, Inc.
Harris & Associates, Inc.
Infogain Corporation
Trinet Group, Inc.
Svb Financial Group
McGrath Rentcorp
Samsung Information Systems America, Inc.
Burr Pilger Mayer, Inc.
Epicor Software Corporation
Svb Financial Group
Lifelong Medical Care
Leisure Sports, Inc.
American Baptist Homes of The West
La Clinica De La Raza, Inc.
Taleo Corporation
Fair Isaac Corporation
American Baptist Homes of The West
The Marcus & Millichap Company
CBS Interactive Inc.
The Hospital Committee For The Livermore-Pleasanton Areas
Good Fellow Top Grade Construction, LLC
Responsys, Inc.
Arcsoft, Inc.
Blood Centers of The Pacific
Ehealthinsurance Services, Inc.
American Baptist Homes of The West
Anka Behavioral Health, Incorporated
Responsys, Inc.
Phoenix Technologies Ltd.
San Benito Health Care District
Saba Software, Inc.
Wilson Sonsini Goodrich & Rosati, Professional Corporation
Yodlee, Inc.
Cybernet Software Systems Inc
American Baptist Homes of The West
Mid-Peninsula Housing Management Corporation
Trinet Group, Inc.
Satellite Healthcare, Inc.
Embarcadero Technologies, Inc.
Shutterfly, Inc.
Taos Mountain, Inc.
Blood Centers of The Pacific
Exponent, Inc.
Hatch Mott Macdonald, LLC
Nagarro Inc.
El Camino Hospital
Epicor Software Corporation
Harris & Associates, Inc.
El Camino Hospital
American Baptist Homes of The West
Performant Financial Corporation
El Camino Hospital
Wested
Servicesource International, Inc.
Trinet Group, Inc.
Linkedin Corporation
Sybase, Inc.
Actuate Corporation
Linkedin Corporation
Iasco
Akqa, Inc.
Collateral Specialists Inc.
Ehealthinsurance Services, Inc.
Ehealthinsurance Services, Inc.
Sonicwall, Inc.
Business Wire, Inc.
Wested
Nexant, Inc.
Cisco Systems LLC
Paypal, Inc.
Trinet Group, Inc.
Visa International Service Association
Svb Financial Group
El Camino Hospital
Liveops, Inc.
Delta Dental of California
Epicor Software Corporation
Vm Services, Inc.
Epicor Software Corporation
Shorenstein Properties LLC
SRI International
Achievo Corporation
Trinet Group, Inc.
Risk Management Solutions, Inc.
Prologis, Inc.
Actuate Corporation
Bay Area Credit Service, LLC
Verisight, Inc.
Ehealthinsurance Services, Inc.
Trinet Group, Inc.
Provident Funding Associates, L.P.
Gordon & Rees LLP
Akqa, Inc.
Synarc Inc.
Wested
Wested
Wested
Proofpoint, Inc.
Freeborders, Inc.
Jive Software Inc.
California Physicians' Service
Farella Braun + Martel LLP
Ironplanet, Inc.
Satmetrix Systems, Inc.
Pier 39 Limited Partnership
Charles Schwab & Co., Inc
IAC Search & Media, Inc.
Svb Financial Group
Svb Financial Group
Kelleyamerit Fleet Services Inc.
Satellite Healthcare, Inc.
Visa International Service Association
SRI International
Essex Property Trust, Inc.
Risk Management Solutions, Inc.
El Camino Hospital
John Stewart Company
CBS Interactive Inc.
Netsuite, Inc.
California Physicians' Service
Enterprise Rent-A-Car Co of San Francisco, LLC
Coastal International, Inc.
Federal Reserve Bank of San Francisco
American Baptist Homes of The West
Anka Behavioral Health, Incorporated
N Model Inc
Exponent, Inc.
Farella Braun + Martel LLP
Trinet Group, Inc.
Genesys Telecommunications Laboratories, Inc.
Techlink Systems, Inc.
Trinet Group, Inc.
Telecare Corporation
Barracuda Networks, Inc
Burr Pilger Mayer, Inc.
Celera Corporation
Triage Consulting Group
Wested
Proofpoint, Inc.
Embarcadero Technologies, Inc.
American Baptist Homes of The West
Advent Software, Inc.
Satmetrix Systems, Inc.
CBS Interactive Inc.
Wageworks, Inc.
Trinet Group, Inc.
Trinet Group, Inc.
Federal Reserve Bank of San Francisco
Tibco Software Inc.
Abbott Vascular Inc.
Servicesource International, Inc.
Cloudera, Inc.
Fair Isaac Corporation
SRI International
Eah Inc.
Novogradac & Company LLP
Fireman's Fund Insurance Company
Shutterfly, Inc.
Shutterfly, Inc.
Fair Isaac Corporation
E2open, Inc.
Sybase, Inc.
E2open, Inc.
Itsi Gilbane Company
Epicor Software Corporation
Paypal, Inc.
Actuate Corporation
Linkedin Corporation
Actuate Corporation
Braden Partners, L.P., A California Limited Partnership
Visa International Service Association
CBS Interactive Inc.
Satellite Healthcare, Inc.
El Camino Hospital
Mellon Capital Management Corporation
It Convergence
Triage Consulting Group
Linkedin Corporation
Littler Mendelson, P.C.
Netsuite, Inc.
Armanino McKenna LLP
Visa International Service Association
Linkedin Corporation
Littler Mendelson, P.C.
Symphony Teleca Corporation
Symphony Teleca Corporation
Erm-West, Inc.
Good Technology, Inc.
Good Fellow Top Grade Construction, LLC
Good Fellow Top Grade Construction, LLC
Quicksort, Inc.
Quicksort, Inc.
T. Y. Lin International
Intelliswift Software, Inc.
Good Technology, Inc.
Trulia, Inc.
Cypress Security, LLC
Digital Realty Trust, Inc.
Cloudera, Inc.
Orrick, Herrington, Sutcliffe LLP
Akqa, Inc.
Rovi Corporation
Quinstreet, Inc.
Orrick, Herrington, Sutcliffe LLP
The Facebook Inc
Wildfire Interactive Inc.
Lucasfilm Ltd., Llc.
Guidewire Software, Inc.
Linkedin Corporation
First Republic Bank
Bkf Engineers
The PMI Group Inc
Astreya Partners, Inc.
Gracenote, Inc.
Techlink Systems, Inc.
Wested
Orrick, Herrington, Sutcliffe LLP
Vista Equity Partners LLC
Atr International, Inc.
Orrick, Herrington, Sutcliffe LLP
Orrick, Herrington, Sutcliffe LLP
Bre Properties, Inc.
Taos Mountain, Inc.
Sedgwick LLP
Exponent, Inc.
Linkedin Corporation
Structural Integrity Associates, Inc.
Protiviti Inc.
Liveops, Inc.
Epocrates, Inc.
Servicesource International, Inc.
Ehealthinsurance Services, Inc.
Risk Management Solutions, Inc.
Pdf Solutions, Inc.
Informatica Corporation
Linkedin Corporation
Actividentity, Inc.
Liveops, Inc.
Yodlee, Inc.
Extend Health, Inc.
Linkedin Corporation
Genomic Health, Inc.
Informatica Corporation
Tectura Corporation
Mid-Peninsula Housing Management Corporation
CBS Interactive Inc.
Informatica Corporation
Burr Pilger Mayer, Inc.
Symphony Teleca Corporation
Freeborders, Inc.
Splunk Inc.
Linkedin Corporation
Actuate Corporation
Celera Corporation
Actuate Corporation
Sybase, Inc.
Brown and Caldwell
Loopnet, Inc.
Serena Software, Inc.
Burr Pilger Mayer, Inc.
Netsuite, Inc.
Symphony Teleca Corporation
Golden Rain Foundation of Walnut Creek
Symphony Teleca Corporation
First Republic Bank
E2open, Inc.
Fair Isaac Corporation
CBS Interactive Inc.
Liveops, Inc.
Brown and Caldwell
Shutterfly, Inc.
American Baptist Homes of The West
Fair Isaac Corporation
Genomic Health, Inc.
The PMI Group Inc
Phoenix Technologies Ltd.
Workday, Inc.
Wageworks, Inc.
Informatica Corporation
Responsys, Inc.
Carmel Partners, Inc.
Fenwick & West LLP
Marklogic Corporation
Workday, Inc.
Telecare Corporation
Performant Financial Corporation
Techlink Systems, Inc.
Shutterfly, Inc.
The Facebook Inc
Satellite Healthcare, Inc.
Marketo, Inc.
E2open, Inc.
San Mateo Credit Union
Federal Reserve Bank of San Francisco
Norcal Mutual Insurance Co Inc
McGrath Rentcorp
Mid-Peninsula Housing Management Corporation
Samsung Information Systems America, Inc.
Actuate Corporation
Proofpoint, Inc.
Imperva, Inc.
Genesys Telecommunications Laboratories, Inc.
Extend Health, Inc.
Simco Electronics
Prologis, Inc.
Prologis, Inc.
Kelleyamerit Fleet Services Inc.
Embarcadero Technologies, Inc.
Marklogic Corporation
Marketwatch, Inc.
Ares Corporation
Leisure Sports, Inc.
Associated Third Party Administrators Inc
Hcl America Inc.
Guidewire Software, Inc.
Protiviti Inc.
Atr International, Inc.
Gracenote, Inc.
N Model Inc
Fireman's Fund Insurance Company
Loopnet, Inc.
Exponent, Inc.
Prologis, Inc.
Evans Analytical Group LLC
Prologis, Inc.
T. Y. Lin International
California Physicians' Service
Phoenix Technologies Ltd.
San Mateo Credit Union
Delta Dental of California

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Have you tried find and replace?
 
Upvote 0
You could use Find & Replace couldn't you?

Find what: L.L.C - Replace with ""

Repeat for the other offending strings.

Regards,
Howard
 
Upvote 0
We have to run about 30 find and replaces in a row to rid them all, is there a more efficient formula to handle this? Thanks for your input!
 
Upvote 0
Yeah, you might want to read the do's and do not's.

You could take the time and use the macro recorder and come up with a comprehensive list of items to replace with "nada".

I ran this on 17 columns over 1100 rows in each column and it took less than a second.

You might note... What:=", inc", I included the comma and the space in the find. The space and L.L.C in the others.
Once done, running on your huge data won't take very long at all, even with 30 different finds. Maybe a few seconds, I did not test that many.

Regards,
Howard

Code: 
Option Explicit
Sub Macro1()
' Macro1 Macro
    Cells.Replace What:=", inc", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=" L.L.C", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" LTD", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Formatting VBA - Why isn't it working on all of them?
Replies
5
Views
341
cjlittlet
C
T
  • Question
Formula to Group Companies under Categories
Replies
2
Views
513
alansidman
A
K
  • Solved
Out of my Element, looking for guidance
Replies
7
Views
453
Peter_SSs
Peter_SSs
T
  • Question
Need help with writting a macro
Replies
3
Views
881
JLGWhiz
JLGWhiz
S
  • Question
Extracting Data from another sheet.
Replies
0
Views
483
shahid5788
S

Forum statistics

Threads
1,214,376
Messages
6,119,172
Members
448,870
Latest member
max_pedreira

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