View original (French)

Excel:Same number of digits in a column

Solved
PierreCompta -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I received an Excel file where I should have numbers composed of exactly 7 digits, no more, no less, in column A. The problem is that some are made up of 4, 5, or 6 digits.

For example:

123456 when it should be 1234560
12340 when it should be 1234000
1234 when it should be 1234000

I need to add 1, 2, or 3 zeros afterward so that these numbers contain 7 digits.

You can imagine that manually, over multiple lines and files, we wouldn't manage!

Can you help me?

Thank you in advance!

Configuration: Windows 7 / Internet Explorer 8.0

4 answers

  1.
    chrismeun Posted messages 91 Registration date   Status Member Last intervention   1
     
    Hello,

    in your original file, do all your numbers have 7 digits?
    0
    1. PierreCompta
       
      No, in the original file, some have 7 digits and others have 6, 5, or 4. I need them all to have 7 digits with zeros at the end.

      Thank you!
      0
    2. PierreCompta
       
      The zeros to add are for those who have less than 7 digits. If I have 1234567, I don’t touch that. Otherwise, for 123456, I add a 0.

      Thank you!
      0
  2. chrismeun Posted messages 91 Registration date   Status Member Last intervention   1
     
    Hello

    123456 =LEN(G5) =IF(H5=6,G5*10,) =LEN(I5)
    12340 =LEN(G6) =IF(H6=5,G6*100,) =LEN(I6)
    1234 =LEN(G7) =IF(H7=4,G7*1000,) =LEN(I7)
    =LEN(G8) =LEN(I8)
    12340 =LEN(G9) =IF(H9=6,G9*10,IF(H9=5,G9*100)) =LEN(I9)
    a little example
    if this can help you

    christian
    0
    1. chrismeun Posted messages 91 Registration date   Status Member Last intervention   1
       
      re
      nb because before result nb because after

      123456 6 1234560 7
      12340 5 1234000 7
      1234 4 1234000 7
      0
      1234 4 1234000 7

      if this is what you're looking for, I'll send you the model attached

      let me know

      christian
      0
    2. PierreCompta
       
      It's good, you guided me! I found it:

      I'm sorting my column from smallest to largest. I'm creating a new column with: =A1*10 and I extend the formula to all the 6-digit numbers, then I replace my results with my 6-digit numbers. I multiply by 100 for the 5-digit number cells and by 1000 for those with 4 digits. After that, I reorder everything!

      Thank you!
      0
    3. chrismeun Posted messages 91 Registration date   Status Member Last intervention   1
       
      re

      I will be absent at the beginning of the afternoon... here is the link if you're interested
      http://www.cijoint.fr/cjlink.php?file=cj201106/cijeG60yt9.xls
      0
  3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    to put it simply:
    if the number always has 7 characters or less than 7
    for cell A1 in B1:
    =A1&REPT(0,7-LEN(A1))
    will always give 7 digits by padding with 0s (result in text format)

    Best regards

    Let's ask ourselves if we are not the only ones who understand what we are explaining?
    0
  4. Ricky38 Posted messages 5776 Registration date   Status Contributor Last intervention   1 463
     
    Hello,

    to absolutely have 7 digits and add 0s if necessary
    select your cells, right-click, format cells, custom and put 0000000 this way you will have
    0123456
    00001234

    --
    Every problem has a solution...you just have to be persistent.
    -1
    1. PierreCompta
       
      I already tried but it doesn't work. Strange. But anyway, it would have put the 0s in front and I need them behind...

      Thank you!
      0
    2. Ricky38 Posted messages 5776 Registration date   Status Contributor Last intervention   1 463
       
      The logic suggests that the 0s should be in front, right?
      If you have 100 in your cell and you add a 0 behind, then the value becomes 1000 and not 100, whereas if you put the 0 in front, the value remains 100.
      0
    3. PierreCompta
       
      These are account numbers, accounting class numbers. For example: account 401 Suppliers or 4010000 Suppliers. It's the same. But for the software in which I need to insert my Excel file, it requires 7 digits, no less, otherwise it won't work.

      Thank you.
      0