I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

Hi,

To find the position in the string of last space use

=LEN(A1)-LEN(RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"
","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

or if you want the last word

=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1," ",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)



Mike

"Jimbo213" wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

In VBA the command is:

A = Right(B, #ofSpacesFromRight)
where
A are the digits a # space from the right
B is the cell entry, variable or array before this operation

in your case:

A = Right(B, 1)

Hope this is helpful.



"Jimbo213" wrote:

I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

If the string to test is in A1, use the following array formula:

=MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*ROW(INDIRECT("1:"&LEN(A1))))

It will return the position of the last space in the text in cell A1.
If there is no space, it returns 0. If A1 is empty, you'll get a #REF
error.

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. You do not type in the curly braces --
Excel inserts them automatically. For much more information about
array formulas, see www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 25 Jun 2009 12:54:03 -0700, Jimbo213
wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

On Thu, 25 Jun 2009 12:54:03 -0700, Jimbo213
wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?


The position in the string of the last space (counting from the left as does
the INSTRREV VBA function):

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))



--ron

Here is yet another way...

=LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

--
Rick (MVP - Excel)


"Jimbo213" wrote in message
...

I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not
a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

Ok great ...
Now, how do you modify that to find the SECOND space from the right?

--
Thanks for your reply & assistance.
Jimbo213


"Rick Rothstein" wrote:

Here is yet another way...

=LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

--
Rick (MVP - Excel)


"Jimbo213" wrote in message
...

I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not
a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

Thanks, I assume you meant "counting from the right ... as InstrRev"

How would you modify that to find the SECOND space from the right?

--
Thanks for your reply & assistance.
Jimbo213


"Ron Rosenfeld" wrote:

On Thu, 25 Jun 2009 12:54:03 -0700, Jimbo213
wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?


The position in the string of the last space (counting from the left as does
the INSTRREV VBA function):

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))



--ron

On Fri, 26 Jun 2009 06:27:02 -0700, Jimbo213
wrote:

Thanks, I assume you meant "counting from the right ... as InstrRev"

You may be misunderstanding how InstrRev works (or perhaps I wasn't clear).

The value that InstrRev returns is the position of the "SearchFor" string (or,
in this case, <space). However, that value is the position in the full
string counting from the left.

For example, looking for the last space using InstrRev:

Now is the Time == 11

11 is the 11th character counting from the left


How would you modify that to find the SECOND space from the right?

--



=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-n))

where "n" is the 0-based count of spaces from the right. So for the 1st space
from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

second space from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))

etc.


--ron

I appreciate your reply, Ron, and I do understand that the first space from
the right in "Now is the Time" == 11

My question is how can the formula be modified to find the second space from
the right ... Now is the Time == 7 [between 'is the' ]

Thanks for your reply & assistance.
Jimbo213


"Ron Rosenfeld" wrote:

On Fri, 26 Jun 2009 06:27:02 -0700, Jimbo213
wrote:

Thanks, I assume you meant "counting from the right ... as InstrRev"

You may be misunderstanding how InstrRev works (or perhaps I wasn't clear).

The value that InstrRev returns is the position of the "SearchFor" string (or,
in this case, <space). However, that value is the position in the full
string counting from the left.

For example, looking for the last space using InstrRev:

Now is the Time == 11

11 is the 11th character counting from the left


How would you modify that to find the SECOND space from the right?

--



=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-n))

where "n" is the 0-based count of spaces from the right. So for the 1st space
from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

second space from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))

etc.


--ron

Wow that's neat.
How could you modify the formula to give
a) the position of the second-to-the-last space
b) the second-to-the-last word

--
Thanks for your reply & assistance.
Jimbo213


"Mike H" wrote:

Hi,

To find the position in the string of last space use

=LEN(A1)-LEN(RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"
","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

or if you want the last word

=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1," ",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)



Mike

"Jimbo213" wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

--
Thanks for your reply & assistance.
Jimbo213

Thank you Chip.
How would you modify that to find the SECOND space from the right?

--
Thanks for your reply & assistance.
Jimbo213


"Chip Pearson" wrote:

If the string to test is in A1, use the following array formula:

=MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*ROW(INDIRECT("1:"&LEN(A1))))

It will return the position of the last space in the text in cell A1.
If there is no space, it returns 0. If A1 is empty, you'll get a #REF
error.

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. You do not type in the curly braces --
Excel inserts them automatically. For much more information about
array formulas, see www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 25 Jun 2009 12:54:03 -0700, Jimbo213
wrote:


I found a Jan 5 2009 post to find the FIRST space from the right using the
InStrRev function. I'd like to incorporate this in a cell's formula, not a
VBA function.

Is that possible?

On Sat, 27 Jun 2009 07:06:01 -0700, Jimbo213
wrote:

My question is how can the formula be modified to find the second space from
the right ... Now is the Time == 7 [between 'is the' ]

I thought I was pretty clear when I wrote in the message to which you
responded:

=========================
=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-n))

where "n" is the 0-based count of spaces from the right. So for the 1st space
from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

second space from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
==============================

Please take a close look, especially at the last line of what I wrote
previously, and let me know what about it is not clear with regard to finding
the second space from the right.
--ron

Ok that's clear now.
I never heard the term "0-based count " before

Thanks for your clarification.
Jimbo213


"Ron Rosenfeld" wrote:

On Sat, 27 Jun 2009 07:06:01 -0700, Jimbo213
wrote:

My question is how can the formula be modified to find the second space from
the right ... Now is the Time == 7 [between 'is the' ]

I thought I was pretty clear when I wrote in the message to which you
responded:

=========================
=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-n))

where "n" is the 0-based count of spaces from the right. So for the 1st space
from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

second space from the right:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
==============================

Please take a close look, especially at the last line of what I wrote
previously, and let me know what about it is not clear with regard to finding
the second space from the right.
--ron

On Mon, 29 Jun 2009 08:05:01 -0700, Jimbo213
wrote:

Ok that's clear now.
I never heard the term "0-based count " before

Thanks for your clarification.
Jimbo213

You're welcome. Glad to help. Thanks for the feedback.
--ron

-----------------------------------------------------------------------------
Our Peering Groups change
Visit : http://spacesst.com/peerin