I have a spreadsheet with a column of names and email addresses. I have to
replace these with a last name and first initial that can be found on another
worksheet. The problem is that each cell contains more than one name and/or
email address, and they all have to stay that way. So, for instance, if I
have all of the following in cell A1:

John Smith, Jane Doe, F. Smith,

I want to get the following result also still in one cell:

Smith, J, Doe, J, Smith, F, Green, A

The other problem is the way that the data is set up on the other worksheet.
It is set up like this:

John Smith Smith, J
J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F
F. Smith
Jane Doe Doe, J
J. Doe

The match might not be found in the same column every time, so I dont think
a function like VLOOKUP would work. Im not sure that any of the regular
functions would work. I think it probably needs a macro.

Is what Im trying to do even possible?

Thanks in advance for any information.

This is just to clarify the data set up in my original post. For some
reason, it came through all jumbled up so that you can't tell what
information is in what column. Here is the data again without the email
addresses. I think that might be what threw it off. Hopefully this will
post correctly.

John Smith Smith, J J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F F. Smith
Jane Doe Doe, J J. Doe

If it doesn't, I guess I will just try to rephrase my question in the
following way: Can VLOOKUP, INDEX, or MATCH be used to look up or match
individual names when there is more than one name in a cell?

Thanks in advance for any information anyone can provide and sorry for the
confusing data.






"FJ" wrote:

I have a spreadsheet with a column of names and email addresses. I have to
replace these with a last name and first initial that can be found on another
worksheet. The problem is that each cell contains more than one name and/or
email address, and they all have to stay that way. So, for instance, if I
have all of the following in cell A1:

John Smith, Jane Doe, F. Smith,

I want to get the following result also still in one cell:

Smith, J, Doe, J, Smith, F, Green, A

The other problem is the way that the data is set up on the other worksheet.
It is set up like this:

John Smith Smith, J
J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F
F. Smith
Jane Doe Doe, J
J. Doe

The match might not be found in the same column every time, so I dont think
a function like VLOOKUP would work. Im not sure that any of the regular
functions would work. I think it probably needs a macro.

Is what Im trying to do even possible?

Thanks in advance for any information.

Try Vlookup with a wildcard:
=vlookup("*"&"aaa",
=vlookup("&"*"aaa",


Regards,
Ryan--
--
RyGuy


"FJ" wrote:

This is just to clarify the data set up in my original post. For some
reason, it came through all jumbled up so that you can't tell what
information is in what column. Here is the data again without the email
addresses. I think that might be what threw it off. Hopefully this will
post correctly.

John Smith Smith, J J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F F. Smith
Jane Doe Doe, J J. Doe

If it doesn't, I guess I will just try to rephrase my question in the
following way: Can VLOOKUP, INDEX, or MATCH be used to look up or match
individual names when there is more than one name in a cell?

Thanks in advance for any information anyone can provide and sorry for the
confusing data.






"FJ" wrote:

I have a spreadsheet with a column of names and email addresses. I have to
replace these with a last name and first initial that can be found on another
worksheet. The problem is that each cell contains more than one name and/or
email address, and they all have to stay that way. So, for instance, if I
have all of the following in cell A1:

John Smith, Jane Doe, F. Smith,

I want to get the following result also still in one cell:

Smith, J, Doe, J, Smith, F, Green, A

The other problem is the way that the data is set up on the other worksheet.
It is set up like this:

John Smith Smith, J
J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F
F. Smith
Jane Doe Doe, J
J. Doe

The match might not be found in the same column every time, so I dont think
a function like VLOOKUP would work. Im not sure that any of the regular
functions would work. I think it probably needs a macro.

Is what Im trying to do even possible?

Thanks in advance for any information.