reading the 5 characters after the LAST "/" in a t

[Sethrow]

Ok this should be really easy (I think!) but can't get it to work! All I want to be able to do is have one column with data like the stuff below and the next colum to contain the 5 numbers (ie 42055 or 45433) that occur after the last "/" (not always the 3rd).

Anyone got any ideas?

Sample data:
October ABC/Standard Input 100x200/Random/42055
October ABC/Standard Input 100x200/Random/42055 tag 2
December AAA/Fixed Input 100x300/Fixed/45433

Many thanks,

Seth

 

[Aladin Akyurek]

Re: reading the 5 characters after the LAST "/" in

Ok this should be really easy (I think!) but can't get it to work! All I want to be able to do is have one column with data like the stuff below and the next colum to contain the 5 numbers (ie 42055 or 45433) that occur after the last "/" (not always the 3rd).

Anyone got any ideas?

Sample data:
October ABC/Standard Input 100x200/Random/42055
October ABC/Standard Input 100x200/Random/42055 tag 2
December AAA/Fixed Input 100x300/Fixed/45433

Many thanks,

Seth

Either:

=REPLACE(A2,1,SEARCH(CHAR(127),SUBSTITUTE(A2,"/",CHAR(127),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))),"")

Or, with the morefunc.xll add-in installed:

=WMID(A2,WORDCOUNT(A2,"/"),1,"/")

 

[Sethrow]

Good work

Good work although I had to change it slightly as it had everything after the last / not just the 5 numbers this works though:

=LEFT(REPLACE(A2,1,SEARCH(CHAR(127),SUBSTITUTE(A2,"/",CHAR(127),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))),""),5)

Can't use the nicer add in as its work computers and it needs to work on anyones computer and I cant really go installing that on everyones computer! Thanks again

Seth

 

[Aladin Akyurek]

Re: Good work

Good work although I had to change it slightly as it had everything after the last / not just the 5 numbers this works though:

=LEFT(REPLACE(A2,1,SEARCH(CHAR(127),SUBSTITUTE(A2,"/",CHAR(127),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))),""),5)

Can't use the nicer add in as its work computers and it needs to work on anyones computer and I cant really go installing that on everyones computer! Thanks again

Seth

Morefunc has an option to include itself into the workbook you want to distribute under Tools|Morefunc when installed.

 

[Krishnakumar]

Or..

=REPLACE(A1,1,LOOKUP(9.99999E+307,FIND("/",A1,ROW($1:$1024))),"")

HTH