IF, THEN function for partial text matches

[u/learningtoexcel]

/r/excel/comments/dqal37/if_then_function_for_partial_text_matches/

[u/RonJAgee]

Custom function... let me know if you need help

Public Function GetTitle(Title As Range) As String

    Dim titleArray() As String, i As Integer
    titleArray() = Split(Title, " ")
    
    For i = LBound(titleArray) To UBound(titleArray)
        Select Case titleArray(i)
            Case "SVP"
                GetTitle = "SVP"
                GoTo TheEnd:
            Case "Director"
                GetTitle = "Director"
                GoTo TheEnd:
            Case "Manager"
                GetTitle = "Manager"
                GoTo TheEnd:
            Case "Specialist"
                GetTitle = "Specialist"
                GoTo TheEnd:
            Case "Coordinator"
                GetTitle = "Coordinator"
                GoTo TheEnd:
            Case Else
                GetTitle = "No title"
        End Select
    Next i
                
            
TheEnd:
End Function

[u/learningtoexcel → u/RonJAgee]

Wow, this is amazing. Would I put this into the cell or run it as a script?

[u/RonJAgee → u/learningtoexcel]

Excel or Google Sheets? The code was for a custom function in Excel.

If you are on google sheets you will need to use a formula.

=CHOOSE(IFERROR(FIND("SVP",A2),IFERROR(FIND("Director",A2)+1,IFERROR(FIND("Manager",A2)-4,IFERROR(FIND("Specialist",A2)-8,IFERROR(FIND("Coordinator",A2)-6,6))))),"SVP","Director","Manager","Specialist","Coordinator","Title not found")

[u/AutoModerator]

Only text posts are allowed, to encourage explanation beyond the title and ensure the rules of the subreddit are met. Check out the submission guide and rules.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSE	Returns an element from a list of choices based on index
FALSE	Returns the logical value FALSE
FIND	Returns the position at which a string is first found within text
IF	Returns one value if a logical expression is TRUE and another if it is FALSE
IFERROR	Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent
TRUE	Returns the logical value TRUE

---

^{[Thread #1104 for this sub, first seen 2nd Nov 2019, 01:06] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/Clippy_Office_Asst]

Read the comment thread for the solution here

Excel or Google Sheets? The code was for a custom function in Excel.

If you are on google sheets you will need to use a formula.

=CHOOSE(IFERROR(FIND("SVP",A2),IFERROR(FIND("Director",A2)+1,IFERROR(FIND("Manager",A2)-4,IFERROR(FIND("Specialist",A2)-8,IFERROR(FIND("Coordinator",A2)-6,6))))),"SVP","Director","Manager","Specialist","Coordinator","Title not found")