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

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

In VBA use the InStrRev function......

sText = Left(Range("A1"), InStrRev(Range("A1"), " ") - 1)


--

Regards,
Nigel




"Roger on Excel" wrote in message
...
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