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

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: reading the 5 characters after the LAST "/" in

Sethrow said:
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

New Member
Joined
Nov 15, 2005
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: Good work

Sethrow said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,201
Members
412,447
Latest member
immy
Top