finding last space in a text function
On Sun, 20 Jul 2008 18:18:10 -0700, Roger on Excel
wrote:
I want to locate the last space in a text string, so that i may reproduce the
string without the characters which occur after the space.
Foe example if i have
"Hydrochloric acid 1M" as the string, i would like to produce "Hydrochloric
acid" from this.
The text string can vary in length as can the final text to be removed
Can anyone help?
Thanks,
Roger
To get the position of the last space in a string in A1:
=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
To return everything up to the last space:
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
--ron
|