Forum Discussion

TeachASU2020's avatar
TeachASU2020
Copper Contributor
Feb 05, 2020

Excel formula assistance

I have a sheet that I import a daily ticket report from our corporate server.  This report is unformatted and I have created a worksheet that I extract only the fields that I need.  One field (Packing List #) is a numeric field.  This field generally consists of 8 digits.  I have used an IF statement (=IF(LEN($A3)=8,IFERROR(VALUE(LEFT($A3,6),IFERROR(VALUE(TRIM(LEFT($A3,5))))) for a field on the worksheet that extracts all the packing list # except for the last two digits.  The formula works if there are 8 digits.  If it is less than 8, I need to extract the digits minus the last two.  If there are less than 8 digits the term FALSE is returned which is should, but I need the actual digits returned so the formula in this cell can extract data from another field.  I have included a screen shot of the worksheet.  

 

Thank you in advance.  Any help/suggestions would be appreciated.

 

excel

7 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Feb 06, 2020
    I’m not able to get to a computer but try below:

    =IF(LEN($A3)>=8,IFERROR(VALUE(LEFT($A3,6),IFERROR(VALUE(TRIM(LEFT($A3,LEN($A3)-2)))))
      • Charla74's avatar
        Charla74
        Iron Contributor
        Feb 06, 2020
        I can figure it out properly in front of a machine but seems it might need the result if false; how about this:

        =IFERROR(IF(LEN($A3)>=8,VALUE(LEFT($A3,6),VALUE(TRIM(LEFT($A3,LEN($A3)-2))))),FALSE),””)
  • mathetes's avatar
    mathetes
    Gold Contributor
    Feb 05, 2020

    TeachASU2020 

     

    A picture may be worth a thousand words, but in the excel techcommunity a spreadsheet (sample) is worth even more. Could you upload such a sample...it's truly a lot more productive to work with that, than with an image, because there may be things not visible in the image that are affecting your results. Thanks

      • TeachASU2020's avatar
        TeachASU2020
        Copper Contributor
        Feb 06, 2020

        TeachASU2020  This is an older spreadsheet that I have been using.  An associate of mine and I created the macros and formulas.  He did most of the formulas that is why the code looks a little crazy.  I was attempting to modify his code to get it to pull the correct number of digits regardless if there are 5 or 8 digits.

Resources