How to identify which level the word Tab is in each cell in Excel 2016?
In the attached file, I would like a formula to identify what level the word Tab appears.
Examples:
Examples:
Home Tab | Styles | Format as Table 1
PivotTable Tools | Analyze Tab | Tools | Recommended PivotTables 2
Developer Tab | Controls | Properties 1
Query Tools | Query Tab | Edit | Edit Query 2
Developer Tab | XML | XML Expansion Packs 1
Data Tab | Data Tools | Consolidate Data 1
Home Tab | Font | Draw Border 1
SmartArt Tools | Design Tab | Create Graphic | Layout 2
Equation Tools | Design Tab | Structures | Bracket 2ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the solution and the link!
Be aware, accepted solution will fail for a case like below, the 'other' solution handles it properly.
Format as Table | Home Tab | Styles
»bp
Format as Table | Home Tab | Styles
»bp
Be aware, accepted solution will fail for a case like below, the 'other' solution handles it properly.
so we can easily modify it as
FIND(" Tab ") instead of FIND("Tab")
if we have such cases...
@Alex
What would be your expected output in case of following string?
"Home syle | Styles | Format as Table|Home Tab"
3 or 4?
Remember the accepted solution doesn't differentiate between Tab and Table.
The formula what Bill has proposed would work with a slight modification.
What would be your expected output in case of following string?
"Home syle | Styles | Format as Table|Home Tab"
3 or 4?
Remember the accepted solution doesn't differentiate between Tab and Table.
The formula what Bill has proposed would work with a slight modification.
=LEN(LEFT(A1,FIND(" Tab ",A1&" ")))-LEN(SUBSTITUTE(LEFT(A1,FIND(" Tab ",A1&" ")),"|",""))+1
with all issues talked here, the fixed version of accepted solution is
+ no "Tab" in value FIX (0 instead of #VALUE error)
=SUMPRODUCT(LEN(LEFT(A1,FIND(" Tab ",A1&" ")))-LEN(SUBSTITUTE(LEFT(A1&" ",FIND(" Tab ",A1&" ")),"|","")))+1+ no "Tab" in value FIX (0 instead of #VALUE error)
=IF(ISERROR(FIND(" Tab ",A1&" ")),0,SUMPRODUCT(LEN(LEFT(A1,FIND(" Tab ",A1&" ")))-LEN(SUBSTITUTE(LEFT(A1&" ",FIND(" Tab ",A1&" ")),"|","")))+1)ASKER
Thanks for all the comments and corrections. I did not notice the difference between the formulas.
I just fixed the formula when I copied it into my spreadsheet.
I just fixed the formula when I copied it into my spreadsheet.
Open in new window
»bp