Return Characters from Text Cell

[a2a]

Very frustrated with what I thought was a simple function, however cannot get this working and would really kindly appreciate any help here

I have a sheet of names in various forms that I am trying to reconcile, however some are in the form FIRST NAME SURNAME and some are FIRST NAME MIDDLE NAME SURNAME.

I'd like simply just to return the surname from that cell, and do this by finding the last occurence of a space " " in the cell and give me that value.

Initially I used: =MID(A1,FIND(" ",A1)+1,LEN(A1)-(FIND(" ",A1))), but this was only so good for the first occurence.

Thank you

 

[Aladin Akyurek]

Try also...

http://www.mrexcel.com/forum/showthread.php?t=195635

 

[a2a]

Wow - that algorithm function is truly awesom - great bit of expert coding there. Thanks for sharing that link

 

[al_b_cnu]

Hi,

Thanks for the compliments

If possible, I'd stick to the the formulae supplied , otherwise, maybe this bespoke UDF mightr do it:

Option Explicit

Function FindName(ByVal Name As Range, _
                  ByVal LookupTable As Range, _
                  ByVal IndexNum As Integer) As Variant
Dim bMatch As Boolean
Dim iPtrName As Integer, iPtrLookup As Integer
Dim iPtrNameUB As Integer, iPtrLookupUB As Integer
Dim rCur As Range, rLookup As Range
Dim sName As String, saName() As String
Dim sCurEntry As String, saCurEntry() As String

FindName = CVErr(xlErrNA)
sName = ""
For Each rCur In Name
    sName = sName & " " & LCase$(CStr(rCur.Value))
Next rCur

If Trim(sName)<> "" Then
    saName = Split(WorksheetFunction.Trim(sName), " ")
    iPtrNameUB = UBound(saName)
    Set rLookup = Intersect(Sheets(LookupTable.Parent.Name).UsedRange, LookupTable.Resize(, 1))
    If Not (rLookup Is Nothing) Then
        For Each rCur In rLookup
            sCurEntry = LCase$(WorksheetFunction.Trim(CStr(rCur.Value)))
            If sCurEntry<> "" Then
                saCurEntry = Split(WorksheetFunction.Trim(sCurEntry), " ")
                iPtrLookupUB = UBound(saCurEntry)
                If saName(iPtrNameUB) = saCurEntry(iPtrLookupUB) Then
                     If iPtrNameUB< iPtrLookupUB Then
                        bMatch = MatchingForenames(saName, saCurEntry)
                     Else
                        bMatch = MatchingForenames(saCurEntry, saName)
                     End If
                     If bMatch Then
                        If IndexNum = 0 Then
                            FindName = rCur.Row
                        Else
                            FindName = rCur.Offset(, IndexNum - 1).Value
                        End If
                        Exit Function
                     End If
                 End If
            End If
        Next rCur
    End If
End If

End Function
Private Function MatchingForenames(ByRef Forenames1() As String, _
                                    ByRef Forenames2() As String) As Boolean
'** NOTE: Assumes ForeNames1 has<= elements than ForeNames2 **
Dim bMatch As Boolean
Dim iPtr As Integer, iLen1 As Integer, iLen2 As Integer

bMatch = True
For iPtr = 0 To UBound(Forenames1) - 1
    iLen1 = Len(Forenames1(iPtr))
    iLen2 = Len(Forenames2(iPtr))
    If iLen1 > iLen2 Then
        bMatch = Left$(Forenames1(iPtr), iLen2) = Forenames2(iPtr)
    Else
        bMatch = Left$(Forenames2(iPtr), iLen1) = Forenames1(iPtr)
    End If
    If bMatch = False Then Exit For
Next iPtr
MatchingForenames = bMatch
End Function

For example:

Excel Workbook
	A	B	C	D	E	F
1	Our Data			Supplier Data
2	Jean Claude Van Damme			Forename(s)	Surname	Match Row
3	Bruce Ignateous Willis			Jean Claude	Damme	2
4	Another Hero			S X	Seagal	5
5	Steven Xavier Seagal			B	Willis	3
6	P A O'Toole			Peter	O'Toole	6
7	John Smith			Unknown	Warrior	#N/A
8				Ian	Smith	#N/A
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F3	=FindName(D3:E3,A:A,0)

 

[a2a]

Thank you again - this is great and also works a treat

Great work!