Find Space from Right [in cell formula]

  • Thread starter Thread starter Jimbo213
  • Start date Start date
J

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?
 
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
 
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.
 
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)
 
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?
Click to expand...


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)))
 
Ok great ...
Now, how do you modify that to find the SECOND space from the right?

--
Thanks for your reply & assistance.
Jimbo213


Rick Rothstein said:
Here is yet another way...

=LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))
Click to expand...
 
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, I assume you meant "counting from the right ... as InstrRev"
Click to expand...

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?

--
Click to expand...



=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
 
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
 
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' ]
Click to expand...

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 said:
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' ]
Click to expand...

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
Click to expand...
 
Ok that's clear now.
I never heard the term "0-based count " before

Thanks for your clarification.
Jimbo213
Click to expand...

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