If I follow the logic... it is replacing the blank with
^^ and it finds the 3rd instance of ^^. -- correct?

That's very close!

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

Let's assume the cell entry is:

asd abc 123 jkl

Formulas evaluate the inner-most functions first then work backwards to the
left. So, the first thing that happens with this formula is:

SUBSTITUTE(A1," ","^^",3)

This is replacing the 3rd space in the string with ^^. So the string looks
like this:

asd abc 123^^jkl

This string is then passed to the FIND function:

FIND("^^","asd abc 123^^jkl")

FIND "finds" the substing ^^ starting at character position 12.

So:

A1 = asd abc 123 jkl

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

=12

The ^^ is just an arbitrary character (or string of characters) that is very
unlikely to already appear in string that you want to evaluate. This ensures
that we get the correct result.

--
Biff
Microsoft Excel MVP


"Steve Stad" wrote in message
...
Biff,

Thanks for reply. If I follow the logic... it is replacing the blank with
^^ and it finds the 3rd instance of ^^. -- correct? If so good solution
and
maybe less typing than these...
=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)
=FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),3))

"T. Valko" wrote:

Assuming there will *always* be at least 3 spaces:

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

--
Biff
Microsoft Excel MVP


"Steve Stad" wrote in message
...
Can you tell me how to find the position number of the third space in a
string of varying length and text.
e.g.,
123 456 789 111 222
need to find position number of space between 789 and 111
or in:
abcd efghi jklmnopqr stuvxy
need position number of space between "r" and 's'.

I am familiar with =find(" ",a1) to find a space but need help with
logic
for third or second space.


.