Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KingGeezer
 
Posts: n/a
Default 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 right-most 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default finding rightmost location of a character

Here's one way:

With your text in Cell A1
B1: =LEN(A1)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(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, WinXP-Pro


"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 right-most 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KingGeezer
 
Posts: n/a
Default 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)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(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, WinXP-Pro


"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 right-most 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KingGeezer
 
Posts: n/a
Default finding rightmost location of a character

One follow-up 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)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(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, WinXP-Pro


"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 right-most 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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 right-most 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 add-in 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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, WinXP-Pro


"KingGeezer" wrote:

One follow-up 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)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(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, WinXP-Pro


"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 right-most 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default finding rightmost location of a character

Ron Coderre wrote...
....
With your text in Cell A1
B1: =LEN(A1)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(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 1-31 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"