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.

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.

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.