kind of rank

J

Jack Sons

Merry Christmas to all who read this.

Suppose in a cell I have the following text:

this is my text

In another cell I want a formula that that tells me, when I put the number
12 at a certain spot in that formula, that the 12th character in the text -
so in this case it is the character "t" (spaces etc. are also characters) -
is the 2nd occurrence in the text, so the output of the formula is 2.
If I put 1 into the formula (the first character) the result is 1 (first
occurrence of "t"); if I put in 15 the result will be 3; input 6 results in
2 (second "i"). And so on.
Prefrably with existing worksheet functions only (but I see no way to do it
with find, index, match, rank etc.). If that is impossible a UDF would also
be nice.

I hope I stated my problem clear enough.
Your help will be very much appreciated.

Again, have a nice X'mas (in Holland we have 2 Christmas days, december 25
and 26, like we have 2 Easter days (sunday and monday) and also 2
Pentacostal days - Whitsunday and Whitmonday - we once were a very devout
nation.

Jack Sons
The Netherlands
 
A

Aladin Akyurek

=LEN(LEFT(A1,B1))-LEN(SUBSTITUTE(LEFT(A1,B1),MID(LEFT(A1,B1),B1,1),""))

where A1 houses "this is my text" and B1 a position value like 12.
 
L

Leo Heuser

Hi Jack

Here's one way to do it. Text in A1 and
the number (12, 1, 15, 6 etc.) in F1:

=SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&F1)),1)=MID(A1,F1,1))+0)

And a merry Christmas to you :)
 
K

Kevin

Right off hand I don't see a way to do this without going
into VBA code. However, you should be able to do it in
VBA.

Kevin
 
J

Jack Sons

Kevin,

VBA, of course. But I really wanted to do it with worksheetfunctions. Aladin
en Leo in their posts showed me ways to do it. Nevertheless thank you for
your reaction to my question.

Jack.
 
Top