Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If text found in Row, delete Column | Excel Programming | |||
Delete empty spaces at the end of text of a column? | Excel Programming | |||
How to delete row if column is blank or certain text | Excel Programming | |||
Delete all rows where Column A contains Text | Excel Worksheet Functions | |||
Delete rows with text and blanks in column A | Excel Programming |