How to approach this issue with Tracking number (FedEx , UPS)

I have a compiled list of tracking numbers I use from the following shipping providers FedEx and UPS in an excel file.

The issue is that FedEx has their tracking number with extra values that don't need to be there.

1. FedEx tracking number count = 32 will only use digits from 17-28 to track the shipment.
2. FedEx tracking number count = 22 will only use the last 8-22 to track the shipment.
3. FedEx tracking number count = 34 will only use the last 21-34 to track the shipment.

and UPS if begins with 1Z ignore and go to next.

I need the whole worksheet to format and extract only the necessary tracking number for record.

How should I approach this?

What do you currently do?

can you share this document or a sampling of the data with 2 or 3 of each criteria - with that we should be able to offer up solutions.

I would imagine though, that you would know what you are entering and therefore will indicate if it is FedEx or UPS

So if I was doing this it would be the following columns

SHIPPER | Tracking Number | Usable Tracking Number

Usable Tracking Number would be a formula....
Formula would be the following based on what you have above...

Please Login or Register  to view this content.

ummm If I missed a parenthsis I apologize but that is roughly the setup I would go with to extract or make a Usable Tracking number

Again, sample data would help to build this out instead of imagining what it could be -

Perhaps something like this?

	A	B	C	D
1	FEDEX	1234567890123456789012	22	890123456789012
2	FEDEX	12345678901234567890123456789012	32	789012345678
3	FEDEX	1234567890123456789012345678901234	34	12345678901234
4
5	UPS 1Z	1z1234567890123456789012		1234567890123456789012
6	UPS	1234567890123456789012		1234567890123456789012

D1=IF(LEN(B1)=34,MID(B1,21,99),IF(LEN(B1)=32,MID(B1,17,12),MID(B1,8,99)))
copied down

D5=IF(LEFT(B5,2)="1Z",MID(B5,3,99),B5)
copied down

Or if you need it in 1, you would need some test to ID which is FEDEX and which UPS, then combine based on that
=IF(A1="FEDEX",IF(LEN(B1)=34,MID(B1,21,99),IF(LEN(B1)=32,MID(B1,17,12),MID(B1,8,99))),IF(LEFT(B1,2)="1Z",MID(B1,3,99),B1))

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Please Login or Register  to view this content.

Place that in C2 or E2, the only example not in your data is if it is UPS and does NOT contain 1Z. If this happens it will take the entire Tracking number... other than that this is doing what you need I believe -

works great! is there a way I can embedded the code to the whole worksheet?

for example if I scan a barcode in B2 I do not need the If statement in C2, B2 should just automatically convert.

would this be done with macro? and enable code in the worksheet that is selected?

This works with your example:

Formula:

Please Login or Register  to view this content.

Use the Fill Handle and copy down column C and all your entries in column B will automatically be converted for you.

If you make it a table, and enter the formula in the top of column C it will auto fill as you add new entries to the table

Make the entire table of data, a table... And if there is not an open column (C) insert a new column so that there is.

Place the formula in C2 and it will apply to the entire list -

To make the current data a Table... simply click on A1 (First header) tap CTRL+A, it will highlight the data to the end. Then while it is highlighted, hit CTRL+T then say okay...it is now a Table

This separates the tracking numbers. Change columns as required. the IZ numbers are in column D and the other numbers are in column C.

See the second file that has column A eliminated and therefore puts IZ numbers in Column C with the other numbers in column B.

This will put all numbers in column C as requested.