Questions

Excel : Leading Zeros in Formulas

+
0 Votes
Locked

Excel : Leading Zeros in Formulas

zachotey
To best simulate, input the following....
A2 = 021
B2 = 05

A3 = update product_code = 'A2' where id_code = 'B2'

I have formated each cell as custom "000" for A2, "00" for B2. This is to ensure that by typing 5 in B2, I will get 05, which is the correct format I need.

If you enter in the formula seen in A3, the leading zeros drop, I need the leading zeros there. What am I do wrong?
  • +
    0 Votes

    Problem:

    Column A (List1) is formatted to display a leading zero before each number. That is, "1" is displayed as "01", "2" as "02", and so on.
    The following formula was created to concatenate the numbers from List1 into one string:
    =A2&A3&A4&A5
    However, an incorrect result of "1234" is returned.
    How can we modify the formula to honor the leading zeros?

    Solution:

    Use the TEXT function as shown in the following formula:
    =TEXT(A2,"00")&TEXT(A3,"00")&TEXT(A4,"00")&TEXT(A5,"00")
    I hope this gives you a clue..

    Please post back if you have anymore problems or questions.

  • +
    0 Votes

    Problem:

    Column A (List1) is formatted to display a leading zero before each number. That is, "1" is displayed as "01", "2" as "02", and so on.
    The following formula was created to concatenate the numbers from List1 into one string:
    =A2&A3&A4&A5
    However, an incorrect result of "1234" is returned.
    How can we modify the formula to honor the leading zeros?

    Solution:

    Use the TEXT function as shown in the following formula:
    =TEXT(A2,"00")&TEXT(A3,"00")&TEXT(A4,"00")&TEXT(A5,"00")
    I hope this gives you a clue..

    Please post back if you have anymore problems or questions.