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

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