Forum Discussion
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 p...
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.
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)) ))
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)) ))