Want to know the position of nth occurence of a number in a text or string

[anupamtiwari05]

Hi All,


I need your help to know the nth occurence of a number in a Text or String.


For example, I have written "anupam53" in cell A1 and now I want the position of first occurence of a number e.g. 5 here or may be 2nd occurence e.g. 3 within this text.


I have tried below array formula but its not working and I have also searched this site and not reached to the solution.


{=FIND({"0","1","2","3","4","5","6","7","8","9"},A1,1)}


when I pressed F9 in the formula, it shows ={#VALUE!,#VALUE!,#VALUE!,8,#VALUE!,7,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

it means its showing the position but don't know how to get this in result.


Please assist if it is possible and also let me know if I am not clear to explain you above.


Thanks & Regards,

Anupam Tiwari

 

[shrivallabha]

Try this array formula:

=SMALL(IF(ISNUMBER(--MID($A$2,ROW($A$1:INDEX(A:A,LEN($A$2))),1)),ROW($A$1:INDEX(A:A,LEN($A$2)))),1)


Here $A$2 holds anupam53

 

[Hui]

Not array entered

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9}))

 

[elhamdkh]

I dont know much excel,but i konw SEARCH formula returns the number of the character at which a specific character or text string is first found. You may find the second occurance by changing the start_num in the SEARCH formula( for example using another search formula and adding 1 to it to skip the 1st occurance)

I konw this is not a solution, but maybe it will help you get an idea

 

[Colin Legg]

For example, I have written "anupam53" in cell A1 and now I want the position of first occurence of a number e.g. 5 here or may be 2nd occurence e.g. 3 within this text."

It sounds like you want to return the digit itself rather than its position.


This will return the first digit as a string:

=MID(A1,SMALL(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),1),1)


This will return the second digit as string:

=MID(A1,SMALL(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),2),1)


If the digit occurence is out of bounds then the formula returns an empty string.

 

[anupamtiwari05]

Hi shrivallabha,


You are genius, the formula is working fine for me, giving result what i wanted.


Thanks a lot for your help.


Regards,

Anupam Tiwari

 

[anupamtiwari05]

@Hui,


Thanks a lot Hui for your help, as always you provide with very nice solutions.


your solution is working fine however I have just tweaked as below


{=SMALL(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9}),$B$1)} where $B$1 cell will have nth occurence number so its giving me any occurence of the number in the text.


@Colin Legg


Your formula has resolved my another query that I have missed here to ask to get the number itself as per the occurence in the text.


Thanks a lot for such a great help.


@elhamdkh


I have tried SEARCH however it was not working for me.

Thanks for your try.


Thanks to all for helping me with such nice solutions.


Regards,

Anupam Tiwari