Forum Discussion

Rudrabhadra's avatar
Rudrabhadra
Brass Contributor
Aug 24, 2022

Zero being excluded while using formula

SC-01760 to be indicated in subsequent rows in a progressive manner. I had used the following formula such that it checks the next column if “SPARE” then it should put “-01761” otherwise it should  put “SC-01761”. When I used the formula it is ignoring the zero. How can this be achieved.

excel

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 24, 2022
    it thinks -01761 is a negative number. use: '-01761 (precede it with an apostrophe)
    • Rudrabhadra's avatar
      Rudrabhadra
      Brass Contributor
      Aug 24, 2022

      mtarler Thanks for your reply. I couldn't get the result as I wanted.
      I have attached the sample file and the requirement is that the starting zero is missing . Hope I could convey properly what I am looking for and where I am wrong.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Aug 25, 2022
        you are using a formula to calculate a number and want a preceding "0" so the MID() returns text as you desire but when you +1 excel converts the text into a number and then drops the "0". if you need the "0" you need to do one of 2 options:
        re-format the number as text:
        =TEXT(MID(A2,FIND("-",A2,1)+1,LEN(A2)-D2)+1,"00000")
        or you can leave the number and change the cell formatting so it will display numbers with a minimum of 5 digits.
        btw, the above TEXT() formula could check/use the LEN of the original text portion to determine the # of 0s to use in the format:
        =LET(out, MID(A2,FIND("-",A2,1)+1,LEN(A2)-D2),
        TEXT(out+1,REPT("0",LEN(out)) ))