Find position number of third space

[Steve Stad]

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.

 

[T. Valko]

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

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

 

[Ms-Exl-Learner]

For finding 1st position number of the space
=FIND(" ",A1)

For finding 2nd position number of the space
=FIND(" ",A1,FIND(" ",A1)+1)

For finding 3rd position number of the space
=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)

Remember to Click Yes, if this post helps!

 

[Mike H]

Hi,

Try this

=FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),3))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Steve Stad]

Thanks Mike,
I see Char(7) must be code for blank and by changing the last # I can find
any number of blank spaces, e.g., 4 for the 4th blank, 5 for the 5th blank
etc.
=FIND(CHAR(7),SUBSTITUTE($A27," ",CHAR(7),4))

Your disclaimer sounds like a version of Occams Razor principle. i.e., is
the principle that "entities must not be multiplied beyond necessity" and the
conclusion thereof, that the simplest explanation or strategy tends to be the
best one..
....When competing hypotheses are otherwise equal, adopt the hypothesis
that introduces the fewest assumptions while still sufficiently answering the
question.

 

[Steve Stad]

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]

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.