Create a macro to eliminate certain words from several columns

[Jorge]

Hello, me again, another question to elaborate further on my last
post/question. Is it possible to creat a program/macro that would eliminate
certain words from certain columns and leave the rest of the information
intact. For example:

A1 B1 C1
State: Florida Phone: 321-555-1234 Company: ABC Store
State: New Mexico Phone: 214-555-9876 Company: XYZ, LLC.
State: Georgia Phone: 305-555-4321 Company: Stuff, Inc.

I would like to get rid off the words "State", "Phone", and "Company" so my
columns would look like this:

A1 B1 C1
Florida 321-555-1234 ABC Store
New Mexico 214-555-9876 XYZ, LLC.
Georgia 305-555-4321 Stuff, Inc.

A macro would make things so much faster for me. Thanks again!!!

Jorge

 

[Gary''s Student]

Try this:

Sub cleanout()
s = Array("State: ", "Phone: ", "Company: ")
For I = 0 To 2
n = Cells(Rows.Count, I + 1).End(xlUp).Row
v = s(I)
For j = 1 To n
Set r = Cells(j, I + 1)
r.Value = Replace(r.Value, v, "", 1)
Next
Next
End Sub

 

[Dave Peterson]

Record a macro when you do a bunch of edit|replaces.

 

[reitanospa1]

If you're new to macros, you could try to use the recorder (Tools/
Macro/Record New Macro), use find and replace, and stop the recorder.

This would create a macro that you could run again in the future. It
also is a great way to learn how macros work because if you edit the
macro (Tools/Macro/Macros/Edit) you can see what the recorder has
collected. Note that recorded macros contain every option, even those
you did not select, so they get a little wordy sometimes.

 

[Jorge]

Hello Gary,

Thank you for all your help. I got it to work for the "State" word but not
for the "Company" or "Phone". Any ideas? I am doing the same example with the
three columns A, B, and C with the words State only to be found in column A,
Phone in B and Company in C. Once I get this to work, I'll twick it so I can
include other words like "Email", "Agent", "City", etc...any advice for the
twicking portion of my problem is welcome since although I can more or less
follow the code (I took C++ like a zillion years ago and understand the
basics of coding) I am not certain on who it works.

Thanks, everyone in this forum has helped me tremedously!!!

Jorge