CODEWITHSUNDEEP
vbaexceluser-defined-functionsudf
DukeSilver
DukeSilver

Reputation: 748

How to turn an Excel formula into a UDF?

I have a formula to swap last names with first names in cells where the format is "Smith, John".

=MID(A4&" "&A4,(FIND(" ",A4)+1),(LEN(A4)-1))

I created a function to utilize this functionality and it seemed to work at first. The function is:

Function SwapNames(text As String) As String SwapNames = Mid(text & " " & text, (Find(" ", text) - 1, (Len(text) - 1)) End Function

I converted my workbook to an Add-In filetype so I could use this globally and now it says the Find function is undefined. What am I doing wrong here?

Upvotes: 0

Views: 492

Answers (2)

ManishChristian
ManishChristian

Reputation: 3784

This is how you can use Split function and swap the name. 

Function SwapNames(text As String) As String
    SwapNames = Trim(Split(text, ",")(1)) & " " & Trim(Split(text, ",")(0))
End Function

So it will change Smith, John to John Smith and Smith, John J to John J Smith.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

As @Nathan_Sav said - use split, and perhaps an optional argument to identify the delimiter.

So =swapnames("Bartrup-Cook Darren") returns "Darren Bartrup-Cook" and =swapnames("Bartrup-Cook Darren","-") returns "Cook Darren Bartrup" a #REF! error is returned if the delimiter isn't present in the string.

Function SwapNames(text As String, Optional Delimiter As String = " ") As Variant

    Dim SplitAt As Long
    Dim NamePart As Variant

    SplitAt = InStr(text, Delimiter)

    If SplitAt = 0 Then
        SwapNames = CVErr(xlErrRef)
    Else
        NamePart = Split(text, Delimiter)

        SwapNames = NamePart(1) & " " & NamePart(0)
    End If

End Function

Upvotes: 2

Related Questions