In the Automation Toolkit parameters and variables are standard cell values and formulas. Therefore, knowledge of text formulas can help to provide more flexibility to your recipes.

This article contains useful formulas for working with Automation Toolkit.

## Contents

- Get the workbook name 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 name containing the automation recipe

The formula below returns the name of the workbook containing the automation recipe.

`=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)`

This formula 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

The formula below returns the sheet name of the automation recipe.

`=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))`

This formula 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 the formula 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.xlsx`

To 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)`

**Included LAMBDA:**

The following LAMBDA function is already included in the Automation Toolkit from v2.0001. Compatible with LAMBDA-enabled version of Excel only.

`=fxGetFileName(FullFilePath)`

**FullFilePath:**The text string of a full file path

## 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))&"\"`

**Included LAMBDA:**

The following LAMBDA function is already included in the Automation Toolkit from v2.0001. Compatible with LAMBDA-enabled version of Excel only.

`=fxGetFolderPath(FullFilePath)`

**FullFilePath:**The text string of a full file path

## Count items in a delimited string

In recipes, we may want we loop through all items in a delimited list. Therefore, we need to know how many items exist in the list.

Let’s assume cell **B2** of the **My Lists** sheet contains the following text:

`North|South|East|West|Central`

The pipe symbol ( | ) is the delimiter.

The following formulas calculate the count of items. 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,"|",""))+1)*NOT(ISBLANK('My Lists'!B2))`

**Excel 365:**

`=COUNTA(TEXTSPLIT('My Lists'!B2,"|"))*NOT(ISBLANK('My Lists'!B2))`

**Included LAMBDA:**

The following LAMBDA function is already included in the Automation Toolkit from v2.0001. Compatible with LAMBDA-enabled version of Excel only.

`=fxCountDelimitedList(List,Separator)`

**List:**The text string of the delimited list**Separator:**The character used as the separator in the list

## 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|Central`

The pipe symbol (** |** ) is the delimiter.

Also, let’s assume cell **H11** contains the number of the item in the list 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)`

**Included LAMBDA:**

`=fxNthItemDelimitedList(List,Separator,NthItem)`

**List:**The text string of the delimited list**Separator:**The character used as the separator in the list**NthItem:**The number of the Nth item to extract from the list

## 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)`

An example using this formula can be found in the Getting Started guide.

### 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)`

## Check if a cell is blank

In the formula below we check if a cell is empty.

The following formula returns TRUE if cell **H11** 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))`

## Get file path from PowerPoint link path

When using the PowerPoint Links List Get action, PowerPoint links return the file path and range name in a single string. Often in our automations we only want to use the file path.

Let’s assume cell **H11** includes the following PowerPoint link path:

`C:\Examples\Automation Toolkit\Reporting Example.xlsx!SheetName!R9C3:R24C5`

To extract the file path of **C:\Examples\Automation Toolkit\Reporting Example.xlsx** we use the formula below. To use this formula in your scenario, replace **H11** with the relevant cell in your automation recipe.

**All versions of Excel:**

`=LEFT(H11,IFERROR(SEARCH(".xlsx",H11),0)+IFERROR(SEARCH(".xlsm",H11),0)+IFERROR(SEARCH(".xlsb",H11),0)+4)`

**Included LAMBDA:**

`=fxPPTLinkFilePath(PPTLinkPath)`

**PPTLinkPath:**The text string of the link generated by PowerPoint

**Discover other articles:**