Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using FIND function to locate space between first and last name | Excel Worksheet Functions | |||
How to find and replace blank space (x) in John Smithx in Excel? | Excel Discussion (Misc queries) | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) | |||
Can I find and replace "white space" in a cell in Excel? | Excel Worksheet Functions | |||
Find last space from the right of text | Excel Discussion (Misc queries) |