hi,
i have got a question...
how do you delete deplicate data in the same cell?

ie. In Cell A1

apple apple apple

i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
could i do that?
theres no semicolon, or any punctuation marks in between the
duplicates... just space. Can this be done?

same as if I have

apple apple banana apple

How can the 2nd apple, banana and 3rd apple be dedupe?

Thanks.
Elaine.

Elaine,
Do you want to just keep the first word in a cell or
to eliminate duplicates of the first word or
eliminate all duplicate words in the cell?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"elaine" <(E-Mail Removed)>
wrote in message
hi,
i have got a question...
how do you delete deplicate data in the same cell?
ie. In Cell A1
apple apple apple
i wanted to delete the 2nd and 3rd apple,
but keep the first apple, how could i do that?
theres no semicolon, or any punctuation marks in between the
duplicates... just space. Can this be done?

same as if I have
apple apple banana apple
How can the 2nd apple, banana and 3rd apple be dedupe?
Thanks.
Elaine.

Sub removeDupes()
Dim x As Integer
Dim t As String, m As String
Dim c As Range, r As Range
Dim l As New Collection
Dim v As Variant
Dim b As Boolean

Set r = Selection
For Each c In r
t = c.Value
Do While Not t = ""
x = InStr(1, t, " ") ' find the first " "
' if we're not at the end of the string then
' the m is the string before the next space
' otherwise it's the
If Not x = 0 Then
m = Mid(t, 1, x - 1)
Else
m = t
End If
If Not m = "" Then
b = False
For Each v In l
If m = v Then
b = True
End If
Next v
If Not b Then l.Add m
End If
t = Mid(t, Len(m) + 2)
Loop
c.Value = ""
For Each v In l
c.Value = c.Value & v & " "
l.Remove (1)
Next v
If Not c.Value = "" Then
c.Value = Left(c.Value, Len(c.Value) - 1)
End If
Next c

End Sub
elaine wrote:
> hi,
> i have got a question...
> how do you delete deplicate data in the same cell?
>
> ie. In Cell A1
>
> apple apple apple
>
> i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
> could i do that?
> theres no semicolon, or any punctuation marks in between the
> duplicates... just space. Can this be done?
>
> same as if I have
>
> apple apple banana apple
>
> How can the 2nd apple, banana and 3rd apple be dedupe?
>
> Thanks.
> Elaine.

' Removes duplicates within a cell from a range of
' selected cells
Sub removeDupes()
Dim x As Integer
Dim t As String, m As String
Dim c As Range, r As Range
Dim l As New Collection
Dim v As Variant
Dim b As Boolean

Set r = Selection
For Each c In r
t = c.Value
Do While Not t = ""
x = InStr(1, t, " ") ' find the first " "
' if we're not at the end of the string then
' the m is the string before the next space
' otherwise t the last word
If Not x = 0 Then
m = Mid(t, 1, x - 1)
Else
m = t
End If
If Not m = "" Then ' don't bother if m is empty
b = False
' looks to see if this word already exists
For Each v In l
If m = v Then
b = True
End If
Next v
' if it's not a duplicate then add it
If Not b Then
l.Add m
End If
End If
' remove the current word from the start of t
t = Mid(t, Len(m) + 2)
Loop
' put the non-dupe words back into the cell
c.Value = ""
For Each v In l
c.Value = c.Value & v & " "
l.Remove (1)
Next v
If Not c.Value = "" Then
c.Value = Left(c.Value, Len(c.Value) - 1)
End If
Next c

End Sub

elaine wrote:
> hi,
> i have got a question...
> how do you delete deplicate data in the same cell?
>
> ie. In Cell A1
>
> apple apple apple
>
> i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
> could i do that?
> theres no semicolon, or any punctuation marks in between the
> duplicates... just space. Can this be done?
>
> same as if I have
>
> apple apple banana apple
>
> How can the 2nd apple, banana and 3rd apple be dedupe?
>
> Thanks.
> Elaine.

