Seperating and comparing parts of a string

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7
Hello out there....

Am in need of code to find names in a string seperated by "|" and then to compare these different parts and delete any duplicates - the string looks something like:

Joe Bloggs|James Brooks|Joe Bloggs|John smith|John Smith

Somehow need to seperate all these names and compare them with each other....have recently finished a big project and my head is thumping too much from overthinking to get round this one so would very much appreciate any help.

Thanks :)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Well, to separate them you can just use Text to Columns from the data menu. Just use the | symbol as the delimiter.
 
Upvote 0
sorry wasn't very clear on this:
Im not just searching on one string i have thousands to get through and i've been asked to include this peice of code at the end of the HUGE macro i've just finished. They want all this done in one push of a button --> i guess some people are just too lazy :biggrin: .

Thanx :)
 
Upvote 0
Well, the macro recorder can give you the code for Text To columns. :)

The searching would be a bit more complex (at least for me). Someone else may get to this before I do, but I'll give it a try :)
 
Upvote 0
Hi, Welcome to the board!

Try this UDF which can either be called directly from a cell, or from within a macro:
Code:
Function DeleteDups(ByVal iString As Variant, _
                    Optional Delimiter As String = "|") As String
Dim bIgnore As Boolean
Dim vTemp() As Variant, sCur As String, iPtr As Integer, iPtr1 As Integer, iOut As Integer

iString = Split(iString, Delimiter)
iOut = -1
For iPtr = 0 To UBound(iString)
    sCur = iString(iPtr)
    iString(iPtr) = LCase$(WorksheetFunction.Trim(sCur))
    bIgnore = False
    For iPtr1 = 0 To iPtr - 1
        If iString(iPtr1) = iString(iPtr) Then
            bIgnore = True
            Exit For
        End If
    Next iPtr1
    If bIgnore = False Then
        iOut = iOut + 1
        ReDim Preserve vTemp(0 To iOut)
        vTemp(iOut) = sCur
    End If
Next iPtr
DeleteDups = Join(vTemp, Delimiter)
End Function
 
Upvote 0
thanks to all for very swift replies - will give your suggestions a try-->

If it works will forever be in your debt!

Thanks :cool:
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top