Find Space from Right [in cell formula]

[Jimbo213]

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?

 

[Mike H]

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

 

[Philosophaie]

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.

 

[Chip Pearson]

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)

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

Here is yet another way...

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

 

[Jimbo213]

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

--
Thanks for your reply & assistance.
Jimbo213

Here is yet another way...

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

 

[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?

 

[Ron Rosenfeld]

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

 

[Jimbo213]

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

 

[Jimbo213]

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

 

[Jimbo213]

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

 

[Ron Rosenfeld]

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

 

[Jimbo213]

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

Thanks for your clarification.
Jimbo213

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

 

[Ron Rosenfeld]

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