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 (Packin...
excel
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)))))
  • TeachASU2020's avatar
    TeachASU2020
    Copper Contributor
    Feb 06, 2020

    Charla74 I get a response that I have entered too few arguments.

    • 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),””)
      • Charla74's avatar
        Charla74
        Iron Contributor
        Feb 07, 2020
        TeachASU2020

        Ok, so this formula simplifies it but not certain it meets your brief. It looks for any number of six or less digits and returns whatever it finds, otherwise (anything over 6 digits) return the first six digits of that number. Let me know if I missed the mark:

        =IF(LEN($A3)<=6,$A3,VALUE(LEFT($A3,6)))

Resources