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

=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.

"Jack Sons" wrote in message
...
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

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 :-)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jack Sons" skrev i en meddelelse
...
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

Aladin and Leo,

Thank you both.

Jack.

"Jack Sons" schreef in bericht
...
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

You're welcome, Jack.

LeoH

"Jack Sons" skrev i en meddelelse
...
Aladin and Leo,

Thank you both.

Jack.

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
-----Original Message-----
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


.

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.

"Kevin" schreef in bericht
...
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
-----Original Message-----
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


.