Delete duplicate data in a single cell

  • Thread starter Thread starter kacey28
  • Start date Start date
K

kacey28

I have a spreadsheet provided by an outsourced company that has duplicate
data within a single cell. For example, a cell with Name values will have
"Jerry White Jerry White" (twice), rather than once "Jerry White".

When duplicate values appear in the same cell, how do you manage to clean
these up without manually deleting data in each individual cell? (Note - I
have several columns that contain various duplicated data.)

Thanks in advance for any help!
 
I think this will work for you:
=LEFT(A1,FIND(" ",A1,1)-1)&RIGHT(A1,FIND(" ",A1,1)+2)


Regards,
Ryan---
 
Hi,
This is a bit long-winded, but it's all I could think of.
With the data to be examined in A2:

=IF(MOD(LEN(A2),2)=1,IF(LEFT(A2,(LEN(A2)-1)/2)=RIGHT(A2,(LEN(A2)-1)/2),LEFT(A2,(LEN(A2)-1)/2),A2),A2)

Copy down as required.
Regards - Dave.
 
Thank you, ryguy7272.

I tried the formula supplied and here's the result:

Field A1 data: CRYSTAL BERNIER CRYSTAL BERNIER

Field A1 data (after formula): CRYSTALAL BERNIER

As you can see, the formula sucessfully removed the duplicate first/last
name, however, it added additional letters to the first name. This occured
in the subsequent list fields as well. Any way to get it to display just the
unique name value without the additional letters?
 
Thank you, Dave. Your formula returned the same value as in the original
cell (i.e. duplicate data).
 
Ryan - additional information. I finally disected your formula to understand
you are giving the find function a starting position within the text of the
field.

Would this formula work when there are variying duplicate names, or other
values, that each have seperate starting positions withing the text
throughout a column?
 
Hi,
My formula works ok for me. Try typing Jerry White Jerry White into A2 and
apply the formula.
Does your data have leading or trailing spaces that you're not telling us
about?
Regards - Dave.
 
Have you tried 'Text to Column"? If you go to Data and select 'Text to
Columns' it will sort each first and last name into separate columns and you
can delete the unnecessary columns. This is just a quick fix, but it works.
 
Thank you, Nita.

Nita Carmicle said:
Have you tried 'Text to Column"? If you go to Data and select 'Text to
Columns' it will sort each first and last name into separate columns and you
can delete the unnecessary columns. This is just a quick fix, but it works.
Click to expand...
 
Dave, I wasn't aware spaces could cause a problem. Yes, the format that the
outsourced company uses in thier spreadsheet does include unnecessary leading
and trailing spaces.

Would it work best if I run the Trim function and then run your formula from
the clean data?
 
Hi KC,
The formula I suggested counts the characters, then compares the first half
to the second half. If there are extra spaces, they mess up the count.

You can clean up your data with =TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Put this in an adjacent cell, then copy down. It will remove leading and
trailing spaces, and the nasty non-breaking space character which is
sometimes present.
Then apply my other formula to cleaned-up data.
If it gives you what you want, copy the good data and Paste Special > Values
over the original data, and delete the columns you used in the process.
Regards - Dave.
 
Back
Top