thanks jason, it works perfectly!

Jason Lepack wrote:

> ' Removes duplicates within a cell from a range of
> ' selected cells
> Sub removeDupes()
> Dim x As Integer
> Dim t As String, m As String
> Dim c As Range, r As Range
> Dim l As New Collection
> Dim v As Variant
> Dim b As Boolean
>
> Set r = Selection
> For Each c In r
> t = c.Value
> Do While Not t = ""
> x = InStr(1, t, " ") ' find the first " "
> ' if we're not at the end of the string then
> ' the m is the string before the next space
> ' otherwise t the last word
> If Not x = 0 Then
> m = Mid(t, 1, x - 1)
> Else
> m = t
> End If
> If Not m = "" Then ' don't bother if m is empty
> b = False
> ' looks to see if this word already exists
> For Each v In l
> If m = v Then
> b = True
> End If
> Next v
> ' if it's not a duplicate then add it
> If Not b Then
> l.Add m
> End If
> End If
> ' remove the current word from the start of t
> t = Mid(t, Len(m) + 2)
> Loop
> ' put the non-dupe words back into the cell
> c.Value = ""
> For Each v In l
> c.Value = c.Value & v & " "
> l.Remove (1)
> Next v
> If Not c.Value = "" Then
> c.Value = Left(c.Value, Len(c.Value) - 1)
> End If
> Next c
>
> End Sub
>
> elaine wrote:
> > hi,
> > i have got a question...
> > how do you delete deplicate data in the same cell?
> >
> > ie. In Cell A1
> >
> > apple apple apple
> >
> > i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
> > could i do that?
> > theres no semicolon, or any punctuation marks in between the
> > duplicates... just space. Can this be done?
> >
> > same as if I have
> >
> > apple apple banana apple
> >
> > How can the 2nd apple, banana and 3rd apple be dedupe?
> >
> > Thanks.
> > Elaine.

hi john,
the funtion doesnt work if it is :

apple apple banana apple banana

in the cell...
it will just return "apple" instead of "apple banana"

Thanks for you help anyway!

elaine.

John wrote:
> You could use XL's functions. try
> Assuming Apple Apple Apple is in cell A1. In B1 type =left(A1,find(" ",a1))
>
> John
>
>
> "elaine" wrote:
>
> > hi,
> > i have got a question...
> > how do you delete deplicate data in the same cell?
> >
> > ie. In Cell A1
> >
> > apple apple apple
> >
> > i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
> > could i do that?
> > theres no semicolon, or any punctuation marks in between the
> > duplicates... just space. Can this be done?
> >
> > same as if I have
> >
> > apple apple banana apple
> >
> > How can the 2nd apple, banana and 3rd apple be dedupe?
> >
> > Thanks.
> > Elaine.
> >
> >

hi John Bundy...
Text to column will be too time consuming after spliting each word onto
a different column if there are like 100s words on each row and there
are 100s of rows...?

Will it be a mission to filter then??

Is there a way to filter the row btw??

elaine.
John Bundy wrote:
> My obvious answer is to tell you to just use text to columns, you can split
> the cell at each space, somehow i'm sure that is too easy. If that doesn't
> work you can build a quick array as long are no words with spaces.
> -John
>
> "elaine" wrote:
>
> > hi,
> > i have got a question...
> > how do you delete deplicate data in the same cell?
> >
> > ie. In Cell A1
> >
> > apple apple apple
> >
> > i wanted to delete the 2nd and 3rd apple, but keep the first apple, how
> > could i do that?
> > theres no semicolon, or any punctuation marks in between the
> > duplicates... just space. Can this be done?
> >
> > same as if I have
> >
> > apple apple banana apple
> >
> > How can the 2nd apple, banana and 3rd apple be dedupe?
> >
> > Thanks.
> > Elaine.
> >
> >