finding last space in a text function

[Roger on Excel]

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

 

[Ron Rosenfeld]

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

 

[Nigel]

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

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