The Automation Toolkit parameters and variables are based on text. Therefore, knowledge of formulas can help to provide more functionality and flexibility to your recipes.
This article contains useful formulas for working with text inside the Automation Toolkit.
Contents
- Get the workbook containing the automation recipe
- Get the sheet name of the automation recipe
- Get the file name from a full file path
- Get the folder from a full file path
- Count items in a delimited string
- Get Nth item from a delimited string
- Get Nth item from a range list
- Check if a cell is blank
Get the workbook containing the automation recipe
In this section, we look at the formula to return the name of the workbook containing the automation recipe.
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))The formula above is pre-populated in the This Workbook variable (cell H9) of an automation recipe template.
Note: The formula may calculate as #VALUE! if the workbook has not been saved.
Get the sheet name of the automation recipe
In this section, we look at the formula to return the sheet name of the automation recipe.
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)The formula above is pre-populated in the This Worksheet variable (cell H10) of an automation recipe template.
Note: The formula may calculate as #VALUE! if the workbook has not been saved.
Get the file name from a full file path
In this section, we look at how to calculate the file name from the full file path.
Let’s assume cell H11 contains the following value:
C:\Examples\Automation Toolkit\Reporting Example.xlsxTo extract the file name of Reporting Example.xlsx we use the formulas below. To use these formulas in your scenario, replace H11 with the relevant cell in your automation recipe.
All versions of Excel:
=MID(H11,FIND("*",SUBSTITUTE(H11,"\","*",LEN(H11)-LEN(SUBSTITUTE(H11,"\",""))))+1,LEN(H11))An example using this formula can be found in the Getting Started guide.
Excel 365:
=TAKE(TEXTSPLIT(H11,"\"),,-1)Get the folder from a full file path
In this section, we look at how to calculate the folder path from the full file path.
As in the previous section, let’s assume cell H11 contains the following value:
C:\Examples\Automation Toolkit\Reporting Example.xlsx
To extract the file path of C:\Examples\Automation Toolkit\ we use the formulas below. To use these formulas in your scenario, replace H11 with the relevant cell in your automation recipe.
All versions of Excel:
=LEFT(H11,FIND("?",SUBSTITUTE(H11,"\","?",LEN(H11)-LEN(SUBSTITUTE(H11,"\","")))))Excel 365:
=TEXTJOIN("\",TRUE,DROP(TEXTSPLIT(H11,"\"),,-1))&"\"Count items in a delimited string
In this section, we look at how to count the number of items in a delimited list.
Let’s assume cell B2 of the My Lists sheet contains the following text:
North|South|East|West|CentralThe pipe symbol ( | ) is the delimiter.
The following formulas calculate the count of items in the list. To use these formulas in your scenario:
- Replace ‘My Lists’!B2 with your relevant cell reference
- Replace “|” with the delimiter character in your list
All versions of Excel:
=LEN('My Lists'!B2)-LEN(SUBSTITUTE('My Lists'!B2,"|",""))+1Excel 365:
=COUNTA(TEXTSPLIT('My Lists'!B2,"|"))Get Nth item from a delimited string
In the previous section, we saw how to count the number of items in a delimited list. In this section, we look at how to extract nth item from that list.
Let’s assume cell B2 of the My Lists sheet contains the following text:
North|South|East|West|CentralThe pipe symbol ( | ) is the delimiter.
Also, let’s assume cell H11 contains the number of the item to return.
The following formulas extract the Nth item from the list. To use these formulas in your scenario:
- Replace ‘My Lists’!B2 with your relevant cell reference
- Replace “|” with the delimiter character in your list
- Replace H11 with the cell reference of the number to return (e.g. 2 returns the 2nd item)
All versions of Excel:
=TRIM(MID(SUBSTITUTE('My Lists'!B2,"|",REPT(" ",LEN('My Lists'!B2))),(H11-1)*LEN('My Lists'!B2)+1,LEN('My Lists'!B2)))Excel 365:
=INDEX(TEXTSPLIT('My Lists'!B2,"|"),H11)Get Nth item from a range list
In this section, we look at how to return the nth item from a range list.
Let’s assume the My Lists sheet contains the following values:
- B2:North
- B3:South
- B4:East
- B5:West
- B6:Central
Also, let’s assume cell H11 contains the number of the item to return from the list.
Method #1: INDEX
The following formula extracts the Nth item from the list. To use these formulas in your scenario:
- Replace ‘My Lists’!B2:B6 with your relevant cell reference
- Replace H11 with the cell reference of the number to return (e.g. 2 returns the 2nd item)
All versions of Excel:
=INDEX('My Lists'!B2:B6,H11)Method #2: OFFSET
OFFSET is an alternative to the INDEX function.
The following formula extracts the item from the list. To use these formulas in your scenario:
- Replace ‘My Lists’!B2 with the reference to the first cell in the list
- Replace H11 with the cell reference of the number to return (e.g. 2 returns the 2nd item)
All versions of Excel:
=OFFSET('My Lists'!B2,H11-1,0)An example using this formula can be found in the Getting Started guide.
Check if a cell is blank
In this section, we look at how to know if a cell is empty.
The following formula returns TRUE if cell H11 cell is blank or FALSE if it contains a value.
=ISBLANK(H11)This formula works well with formulas that return ranges, such as the INDEX and OFFSET.
Using the OFFSET example above, if the number in cell H11 were 6, OFFSET would return a blank cell as there is not a 6th value in the list. Therefore the result of the formula below would be TRUE.
=ISBLANK(OFFSET(‘My Lists’!B2,H11-1,0))
Discover other articles:
