KTHCHARINSTANCE

[tboulden]

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.

=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)

 

[smozgur]

I have two questions about the function.

1. Although it is not a big deal, perhaps NTHCHARINSTANCE could be used as the function name?
2. Shouldn't the function return NA() if an error such as char not found, invalid parameter, etc? I do understand this function will be used in another function, however, I believe it should be handled in the other function instead. The function should better provide a useful result including its error handling.

Also, the myChar argument is defined as the character to search for. However, the function also allows searching for a word in the string, which is great.

 

[tboulden]

All good points, I've re-worked the formula with these things in mind, and also to consolidate the AllCharPositions that utilized this LAMBDA. Please see below; I propose something like the following:

• STARTPOS(txt,str,n) =>
  • STARTPOS("This is This","is",1) = 2
  • STARTPOS("This is This","is",4) = #N/A
  • If we treat param n as optional: STARTPOS("This is This","is",) = array(3,6,11)
  • If we treat param n as required: STARTPOS("This is This","is","ALL") = array(3,6,11)

Optional n param version:

=LAMBDA(
    txt,
    str,
    n,
    LET(
        k,
        IF(
            ISBLANK(n),
            SEQUENCE(
                LEN(SUBSTITUTE(txt,str,CHAR(1)))
                -
                LEN(SUBSTITUTE(txt,str,""))
            ),
            n
        ),
        IFERROR(
            FIND(
                CHAR(1),
                SUBSTITUTE(txt,str,CHAR(1),k)
            ),
            NA()
        )
    )
)

Required n param version:

=LAMBDA(
    txt,
    str,
    n,
    LET(
        k,
        IF(
            n = "ALL",
            SEQUENCE(
                LEN(SUBSTITUTE(txt,str,CHAR(1)))
                -
                LEN(SUBSTITUTE(txt,str,""))
            ),
            n
        ),
        IFERROR(
            FIND(
                CHAR(1),
                SUBSTITUTE(txt,str,CHAR(1),k)
            ),
            NA()
        )
    )
)

 

[tboulden]

STARTPOS("This is This","is",1) = 2

Obviously, this should be 3, not 2, sorry.