Extracting certain data from cells

[jjacks60]

Hi,

I have a list of SKU numbers that changes constantly, but I always want to extract a certain part of these references.

1. IF there is a -, then extract whatever the length of the cell is
2. Next IF, IF length = 5, then extract the cell I.E. E41
3. Next IF, if length > 5 then find a string of 5 numbers from the cell and extract those numbers only

I currently have it working for the first two IFs, having trouble making the last happen. The issue is sometimes a SKU looks like this "P70403PAK1", sometimes like this "70320CDN5". In either of these scenarios I want the sequence of 5 numbers extracted to use as a reference in a vlookup I've made.

Here is the formula as it stands now.
=IFERROR(VLOOKUP(IF(LEN(E47)=5,LEFT(E47,5),IF(FIND("-",E47),(LEFT(E47,LEN(E47)))*****)),'Case #''s'!A:C,3,0),"No SKU Ref!")

Where the ***** is is where I'd make the last IF function.
It may require two additional IF functions.

The problem is every cell I'm reference is currently text because of ' being in front of every SKU value. This is the way the raw data comes to me I can't alter it.

Any advice is appreciated greatly.