Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to search a string from the right ?
Hello people,
First time around here. I am not sure I'm on the right newsgroup, so please redirect me if needed. Here's my question: I need to be able to search a string from the right in an Excel-cell. Basically I need to find the last space in a string. I can find the first space in a string with the find-command but that works only from left to right. How can I do this from right to left ? Many thanks, Mav. |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to search a string from the right ?
You can still use the FIND function, you just need a way to identifiy the
last space. This can be accomplished by using SUBSTITUTE to replace the last space with a different character. For this example, I'll use the tilde ~ character. Whatever character you use, make sure it is one that would never occur normally in your data. =FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) HTH, Elkar "EuroMaverick" wrote: Hello people, First time around here. I am not sure I'm on the right newsgroup, so please redirect me if needed. Here's my question: I need to be able to search a string from the right in an Excel-cell. Basically I need to find the last space in a string. I can find the first space in a string with the find-command but that works only from left to right. How can I do this from right to left ? Many thanks, Mav. |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
How to search a string from the right ?
Hello Elkar,
That worked like a charm ! Many thanks, Mav. "Elkar" wrote: You can still use the FIND function, you just need a way to identifiy the last space. This can be accomplished by using SUBSTITUTE to replace the last space with a different character. For this example, I'll use the tilde ~ character. Whatever character you use, make sure it is one that would never occur normally in your data. =FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) HTH, Elkar "EuroMaverick" wrote: Hello people, First time around here. I am not sure I'm on the right newsgroup, so please redirect me if needed. Here's my question: I need to be able to search a string from the right in an Excel-cell. Basically I need to find the last space in a string. I can find the first space in a string with the find-command but that works only from left to right. How can I do this from right to left ? Many thanks, Mav. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
SUMPRODUCT and search string | Excel Worksheet Functions | |||
Q: search in string | Excel Discussion (Misc queries) | |||
Segregating a Search String | Excel Discussion (Misc queries) | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |