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?

 

[Rick Rothstein]

Hey Rick! It's been a while.

The original poster was looking for a right-to-left solution. I Necro'd the post since it was a couple years old, and on the right track for what I needed. my issue was finding the second delimiter so I knew where to end the MID. I'll take a deeper look at your function later! Always seeking to learn!

In the future, it might be better to start you own thread when your question is not exactly like the thread you think you want to post in... then people like me who only look at the original message and thread title and never look to see if the thread is old or has bein "Necro'd" won't be lured into answering the wrong question.

 

[CatyH]

I have this same question, except if my person's name is Matthew Brummel Smith, I don't get "4", I get "20"... because it's still finding the first instance of "m"... How do I find the last instance of m? Wish there were a parameter that allowed you to specify direction of search. Why must we always assume left to right and top to bottom? Why can't vertical and horizontal direction be declared... ?someday... Excel 2020!

 

[Scott Huish]

I'm not sure how you get 20, but how about this?

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

 

[CatyH]

Thanks, Scott - that works really well for finding the last instance... I'm curious now about:
1) finding the penultimate instance (see... i have a cell that contains the path name for the current file, which i need because i've parameterized the macros to call other files in different folders and I need to know which level in the hierarchy this one is...) so ... the penultimate existence of the "SLASH" character will help me find the parent folder name... and...
EDIT: well, actually... i am appending... since my pathname ends in a "SLASH" I can just do Len () -1. So I can find penultimate pretty easily in this case... but still have a question on #2 ...

2) what in the world does your code MEAN and how DOES IT WORK? 2^15 must be just so it exhausts all possibilities (because no string will ever be THAT long, right?) what is the Indirect ("1:"&Len(a1)) all about? and the +1 at the end?

I love a good mystery, but feel ill-equipped to decipher this one.... oh... and THANK YOU for your help!

I'm not sure how you get 20, but how about this?

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

 

[Scott Huish]

2^15 is equal to 32,768, the largest amount of text an Excel cell can hold.

the ROW(INDIRECT part is so that you can create an array in the exact amount of characters in the cell.
So, if the length of your cell was 10 characters, it would create a an array of {1,2,3,4,5,6,7,8,9,10} by iterating through those ROW values.

Now if the value was found at 8. This would be 3rd from the end, but the length of the string (10) - where it found the character 8 is 2, so you have to add 1 to show that is 3rd from the end.

However, your question now isn't what I was answering. You specifically asked about Matthew Brummel Smith and then your question changed. If you are trying to remove text before or after a specific character the answer would be different, so it's important to ask the actual question you are concerned about.

It sound like now, you would like everything before the last slash in a cell, if that's the case, then try this:

Excel 2010
	A	B
1	c:\test\folder2\file.xls	c:\test\folder2
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(A1,"",REPT(" ",1000),LEN(A1)-LEN(SUBSTITUTE(A1,"",""))),1000))

 

[CatyH]

Yes - the curse of the brain... i don't just read what IS, but always thinking about WHAT ELSE COULD THIS BE USED FOR... I was using MID to parse the string to what I needed. There were various pieces of the path that I needed to dissect and pull out (it's a file to live on a shared drive but local copy means I can't hard code path for any of the files' locations. So i need to build the path based on where they are. I have cells to pull apart what i need and then macros to create the desired variables and references).

I SO appreciate your explanation! With knowing HOW / WHY something works, it becomes more applicable to other instances besides Matthew Brummel Smith


AND WHAT????? THE REPT FUNCTION?!? THIS IS AMAZING NEW KNOWLEDGE!!!! AWESOME!!!! You are a wealth of knowledge! THANK YOU!!!!

...

However, your question now isn't what I was answering. You specifically asked about Matthew Brummel Smith and then your question changed. If you are trying to remove text before or after a specific character the answer would be different, so it's important to ask the actual question you are concerned about.

It sound like now, you would like everything before the last slash in a cell, if that's the case, then try this:

Excel 2010

	A	B
1	c:\test\folder2\file.xls	c:\test\folder2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(A1,"",REPT(" ",1000),LEN(A1)-LEN(SUBSTITUTE(A1,"",""))),1000)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[CatyH]

Took me a minute to realize your "" are actually "SLASH" [SLASH being the character]... are ALL the "" = "SLASH" including the final (well, right-most) SUBSTITUTE ? Or in that one are you subbing the SLASH for NULL?

2^15 is equal to 32,768, the largest amount of text an Excel cell can hold.

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(A1,"",REPT(" ",1000),LEN(A1)-LEN(SUBSTITUTE(A1,"",""))),1000)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Scott Huish]

Sorry, it didn't post correctly, let's try again:

=TRIM(LEFT(SUBSTITUTE(A1,"&bsol;",REPT(" ",1000),LEN(A1)-LEN(SUBSTITUTE(A1,"&bsol;",""))),1000))