Find last space

[Francis Hookham]

Can you let me have the function for finding the last space in a string?
At the same time, for future ref the #th space in a string please.

My present problem is to separate the number at the end of a series of
strings, typically:

0 - Council and Club Meetings 27

I need to find the last space in order to separate
0 - Council and Club Meetings
and
27
into two cells using =LEFT() and =RIGHT()

Francis Hookham

 

[Bob Phillips]

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

gets the last space

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

gets the characters upto the last space

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Francis Hookham]

Many thanks Bob - just the job - I wish I understood how it is constructed!

When I extract the numbers from the end of the string they are still text
and I need to use them as numbers - how?

While on the subject of finding spaces, how do I use your formula to find a
specified space - say the 4th space in a string?

Francis

 

[Peo Sjoblom]

=FIND("^^",SUBSTITUTE(A2," ","^^",4))

will give you the number counted from the left of the 4th space

so

We have no bananas today sir, how about a durian fruit instead?

would return 19 since the 4th space counted from the left is in the 19th
place

meaning that if you want what's left of the 19th character you would use

=LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",4))-1)

Note that the ^^ can be replaced with any character(s) that is not used on
a regular basis in a text string.