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 :)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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,494
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,320
Messages
5,571,531
Members
412,401
Latest member
allenayres83
Top