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
