Find nth position of a character in a string using formula

[asad]

Hello Guys,

I am trying to work out how to get nth position of a character in a string. For example in this

kjishdfiuayigdscka

if I use the formula

=FIND("i",I$1)

, it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?

Thanks

Asad

 

[NewOrderFac33]

(Bad brain day - apologies)

 

[NewOrderFac33]

Good morning, Rick,

With my labels in C and the search character in D, your hyperformula returns the first occurrence of the search character.
I'm obviously missing something here, but how do I amend it to refer to an Nth occurrence of the search character?

Regards

Pete

 

[Ingolf]

Hi, Pete

try this:
=IFERROR(FIND("^&",SUBSTITUTE($C1,$D1,"^&",COLUMNS($D:D))),"")

 

[István Hirsch]

I wanted to get the positions of three i's in the string. So the answer should be 3, 8, and 12.

Thanks for that. I will test it tomorrow and let you know.

Asad

Sorry I misunderstood your question.

If you need the positions of „i”s and the strings are in col A, leave col B empty and enter into C1:

=IFERROR(FIND("i",$A1,B1+1),"")

then copy down and right.

Excel Workbook
	A	B	C	D	E
1	abckdisfdisdfsdisff		6	10	16
2	kjishdfiuayigdscka		3	8	12
3	sfkli2334iweriwer		5	10	14
4	isfsflslsi		1	10
Sheet

 

[NewOrderFac33]

Ingolf - got it now - columns returns a number that takes the place of a fixed value for the instance you're looking for - although why anyone would want to do it like this, I don't really understand.

Have a good weekend

Pete

 

[asad]

Thanks a lot every one for your answers. It really was good to know that there were so many solutions and I could achieve my result using different options. Thanks to all of you.

PS: Pete, some one might use this to find nth instance of a particular character and then get the position and then use it to get the other part of the string as a final answer. I definitely am one of those .

Asad

 

[Rick Rothstein]

Sorry I misunderstood your question.

If you need the positions of „i”s and the strings are in col A, leave col B empty and enter into C1:

=IFERROR(FIND("i",$A1,B1+1),"")

then copy down and right.

	A	B	C	D	E
1	abckdisfdisdfsdisff		6	10	16
2	kjishdfiuayigdscka		3	8	12
3	sfkli2334iweriwer		5	10	14
4	isfsflslsi		1	10

<colgroup><col style="width: 30px;"><col style="width: 127px;"><col style="width: 29px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Excellent approach with that formula, but it does not do exactly what the OP asked for. The OP wanted to put the characters he was searching for placed in Column B next to the text in Column A (rather than fixing the search character inside the formula like you did). However, your formula can be modified to do this quite easily, just put the "to be searched for" character (the "i" in your formula) in cell B1 and then put this formula in cell C1 and copy it across for as many cells as might ever be needed (it outputs the empty string "" when it runs out of characters to be found), then copy all those cells down to the end of the data (not beyond the end of the data though)...

=IFERROR(FIND($B1,$A1,1+MAX($B:B)),"")

 

[István Hirsch]

Excellent approach with that formula, but it does not do exactly what the OP asked for. The OP wanted to put the characters he was searching for placed in Column B next to the text in Column A (rather than fixing the search character inside the formula like you did). However, your formula can be modified to do this quite easily, just put the "to be searched for" character (the "i" in your formula) in cell B1 and then put this formula in cell C1 and copy it across for as many cells as might ever be needed (it outputs the empty string "" when it runs out of characters to be found), then copy all those cells down to the end of the data (not beyond the end of the data though)...

=IFERROR(FIND($B1,$A1,1+MAX($B:B)),"")

Thanks Rick, good addition. I did overlook the fact that col B could not be empty since the OP used it. So, if the strings are in col A, the characters to search for in col B, then col C should be left empty and the formula put in D1 should look:

=IFERROR(FIND($B1,$A1,C1+1),"")

then copy it down (as long as there are data in col B!), and right.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	abciiikdisfdisdfsdisff	i		4	5	6	9	13	19
2	aajishdfiuayigadscka	a		1	2	11	15	20
3	sfkwli2334iweriwr	w		4	12	16
4	isfsflslsi	u
Sheet

I must do something wrong, but I can not produce the same result with the formula you proposed, the issue seems to come up when the character searched for occurs several times consecutively, for example „iiiasdfisd”.

 

[Momentman]

I must do something wrong, but I can not produce the same result with the formula you proposed, the issue seems to come up when the character searched for occurs several times consecutively, for example „iiiasdfisd”.

If you are referring to Rick's formula in POST #9, it does work, even for searching a character that occurs consecutively

Excel 2010
	A	B	C	D	E	F	G	H
1	abciiikdisfdisdfsdisff	i	4	5	6	9	13	19
2	aajishdfiuayigadscka	a	1	2	11	15	20
3	sfkwli2334iweriwr	w	4	12	16
4	jjjkrtyuopjkl	j	1	2	3	11
Sheet5

 

[István Hirsch]

If you are referring to Rick's formula in POST #9, it does work, even for searching a character that occurs consecutively

Excel 2010

	A	B	C	D	E	F	G	H
1	abciiikdisfdisdfsdisff	i	4	5	6	9	13	19
2	aajishdfiuayigadscka	a	1	2	11	15	20
3	sfkwli2334iweriwr	w	4	12	16
4	jjjkrtyuopjkl	j	1	2	3	11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Thanks Momentman. No, I am not referring to #9, as the quoting shows in #18, I am referring to the formula proposed by Rick in #17. I still want to know if I do something wrong or this formula needs some modifications to produce each location when the character searched for occurs several times consecutively in the string.