Mike has given you a formula to find the last space which you can use in a
MID function call as you mentioned; however, you might want to consider this
alternative which will give you the part of the data you asked for in a
shorter function using less function calls...

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"")

It is because of alternative methods of accomplishing a goal like this that
I asked you "why" you wanted to find the last space.

--
Rick (MVP - Excel)


"delmac" wrote in message
...
I've got an imported file of 12000 address and I only need the 1st line
which
ends at the last space from the right. I can then using len and mid to
delete
the last part of the string if I know where the last space is.

Regards
--
delmac


"Rick Rothstein" wrote:

A couple of questions. First off, are you asking for a worksheet formula
solution or a VB solution? Second, what is the goal of looking for that
last
space... in order to find the last "word" in the line? If so, that would
be
the better question to ask... in other words, tell us what you are
ultimately trying to accomplish as there *may* (no guarantee) be a better
way to do it than the one you have in mind.

--
Rick (MVP - Excel)


"delmac" wrote in message
...
HI all, has anyone got a formula for finding thwe 1st space from the
right
of
a string
e.g. These can have variable lengths as they are all addresses
Gar 29 Durban Avenue Dalmuir West C

I want to find the space between West and C

Thanks all (again)
--
delmac