how to find character's position in a cell from right to left?

[Lester07]

How can I look up for text/character's position in a cell from right to left using various text functions?

 

[Smitty]

Welcome to the Board!

This may be a start:

=FIND("a",A1)

Hope that helps,

 

[Domski]

Welcome to the Board!

This may be a start:

=FIND("a",A1)

Hope that helps,

Don't think that addresses the "right to left" bit Smitty.

Can you give an example of what you are trying to achieve.

Dom

 

[pgc01]

Hi

This formula gives you the position of the last "a" in A1:

=FIND("|",SUBSTITUTE(A1,"a","|",LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))))

Remark: I used the character "|" as auxilliary, assuming there's no "|" in A1. If there is, choose another character not in A1, like for ex. "#".

 

[barry houdini]

Another way....

=LOOKUP(2^15,SEARCH("a",A1,ROW(INDIRECT("1:"&LEN(A1)))))

 

[Lester07]

Thanks guys, I learned something new... but what i really want to know is how can I get the number of characters "from the right" to the character I'm looking for...

Ex. I want to find the position of letter "m" in "John Smith", but I need to count it from the right. With that example, it should give me "2" instead of "6" when i use the "FIND" function...

Thanks so much guys...

 

[barry houdini]

Wouldn't it be 4?

With John Smith in A1

=MATCH("m",INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0)

 

[mikerickson]

How about
=LEN(A1)-FIND("m",A1)

will return 3 if "John Smith" is in A1.

(If the "desired 2" isn't a typo, please explain how it was derived.)

 

[Lester07]

I'm sorry, I'm so brain dead LOL...Right, it should be 4 not 2... Anyway it worked!!! Great! Now I can have a goodnight sleep... Thanks!!!

 

[VLG]

Wouldn't it be 4?

With John Smith in A1

=MATCH("m",INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0)

Thanks, Barry Houdini. Had a use for this today.

I don't know how the formula works, but it works.

The =SUBSTITUTE is also handy in certain situations.

-VLG