If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




finding rightmost location of a character
I have a text string (a directory path actually) that has several "/"
characters in it. I'd like to find the location of the rightmost occurrance of an "/". For example, if the string was: "mama/poppa/bogus/dog" .... how do I find the position number of the "/" right before 'dog'? Thanks for any help you can provide! 
Ads 
#2




finding rightmost location of a character
Here's one way:
With your text in Cell A1 B1: =LEN(A1)+1MATCH("/",MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&(LEN(A1)))),1),0) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. Here's another way: B1: =LEN(LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)LEN(SUBSTITUTE(A1,"/",""))))1))+1 Just press [Enter] for that one. Does that help? *********** Regards, Ron XL2002, WinXPPro "KingGeezer" wrote: > I have a text string (a directory path actually) that has several "/" > characters in it. > I'd like to find the location of the rightmost occurrance of an "/". > For example, if the string was: "mama/poppa/bogus/dog" .... how do I find > the position number of the "/" right before 'dog'? > Thanks for any help you can provide! 
#3




finding rightmost location of a character
Ron! replace that bronze status with a gold one!
I think this will work just fine! It'll take me longer to *analyze* how this work, than it did for you to answer it! many thanks! "Ron Coderre" wrote: > Here's one way: > > With your text in Cell A1 > B1: =LEN(A1)+1MATCH("/",MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&(LEN(A1)))),1),0) > Note: Commit that array formula by holding down the [Ctrl][Shift] keys and > press [Enter]. > > Here's another way: > B1: > =LEN(LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)LEN(SUBSTITUTE(A1,"/",""))))1))+1 > > Just press [Enter] for that one. > > Does that help? > > *********** > Regards, > Ron > > XL2002, WinXPPro > > > "KingGeezer" wrote: > > > I have a text string (a directory path actually) that has several "/" > > characters in it. > > I'd like to find the location of the rightmost occurrance of an "/". > > For example, if the string was: "mama/poppa/bogus/dog" .... how do I find > > the position number of the "/" right before 'dog'? > > Thanks for any help you can provide! 
#4




finding rightmost location of a character
...A variation on Ron's first suggestion =MATCH(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="/")) confirmed with CTRL+SHIFT+ENTER  daddylonglegs  daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=504238 
#5




finding rightmost location of a character
One followup Ron, if you're still monitoring .....
Wouldn't it be enough to stop after the 'search' function? I get the same #, if I don't bother with the "len(left ....) parts. Thanks; you're a star. "KingGeezer" wrote: > Ron! replace that bronze status with a gold one! > I think this will work just fine! It'll take me longer to *analyze* how > this work, than it did for you to answer it! > many thanks! > > "Ron Coderre" wrote: > > > Here's one way: > > > > With your text in Cell A1 > > B1: =LEN(A1)+1MATCH("/",MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&(LEN(A1)))),1),0) > > Note: Commit that array formula by holding down the [Ctrl][Shift] keys and > > press [Enter]. > > > > Here's another way: > > B1: > > =LEN(LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)LEN(SUBSTITUTE(A1,"/",""))))1))+1 > > > > Just press [Enter] for that one. > > > > Does that help? > > > > *********** > > Regards, > > Ron > > > > XL2002, WinXPPro > > > > > > "KingGeezer" wrote: > > > > > I have a text string (a directory path actually) that has several "/" > > > characters in it. > > > I'd like to find the location of the rightmost occurrance of an "/". > > > For example, if the string was: "mama/poppa/bogus/dog" .... how do I find > > > the position number of the "/" right before 'dog'? > > > Thanks for any help you can provide! 
#6




finding rightmost location of a character
On Mon, 23 Jan 2006 15:20:02 0800, "KingGeezer"
> wrote: >I have a text string (a directory path actually) that has several "/" >characters in it. >I'd like to find the location of the rightmost occurrance of an "/". >For example, if the string was: "mama/poppa/bogus/dog" .... how do I find >the position number of the "/" right before 'dog'? >Thanks for any help you can provide! You can use regular expressions to easily extract whatever phrase you wish from the string. They are available either via VBA, or, most easily, from Longre's free morefunc.xll addin at http://xcell05.free.fr For example, to get the position of the last "/" =REGEX.FIND(A1,"\/",1) or =REGEX.FIND(A1,"\/\w+$") But, perhaps you want the last word (dog): =REGEX.MID(A1,"\w+$") or perhaps everything except the last word: mama/poppa/bogus/ =REGEX.MID(A1,".*\/") Maybe without the trailing "/" mama/poppa/bogus =REGEX.MID(A1,".*(?=\/)") ron 
#7




