Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
I think this will work for you:
=LEFT(A1,FIND(" ",A1,1)-1)&RIGHT(A1,FIND(" ",A1,1)+2) Regards, Ryan--- -- RyGuy "kacey28" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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? "ryguy7272" wrote: I think this will work for you: =LEFT(A1,FIND(" ",A1,1)-1)&RIGHT(A1,FIND(" ",A1,1)+2) Regards, Ryan--- -- RyGuy "kacey28" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
Thank you, Dave. Your formula returned the same value as in the original
cell (i.e. duplicate data). "Dave" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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? "ryguy7272" wrote: I think this will work for you: =LEFT(A1,FIND(" ",A1,1)-1)&RIGHT(A1,FIND(" ",A1,1)+2) Regards, Ryan--- -- RyGuy "kacey28" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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. -- Nita Carmicle Cleveland, OH "kacey28" wrote: 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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
Thank you, Nita.
"Nita Carmicle" wrote: 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. -- Nita Carmicle Cleveland, OH "kacey28" wrote: 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! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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? "Dave" wrote: 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete duplicate data in a single cell
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. "kacey28" wrote: 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? "Dave" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete duplicate data | New Users to Excel | |||
Duplicate Text within single cell. How to identify & split | Excel Worksheet Functions | |||
delete duplicate data | New Users to Excel | |||
Delete row where there is duplicate data in Column E | New Users to Excel | |||
How do I delete both sets of duplicate data? | Excel Discussion (Misc queries) |