Find position number of third space
 

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.

Answer: Find position number of third space
 

Here's how to find the position number of the third space in a string using Excel:

1. Use the
   Code:

   ---

   FIND

   ---

   function to find the position of the first space in the string:
   
   Code:

   ---

   =FIND(" ",A1)

   ---

2. Use the
   Code:

   ---

   FIND

   ---

   function again to find the position of the second space in the string, starting the search from the position of the first space:
   
   Code:

   ---

   =FIND(" ",A1,FIND(" ",A1)+1)

   ---

3. Use the
   Code:

   ---

   FIND

   ---

   function once more to find the position of the third space in the string, starting the search from the position of the second space:
   
   Code:

   ---

   =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)

   ---

For example, if you have the string "123 456 789 111 222", and you want to find the position number of the space between "789" and "111", you can use the third formula above and replace "A1" with the cell reference that contains the string. The formula will return the position number of the third space, which is 11 in this case.

Similarly, if you have the string "abcd efghi jklmnopqr stuvxy", and you want to find the position number of the space between "r" and "s", you can use the third formula above and replace "A1" with the cell reference that contains the string. The formula will return the position number of the third space, which is 17 in this case.

Find position number of third space
 

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.

Find position number of third space
 

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Steve Stad" wrote:

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.

Find position number of third space
 

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" wrote:

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.

Find position number of third space
 

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.

"Mike H" wrote:

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" wrote:

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.

Find position number of third space
 

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.


.

Find position number of third space
 

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.


.