Find last space
 

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

Find last space
 

=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" wrote in message
...
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

Find last space
 

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


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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

Find last space
 

=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.


--
Regards,

Peo Sjoblom


"Francis Hookham" wrote in message
...
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


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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