# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
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,"|")
``````

``````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
``````
TabTextQ29055408.xlsm
0

Experts Exchange Solution brought to you by