I need to extract the rightmost part of a cell, from the last "/" character in the cell. Problem is, there may be several /s in a cell and I need to identify the last one of them.
Thanks for your help.
I need to extract the rightmost part of a cell, from the last "/" character in the cell. Problem is, there may be several /s in a cell and I need to identify the last one of them.
Thanks for your help.
Last edited by Basz; 06-16-2009 at 10:07 AM.
Unfortunately, if you don't have Office 2007, there's no default Excel functions (that I know of) that can do this.
However, it's fairly straightforward to make one.
- Hit ALT + F11
- Right-click on Sheet1. Goto Insert-->Module
- Inside the Module1 code, paste the following:
And that's it!Please Login or Register to view this content.
Just go into your spreadsheet now, and type this in:
Where A1 is the cell with the forward-slashes in it.Please Login or Register to view this content.
I hope this helps!
starryknight64
You could use this formula
=REPLACE(A1,1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")
Thanks, that works!
Here's another way...
=REPLACE(A1,1,FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))),"")
The danger is that the string ^^ exists in the cell in question. To avoid the disastrous effects of this, I recommend this sterling alternative:
=REPLACE(B3,1,FIND("Cheeky Charlie is thoroughly wonderful",SUBSTITUTE(B3,"/","Cheeky Charlie is thoroughly wonderful",LEN(B3)-LEN(SUBSTITUTE(B3,"/","")))),"")
lol.......................................................
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks