KTHCHARINSTANCE is just a LAMBDA-packaged version of the formula here: Finding the Nth Occurrence of a Character (Microsoft Excel)

This is a helper function for a more substantial LAMBDA I'd like to post, but I want to get used to how post creation editing works, so I'll do a couple small ones first.

Takes advantage of SUBSTITUTE's 4th parameter instance_num and a unique character CHAR(1) to find the position of instance_num in the string. The IFERROR wrap could probably be better, but was a quick stopgap for the logic of my later formula.

This is a helper function for a more substantial LAMBDA I'd like to post, but I want to get used to how post creation editing works, so I'll do a couple small ones first.

Takes advantage of SUBSTITUTE's 4th parameter instance_num and a unique character CHAR(1) to find the position of instance_num in the string. The IFERROR wrap could probably be better, but was a quick stopgap for the logic of my later formula.

Excel Formula:

```
=LAMBDA(
myStr,
myChar,
k,
IFERROR(
FIND(
CHAR(1),
SUBSTITUTE(myStr,myChar,CHAR(1),k)
),
LEN(myStr)+1
)
)
```

LAMBDA_UnpackLet.xlsx | ||||||
---|---|---|---|---|---|---|

A | B | C | D | |||

1 | myStr | myChar | k | KTHCHARINSTANCE | ||

2 | This is a test. | s | 1 | 4 | ||

3 | This is a test. | s | 2 | 7 | ||

4 | This is a test. | t | 1 | 11 | ||

5 | This is a test. | t | 3 | 16 | ||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

D2:D5 | D2 | =KTHCHARINSTANCE(A2,B2,C2) |

Last edited by a moderator:

Upvote
0