I have a column of data which is not consistent. Examples of the data in the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only the
name (first and last). I know I can convert my data from text to columns or
even write a macro to do this but I don't want anyone to have to remember to
do anything. I want the name to be returned once the data is pasted into a
template.

Thanks.

How complete are your examples; that is, when the name is not by itself, is
the additional text **always** in front of the name and is there **always**
a dash between them? Also, do I understand correctly that you want the
parsed name to replace the typed-in name in the same cell? Is yes to this
last question, you will need VB code to do this automatically... is a VB
code solution okay? If not, then you will need to put the parsed data in
another column using a formula (you cannot have a formula in a cell that a
user types in).

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
I have a column of data which is not consistent. Examples of the data in
the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only the
name (first and last). I know I can convert my data from text to columns
or
even write a macro to do this but I don't want anyone to have to remember
to
do anything. I want the name to be returned once the data is pasted into
a
template.

Thanks.

Assuming the name is after the ' - '...
=IF(ISERROR(FIND("-",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1))))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Nadine" wrote:

I have a column of data which is not consistent. Examples of the data in the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only the
name (first and last). I know I can convert my data from text to columns or
even write a macro to do this but I don't want anyone to have to remember to
do anything. I want the name to be returned once the data is pasted into a
template.

Thanks.

This doesn't work. The name is after the "- " if there is one. Thanks for
trying.

"Gary Brown" wrote:

Assuming the name is after the ' - '...
=IF(ISERROR(FIND("-",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1))))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Nadine" wrote:

I have a column of data which is not consistent. Examples of the data in the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only the
name (first and last). I know I can convert my data from text to columns or
even write a macro to do this but I don't want anyone to have to remember to
do anything. I want the name to be returned once the data is pasted into a
template.

Thanks.

Since it appears you are willing to parse the name out in another cell, try
this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
This doesn't work. The name is after the "- " if there is one. Thanks
for
trying.

"Gary Brown" wrote:

Assuming the name is after the ' - '...
=IF(ISERROR(FIND("-",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1))))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Nadine" wrote:

I have a column of data which is not consistent. Examples of the data
in the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only
the
name (first and last). I know I can convert my data from text to
columns or
even write a macro to do this but I don't want anyone to have to
remember to
do anything. I want the name to be returned once the data is pasted
into a
template.

Thanks.

Your and Gary's both worked. I must have missed something in Gary's but when
I tried it again, it worked. Thank you both!!!!!!!!!!!!!!!!

"Rick Rothstein" wrote:

Since it appears you are willing to parse the name out in another cell, try
this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

--
Rick (MVP - Excel)



"Nadine" wrote in message
...
This doesn't work. The name is after the "- " if there is one. Thanks
for
trying.

"Gary Brown" wrote:

Assuming the name is after the ' - '...
=IF(ISERROR(FIND("-",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1))))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Nadine" wrote:

I have a column of data which is not consistent. Examples of the data
in the
column: C- Name, CO- Name, CN- Name, Name, etc.

I want to write a formula/function that will automatically return only
the
name (first and last). I know I can convert my data from text to
columns or
even write a macro to do this but I don't want anyone to have to
remember to
do anything. I want the name to be returned once the data is pasted
into a
template.

Thanks.

.