if function for removing characters then another if

[Sphinx404]

I have a lot of lines in a data set that look something like this:

006MSP95228184
006ATL95228195
006ORD95485250

I don't care about anything but the 95228184, so I used the following function to rid myself of it:

=IF(LEFT(A1,3)="006",RIGHT(A1,8),"")

RESULT: 95228184 - excellent right?

However, this doesn't account for anomalies:

006DTW21214
006MCO
006816273172

etc....

I need a formula that will just give me the final 8 numbers at the back end while accounting for the anomalies.

I need the final 8 numbers, but if there isn't 8 numbers I don't want it, and if it has alphabet characters in it, I don't want that crap either. This is way above my head, so you Excel pros, I need you! Any help you can provide would be greatly appreciated.

Steve

 

[barry houdini]

Perhaps try this version

=IF(AND(LEFT(A1,3)="006",ISNUMBER(RIGHT(A1,8)+0)),RIGHT(A1,8),"")

 

[Sphinx404]

HOUDINI!!!! You rascal! It's flawless. I'd give you the ol' football ***-slap if I could. Nice work!

 

[Rick Rothstein]

I need a formula that will just give me the final 8 numbers at the back end while accounting for the anomalies.

I need the final 8 numbers, but if there isn't 8 numbers I don't want it, and if it has alphabet characters in it, I don't want that crap either.

Does this formula do what you want...

=IF(AND(LEFT(A1,3)="006",AND(IF(MID("A00000000",{1,2,3,4,5,6,7,8,9},1)="A",(MID(RIGHT(A1,9),{1,2,3,4,5,6,7,8,9},1)>="A")*(MID(RIGHT(A1,9),{1,2,3,4,5,6,7,8,9},1)<="Z"),ISNUMBER(-MID(RIGHT(A1,9),{1,2,3,4,5,6,7,8,9},1))))),RIGHT(A1,8),"")

 

[Sphinx404]

Dear lord Rothstein, yours also works flawlessly. I need to start brushing up on my Excel skills. You guys are the celebrities of the data world. Thank you!

 

[Rick Rothstein]

Perhaps try this version

=IF(AND(LEFT(A1,3)="006",ISNUMBER(RIGHT(A1,8)+0)),RIGHT(A1,8),"")

HOUDINI!!!! You rascal! It's flawless. I'd give you the ol' football ***-slap if I could. Nice work!

Are you sure? You said 006816273172 was an anomaly, but barry's formula returns 16273172 for it. Also, depending on how anomalous your data can be, his formula would return 952281E4 for 006MSP952281E4 and 8OCT2017 for 006MSP95228OCT2017 as well as other similarly constructed values.

 

[Sphinx404]

There are 2 numbers that show up on Barry's return that do not show up on yours

Original Manipulated

006ATL0816516100	16516100
006ms095566236	95566236

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Neither of which I want. Rick, I also ran your formula in a much larger report that encompasses all of 2017 rather than just last month and yours still didn't return even the most elusive of anomalies. Thus far, it accounts for what I need it to account for, and leaves the anomalies out. That's a hell of a formula. I told ya... I'm way out of my league here. Thank you for the support. I am truly grateful that this website exists and the public has access to gentlemen like yourselves, thank you!

 

[Sphinx404]

Okay, so I have another problem.....

Can I add another if function on the tail of the original formula you created?

I have hundreds of the following which will contain: UPS, FEDEX, DHL, etc. in the data:

DHL00695228184
FEDEX00695228195
UPS00695485250

Is it possible to create an all encompassing formula that would separate these out as well... at the tail end of the formula you provided? Or am I asking for trouble with such a lengthy formula?

=IF(LEFT(A1,3)="FED","FedEx","FALSE"),"")