Remove duplicates in a single cell

**SonGoku** · 02-27-2013, 03:27 AM

I have about a 1000 names arranged in column A. My problem is however, each name appears twice, for example:

Cell 1 = Adam SparksAdam Sparks
Cell 2 = Ging FreecssGing Freecss
Cell 3 = Steve PetersonSteve Peterson
and so on

I want the name to appear just once. Please help.

**:) Sixthsense :)** · 02-27-2013, 03:31 AM

Using formula or VBA code?

**SonGoku** · 02-27-2013, 04:33 AM

Originally Posted by :) Sixthsense :)

Using formula or VBA code?

I'd prefer using a Macro, but basically anything that does the job will do.

To add to the complication, I tried working around this using the Text to Column feature but there are people with second names and third names as well. If I use a few filters I just might be able to do this.

For example:

Thomas PetersonThomas Peterson
Thomas Jeffery PetersonThomas Jeffery Peterson
Maureen Man Lai LeungMaureen Man Lai Leung
and so on.

Thanks

**Marcol** · 02-27-2013, 04:47 AM

Maybe this, then Copy, Paste Special > Values.

Formula:

=LEFT(A1,LEN(A1)/2)

Or to be safe, allow for trailing spaces

Formula:

=LEFT(TRIM(A1),LEN(TRIM(A1))/2)

**SonGoku** · 02-27-2013, 05:20 AM

Originally Posted by Marcol

Maybe this, then Copy, Paste Special > Values.

Formula:

=LEFT(A1,LEN(A1)/2)

Or to be safe, allow for trailing spaces

Formula:

=LEFT(TRIM(A1),LEN(TRIM(A1))/2)

Thanks Marcol! The first formula worked like a charm! Do you mind explaining it? I'm new to all of this but I'm really into programming and I need to learn how to think like that.

Thanks for the help, you're a life saver!

**Marcol** · 02-27-2013, 06:10 AM

Not a lot to explain, you know the string is twice as long as you need, so, starting on the left, you need to return half of the string, LEN(A1)/2.
You could equally use RIGHT(), to return the second half. In your case it is the same as the first half.

Formula:

=RIGHT(A1,LEN(A1)/2)

This isn't programming, it's native formula.

Excels' programming language is VBa. Code for this might look like this.
In a standard module.

Sub HalfString()
    Dim n As Long, LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For n = 1 To LastRow
        Cells(n, "A") = Left(Cells(n, "A"), Len(Cells(n, "A")) / 2)
    Next
    
End Sub