Return last numbers of a variable length string

maounde

New Member
Joined
Jan 8, 2008
Messages
10
I am at a loss here. I have a collection of urls like this:
http://mycompany.com/t5/something/bd-p/153 (I need the formula to return 153)
or
http://mycompany.com/t5/something-else/bd-p/52 (I need the formula to return 52 )

Problems I find:
- The length in characters of the urls are variable
- The numbers at the end could be 2 to 4 digits

The only reliable constant I have is that the number I need to extract at the end is always after the last "/" and there are always exactly 6 "/", if it helps.
thx in advance.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
=RIGHT(RIGHT(A1,5),LEN(RIGHT(A1,5))-FIND("/",RIGHT(A1,5)))

You said it's always AT MOST 4 digits. So just truncate that bad boy to 5 and then run your logic on that. Otherwise you could go through and start at the 6th "/" but that's a longer code.
 

maounde

New Member
Joined
Jan 8, 2008
Messages
10
Wow... Both formulas work great. I am more familiar with the syntax of the first. The lookup one, with ^ and {}, not so much... but never mind, it works!
Thank you both!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,877
Members
409,668
Latest member
mitunsLax

This Week's Hot Topics

Top