1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30If I follow the logic... it is replacing the blank withThat's very close!
^^ and it finds the 3rd instance of ^^. -- correct?
=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" <Stev...@discussions.microsoft.com> wrote in message news:39798AAB-5528-42A4...@microsoft.com...
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" <Stev...@discussions.microsoft.com> wrote in message
news:F71EB08D-81EC-4633...@microsoft.com...
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.
.
On Monday, May 29, 2023 at 1:38:08 PM UTC-7, Michael Charles wrote:whicwich.
On Monday, February 1, 2010 at 1:40:31 PM UTC-8, T. Valko wrote:
If I follow the logic... it is replacing the blank withThat's very close!
^^ and it finds the 3rd instance of ^^. -- correct?
=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" <Stev...@discussions.microsoft.com> wrote in message news:39798AAB-5528-42A4...@microsoft.com...
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 2niggernigeernggerniggerAssuming there will *always* be at least 3 spaces:
=FIND("^^",SUBSTITUTE(A1," ","^^",3))
--
Biff
Microsoft Excel MVP
"Steve Stad" <Stev...@discussions.microsoft.com> wrote in message
news:F71EB08D-81EC-4633...@microsoft.com...
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.
.
On Monday, February 1, 2010 at 1:40:31 PM UTC-8, T. Valko wrote:their ey como le primero lettre bcdefg bcdefg. no . bcdefg. dd?
If I follow the logic... it is replacing the blank withThat's very close!
^^ and it finds the 3rd instance of ^^. -- correct?
=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" <Stev...@discussions.microsoft.com> wrote in message news:39798AAB-5528-42A4...@microsoft.com...
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30Assuming there will *always* be at least 3 spaces:
=FIND("^^",SUBSTITUTE(A1," ","^^",3))
--
Biff
Microsoft Excel MVP
"Steve Stad" <Stev...@discussions.microsoft.com> wrote in message
news:F71EB08D-81EC-4633...@microsoft.com...
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.
.
On Monday, February 1, 2010 at 1:40:31 PM UTC-8, T. Valko wrote:
If I follow the logic... it is replacing the blank withThat's very close!
^^ and it finds the 3rd instance of ^^. -- correct?
=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" <Stev...@discussions.microsoft.com> wrote in message news:39798AAB-5528-42A4...@microsoft.com...
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 2niggernigeernggerniggerAssuming there will *always* be at least 3 spaces:
=FIND("^^",SUBSTITUTE(A1," ","^^",3))
--
Biff
Microsoft Excel MVP
"Steve Stad" <Stev...@discussions.microsoft.com> wrote in message
news:F71EB08D-81EC-4633...@microsoft.com...
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.
.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 298 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 17:43:09 |
| Calls: | 6,677 |
| Files: | 12,222 |
| Messages: | 5,341,218 |