Nested IF formula question

[John_Vo]

Hi,

I am trying to figure out the correct formula to return a UPS or FedEx tracking link based on the number of characters in a particular field.

I have a field called “Tracking Number” it will contain either a single UPS tracking number that is 18 characters, or a single FedEx tracking number that is 12 or more characters depending on if there are multiple tracking numbers separated by a comma.

What I would like to do is have the formula display the appropriate UPS tracking link with the tracking number added in IF the Tracking Number field length is 18, or the appropriate FedEx tracking link if the field length is NOT empty. If it is empty, then I don’t want any value returned.

So far, I have figured out how to do it if the Length of tracking number field is 18 (for UPS) or not 18 for FedEx and to not display anything if there is no value in the tracking number field. However, I need to make the formula display the FedEx link for any value that is NOT 0 or 18.

I hope I explained myself clearly enough to make sense and get help. Here is the formula I have so far:

IF(LEN({Tracking Number})= 18, ‘UPS LINK GOES HERE’’&{Tracking Number},IF(LEN({Tracking Number})=12, ‘FEDEX LINK GOES HERE’&{Tracking Number},0))

[W_Vann_Hall]

Here’s the pseudocode:

IF
    {Tracking Number} isn't blank,
    IF {Tracking Number} is 18 characters long,
        It's UPS, so use the UPS link,
        Otherwise, it's FedEx, so use the FedEx link,
            and append {Tracking Number} to whichever link you use.

And the code:

IF(
    {Tracking Number},
    IF(
        LEN(
            {Tracking Number}
            )= 18,
        'UPS LINK GOES HERE',
        'FEDEX LINK GOES HERE'
        )&{Tracking Number}
    )

I think that should work.

[John_Vo]

Holy moly, that did it! Thank you SO much. I never would have figured it out but after examining your code for a bit, i think I understand how to nest more efficiently (and correctly) now!