Searching from the right of text in a cell

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

What I need to be able to do is determine how many
characters in from the right of some text in a cell the
first space occurs. The formula SEARCH(" ",A4) will give
me the count to the first space from the left. Does any
now how to get the SEARCH function to perform from the
right of the text?
 
The following array formula should work.

=MAX(IF(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)="
",ROW(INDIRECT("1:"&LEN(A4))),0))

*Ensure that you Ctrl-Shift-Enter this formula instead of just hitting
Enter.

/i.
 
Immanuel - It works great - thankyou
-----Original Message-----
The following array formula should work.

=MAX(IF(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)="
",ROW(INDIRECT("1:"&LEN(A4))),0))

*Ensure that you Ctrl-Shift-Enter this formula instead of just hitting
Enter.

/i.





.
Click to expand...
 
The following array formula should work.

=MAX(IF(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)=" ",
ROW(INDIRECT("1:"&LEN(A4))),0))
Click to expand...

It does, but there's a way to do this without an array formula.

=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
 
Very elegant! I like.

/i.

Harlan Grove said:
...

It does, but there's a way to do this without an array formula.

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

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Click to expand...
 
Back
Top