I have several spreadsheet with a column for e-mail address. Some cells have
two e-mail addresses. I need to separate the addresses so that there is only
one address per cell, then delete the second address. This is easy to do
using text-to-columns and I recorded a macro to do just that.

The problem is that the column where the addresses are located can change
between files. In one file it's column A. In another file it's column D.
When I record a macro, it's the same column every time (always column A), so
the macro doesn't work for every file.

Can someone give me code that will find the address, then separate them,
then delete the extra?

Thanks!

Record the macro with relative reference and put your cursor on the column
where the email address are and run it.

"User" wrote:

I have several spreadsheet with a column for e-mail address. Some cells have
two e-mail addresses. I need to separate the addresses so that there is only
one address per cell, then delete the second address. This is easy to do
using text-to-columns and I recorded a macro to do just that.

The problem is that the column where the addresses are located can change
between files. In one file it's column A. In another file it's column D.
When I record a macro, it's the same column every time (always column A), so
the macro doesn't work for every file.

Can someone give me code that will find the address, then separate them,
then delete the extra?

Thanks!

You may want to have the user select the column and have your macro work with
that (or use the activecell's column).

Or you could find (like Edit|Find) a keyword in the header row (if there is a
header row and the keyword doesn't change (and is unique, too!)).



User wrote:

I have several spreadsheet with a column for e-mail address. Some cells have
two e-mail addresses. I need to separate the addresses so that there is only
one address per cell, then delete the second address. This is easy to do
using text-to-columns and I recorded a macro to do just that.

The problem is that the column where the addresses are located can change
between files. In one file it's column A. In another file it's column D.
When I record a macro, it's the same column every time (always column A), so
the macro doesn't work for every file.

Can someone give me code that will find the address, then separate them,
then delete the extra?

Thanks!

--

Dave Peterson