Find nth position of a character in a string using formula

A

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,434
Hello Guys,

I am trying to work out how to get nth position of a character in a string. For example in this
Code: 
kjishdfiuayigdscka
if I use the formula
Code: 
=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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
I know that I could use
Code: 
=FIND("i",I$1,FIND("i",I1,FIND("i",I1)+1)+1)
and that will give me the correct answer. But there must be a better way to achieve this without having to keep adding Find formula for every nth position.
 
Upvote 0

Excel 2010
AB
1kjishdfiuayigdscka12
Sheet4
Cell Formulas
RangeFormula
B1=FIND("^&",SUBSTITUTE(A1,"i","^&",3))
 
Upvote 0
Momentman said:
Excel 2010
A
B
1
kjishdfiuayigdscka
12

<TBODY>
</TBODY>
Sheet4​

Worksheet Formulas
Cell
Formula
B1
=FIND("^&",SUBSTITUTE(A1,"i","^&",3))

<TBODY>
</TBODY>


<TBODY>
</TBODY>
Click to expand...

Out of curiosity, is it possible to list all instances of occurrences of that character in the string?

Thanks
Asad
 
Upvote 0
István Hirsch said:
If you want to extract the 3 "i"s from the string:

=REPT("i",LEN(A1)-LEN(SUBSTITUTE(A1,"i","")))
Click to expand...
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
 
Upvote 0
You can drag the formula in C1 down and across, once it cant find a position of the character(i.e after the last "i"), it returns a blank

Excel 2010
ABCDE
1kjishdfiuayigdsckai3812
2jacobsangita27
Sheet4
Cell Formulas
RangeFormula
C1{=IFERROR(SMALL(IF(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)=$B1,ROW(INDIRECT("1:"&LEN($A1)))),COLUMN(A1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Here is another formula that you enter normally... put it in cell C1 and copy across for as many cells as you think you will ever need, then copy all of those down as far as you like (if you go past the position of any data, the formula outputs the empty string ("").

=IFERROR(FIND("^&",SUBSTITUTE($A1,$B1,"^&",COLUMNS($A:A))),"")

Note to Momentman: This is your original single-result formula slightly modified.
 
Upvote 0
Rick Rothstein said:
Here is another formula that you enter normally... put it in cell C1 and copy across for as many cells as you think you will ever need, then copy all of those down as far as you like (if you go past the position of any data, the formula outputs the empty string ("").

=IFERROR(FIND("^&",SUBSTITUTE($A1,$B1,"^&",COLUMNS($A:A))),"")

Note to Momentman: This is your original single-result formula slightly modified.
Click to expand...

Hahahhaha...That was a real overkill by my formula in post 8 :), You always make it look so easy

Your formula returns the position of the instances {1,2,3....}, wonder why it didn't just come to me that way :)

Welldone Sir.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Finding the Nth Smallest Position
2 3
Replies
21
Views
463
Fluff
Fluff
W
  • Solved
Extract the characters between 2 spaces in a string
Replies
5
Views
184
Fluff
Fluff
M
  • Question
find nth occurrence and return values
Replies
4
Views
409
kvsrinivasamurthy
K
B
  • Solved
Removing characters from the front of a text string
2
Replies
16
Views
443
awoohaw
awoohaw
N
  • Question
Return position character first appears in a text string from the right hand side
Replies
9
Views
404
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,782
Messages
6,121,532
Members
449,037
Latest member
tmmotairi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top