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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Well, to separate them you can just use Text to Columns from the data menu. Just use the | symbol as the delimiter.
 

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7
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 :)
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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 :)
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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
 

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7
thanks to all for very swift replies - will give your suggestions a try-->

If it works will forever be in your debt!

Thanks :cool:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,142
Members
430,343
Latest member
Sailingexcel

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
Top