Delete defined text in a column
I got a column for some "company names" but I want to remove those "Co",
"company", "inc." etc, or "Atlanta", "L.A.", etc; I put those "to be removed" text in another column so that all text in this column will be removed from the company name column, eg. Newell co, L.A. should be read as Newell IBM Global Services Ltd, USA should be read as IBM Global Services Australia Home Depot Inc. should be read as Home Depot |
Delete defined text in a column
Sub removefromname() Const RemoveCol As String = "C" RemoveString = Array("company", "inc.", "etc", "Atlanta", "L.A.") Lastrow = Range(RemoveCol & Rows.Count).End(xlUp).Row For RowCount = 1 To Lastrow For Each MyStr In RemoveString ColData = Range(RemoveCol & RowCount) If InStr(ColData, MyStr) 0 Then ColData = Trim(Replace(ColData, MyStr, "")) Range(RemoveCol & RowCount) = ColData 'remove exit if you need to replace multiple items Exit For End If Next MyStr Next RowCount End Sub "Angus" wrote: I got a column for some "company names" but I want to remove those "Co", "company", "inc." etc, or "Atlanta", "L.A.", etc; I put those "to be removed" text in another column so that all text in this column will be removed from the company name column, eg. Newell co, L.A. should be read as Newell IBM Global Services Ltd, USA should be read as IBM Global Services Australia Home Depot Inc. should be read as Home Depot |
All times are GMT +1. The time now is 06:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com