finding rightmost location of a character
You're absolutely correct.
No point in making Excel work any harder than it needs to. *********** Regards, Ron XL2002, WinXPPro "KingGeezer" wrote: > One followup Ron, if you're still monitoring ..... > Wouldn't it be enough to stop after the 'search' function? > I get the same #, if I don't bother with the "len(left ....) parts. > Thanks; you're a star. > > "KingGeezer" wrote: > > > Ron! replace that bronze status with a gold one! > > I think this will work just fine! It'll take me longer to *analyze* how > > this work, than it did for you to answer it! > > many thanks! > > > > "Ron Coderre" wrote: > > > > > Here's one way: > > > > > > With your text in Cell A1 > > > B1: =LEN(A1)+1MATCH("/",MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&(LEN(A1)))),1),0) > > > Note: Commit that array formula by holding down the [Ctrl][Shift] keys and > > > press [Enter]. > > > > > > Here's another way: > > > B1: > > > =LEN(LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)LEN(SUBSTITUTE(A1,"/",""))))1))+1 > > > > > > Just press [Enter] for that one. > > > > > > Does that help? > > > > > > *********** > > > Regards, > > > Ron > > > > > > XL2002, WinXPPro > > > > > > > > > "KingGeezer" wrote: > > > > > > > I have a text string (a directory path actually) that has several "/" > > > > characters in it. > > > > I'd like to find the location of the rightmost occurrance of an "/". > > > > For example, if the string was: "mama/poppa/bogus/dog" .... how do I find > > > > the position number of the "/" right before 'dog'? > > > > Thanks for any help you can provide! 
#8




finding rightmost location of a character
Ron Coderre wrote...
.... >With your text in Cell A1 >B1: =LEN(A1)+1MATCH("/",MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&(LEN(A1)))),1),0) >Note: Commit that array formula by holding down the [Ctrl][Shift] keys and >press [Enter]. .... If you're going to use an array of sequential integers, you could use a shorter, nonarray formula. =LOOKUP(32768,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1)))))+1 and you could avoid the volatile INDIRECT using =LOOKUP(LEN(A1),FIND("/",A1, ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,LEN(A1),1 ))))+1 The other approach, substituting only the final occurrence of the substring, may be the best way to go, but it's safer to use a control character (decimal codes 131 and 127) than strings of graphic characters (all other decimal codes except 0). FWLIW, Windows .CMD batch files use ^ as a metacharacter, so ^^ represents literal circumflexes, so using "^^" as the substitution substring would be a bad idea when parsing .CMD files. 
#9




finding rightmost location of a character
Harlan Grove Wrote: > > =LOOKUP(32768,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1)))))+1 > Harlan, could you possibly explain the significance of the 32768? BTW I don't believe you need the +1 at the end  daddylonglegs  daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=504238 
#10




finding rightmost location of a character
daddylonglegs wrote...
>Harlan Grove Wrote: >> >> =LOOKUP(32768,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1)))))+1 > >Harlan, could you possibly explain the significance of the 32768? The key is the LOOKUP call. Given how it works, if its 1st argument is greater than any value in its 2nd argument, it returns the last item in its last argument with the same type as its 1st argument. Since strings can't be longer than 32767 characters in Excel, 32768 is guaranteed to be greater than any numeric value returned by FIND, so the LOOKUP formula above returns FIND's last numeric result, which corresponds to the position of the last / in A1. You could use any arbitrarily large value as the 1st argument to LOOKUP. >BTW I don't believe you need the +1 at the end Sorry, the +1 advances you to the character position after the last /. I was paying too much attention to other responses. 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Count occurrence of character within a cell  Kelli  Excel Discussion (Misc queries)  2  January 18th 06 03:20 PM 
Return cell contents based on conditional lookup  jarviscars  Excel Worksheet Functions  15  August 5th 05 08:05 AM 
Finding a character type within a cell  shineboxnj  Excel Worksheet Functions  1  July 22nd 05 03:12 AM 
Stock Location Sorting Problem  mark1caroline  Excel Discussion (Misc queries)  3  July 13th 05 03:53 PM 
Add up Plus & Minus Figures separately by Location  John  Excel Worksheet Functions  2  January 21st 05 10:40 AM 