How to turn this calculation into a function?

I had this question after viewing How to identify which level the word Tab is in each cell in Excel 2016?.

How could this be made as a function? Instead of just the word "Tab", a word would be submitted to the function.
LVL 1
Alex CampbellAsked:
Who is Participating?
 
byundtCommented:
Here is a user defined function that will identify the level a Word is at, given some text to search, text to find and a divider string. There is an optional input for whether the search should be case sensitive or not; default is not case sensitive.

Install the code in a regular module sheet, then use it with a worksheet formula like:
=WordLevel("tab ",$A2,"|")

Open in new window


Function WordLevel(sFind As String, sText As String, sDivider As String, Optional bCaseSensitive As Boolean = False) As Long
'Splits sText into phrases at each sDivider substring, then searches for sFind. Returns index number of first phrase
Dim phrases As Variant
Dim i As Long, n As Long
Dim Compare As Integer
If bCaseSensitive = False Then
    sFind = LCase(sFind)
    sText = LCase(sText)
End If
phrases = Split(sText, sDivider)
n = UBound(phrases)
For i = n To 0 Step -1
    If InStr(1, phrases(i), sFind) > 0 Then Exit For
Next
WordLevel = i + 1
End Function

Open in new window

TabTextQ29055408.xlsm
0
 
Alex CampbellAuthor Commented:
Great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.