Position for the 3rd and 4th appearance of a character in a text string

[Sven]

Hi,
I’m looking for an Excel function (not a VBA) which returns the position of the 3rd and 4th time a specific character appears in a text string – for example @

Example text string in cell A1:
what@time@is@it@right@now

In the text string above – the position of the 3rd and 4th time the @ is displayed is position 13 and position 16

I have tried to use the =FIND() function – but the problem is that I then have to refer back to the 'previous' cell to find the ‘start_num’ and I would like to have the columns independent of each other.

Thanks,
Sven

[martindwilson]

=FIND("^",SUBSTITUTE(A1,"@","^",3)) and =FIND("^",SUBSTITUTE(A1,"@","^",4))
if you use ^ somewhere pick a character you aren't likely to have
say
=FIND("~",SUBSTITUTE(A1,"@","~",3))

[Sven]

Hi martindwilson,
I tried to have cell
A1 => what@time@is@it@right@now
B1 => FIND("^",SUBSTITUTE(A1,"@","^",3))

Sorry if I ask a stupid question - but I don't understand what to put in instead of "^" in your solution in cell B1? I would like to display 13 in cell B1 because that is the position when the character @ is displayed the 3rd time in the text string - and i don't understand how to achieve that?

Thanks for clarifying,
Sven

[daddylonglegs]

Just use that formula exactly......

=FIND("^",SUBSTITUTE(A1,"@","^",3))

[Sven]

Great! – it now works.

The problem was that in my regional setting I use ; instead of ,

=FIND("^",SUBSTITUTE(A1,"@","^",3))
=FIND("^";SUBSTITUTE(A1;"@";"^";3)) (this works for me)

Last question about the character ^ you have in your example:
Question: Is it true that you need to set something that you know for sure you will never have in the text string? (for example ^ )

Thanks,
Sven

[zbor]

true. You can use some CHAR(155) or something less likely

[Sven]

OK - thanks ALL.
Case SOLVED :-)