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)

 

[tboulden]

Hey @smozgur Please critique posting style before I get too out of line. Thanks!!

 

[tboulden]

@smozgur, also, should a subsequent LAMBDA that uses KthCharInstance be its own post? I'm thinking that I might just post it as a comment here? Don't want to start off on the wrong foot.

 

[smozgur]

Hey @smozgur Please critique posting style before I get too out of line. Thanks!!

Thanks, @tboulden. It looks perfect!
I only changed the title to be all uppercase, as an Excel function, and also reduced the indent in the Lambda function code.

@smozgur, also, should a subsequent LAMBDA that uses KthCharInstance be its own post? I'm thinking that I might just post it as a comment here? Don't want to start off on the wrong foot.

Yes. Please remember to refer to this thread in the new function post.
You can see how I did in my SLUGIFY.PLUS function:

SLUGIFY.PLUS

SLUGIFY.PLUS function is the advanced version of the SLUGIFY function I previously created. We often use the document title as the page name to create a web page, which is called "URL slug". The document title often has invalid characters that cannot be used in the URL. Therefore, we generate a...

www.mrexcel.com

 

[smozgur]

Yes. Please remember to refer to this thread in the new function post.

Let me clarify. This function does something on its own. So, it is the right way to keep this function alone.
If your new function is going to use this function, then you can refer to this function.

Personally, I would embed the logic that exists in this function into the new function, and keep the new function independent (where it is possible), because the Lambda function definition kind of promises for that. However, it is not something practical all the time for complex formulas, or it might be also a better idea to reuse the functions that could be used in many other new functions.
So, it actually depends on your approach.

 

[tboulden]

Let me clarify. This function does something on its own. So, it is the right way to keep this function alone.
If your new function is going to use this function, then you can refer to this function.

Personally, I would embed the logic that exists in this function into the new function, and keep the new function independent (where it is possible), because the Lambda function definition kind of promises for that. However, it is not something practical all the time for complex formulas, or it might be also a better idea to reuse the functions that could be used in many other new functions.
So, it actually depends on your approach.

Just as a quick example before I make a separate thread, my LAMBDA ALLCHARPOSITIONS just finds the total number of chars N in a string, then passes a SEQUENCE(N) to the k parameter of KTHCHARINSTANCE. So it doesn't do anything new; I think of it as a corollary to KTHCHARINSTANCE almost.

 

[tboulden]

just finds the total number of chars N in a string

This makes more sense as "finds the total number of myChar in myStr = N, then passes SEQUENCE(N) to the k parameter...

 

[smozgur]

Just as a quick example before I make a separate thread, my LAMBDA ALLCHARPOSITIONS just finds the total number of chars N in a string, then passes a SEQUENCE(N) to the k parameter of KTHCHARINSTANCE. So it doesn't do anything new; I think of it as a corollary to KTHCHARINSTANCE almost.

If you post here, then it will be a further discussion as an extension to KTHCHARINSTANCE function. If you post a new thread, then it will be a new function.
If you ask me, then I would say they are different functions.
Also, consider this, we have a voting system in the Lambda forum, and the posted functions can be voted by the other members. If you don't create a new thread then it won't be voted as a standalone function.

Please refer to my SLUGIFY and SLUGIFY.PLUS functions once again. They are doing the same thing, but I improved the original function in the PLUS version. To me, the PLUS version is a brand new version, like Office 2019 vs Office 365. In fact, in your sample above, they are completely different functions doing different things.

Finally, it really depends on how you'd like to proceed. There is no harm to post a new thread and it is my suggestion in this case.

 

[tboulden]

Thanks @smozgur, I'll post a different thread; I think I have a concise way of including the logic for KTHCHARINSTANCE and will also link back here.

 

[smozgur]

Thanks @smozgur, I'll post a different thread; I think I have a concise way of including the logic for KTHCHARINSTANCE and will also link back here.