0

I've an issue with access actually I don't know how I can do that. I have an excel file with numbers from 4 till 12 digits. I need to fill them with 0 till they become 13 digits but 4, 5 and 12 digits should stay like they are.

Exp:
123456 - should be 0000000123456
123456789 - should be 0000123456789
1234 - should stay as 1234

Could someone be so kind and help me in this issue?

2
  • Do you want them to convert to text, or keep as number just change their look? First case you can use Format(x,"000000000000"). Second case you need to change the number format of the field / column containing your data. Jan 30, 2017 at 7:42
  • It will be in text format. I'm really not good in access so I fixed the format in excel. Ordered by Len took the 4,5 and 12 digits out and useing the formula =REPT(0;13-LEN(a1))&a1 after this my old access functions did they job and I got my file I needed. Thanks for your interest and your help.
    – Umut
    Jan 30, 2017 at 8:33

1 Answer 1

0

I have an answer on how you could fix this in excel.
See the image below and I hope this is what is the case for you as well.

sample_showing_formula_and_table

I have taken the sample input from 4 to 12 digits long and used a fomula to pad them with leading zeros as per your need.

The formula is
=IF(OR(LEN(B3)=4,LEN(B3)=5,LEN(B3)=12),B3,CONCATENATE(REPT("0",13-LEN(B3)),B3))

Explanation:

  • The OR condition checks the length of the input number. If the length is 4,5 or 12 it will return 'TRUE1 and the formula will place the same number in the next cell
  • In case if the length of the number is not 4,5 or 12, REPT function will repeat the quantity of zeros required and concatenate will join the zeros along with the original input.

Let me know if you have any specific questions.

6
  • Access is not a spreadsheet application:(, your solution won't work there. Jan 30, 2017 at 7:45
  • @MátéJuhász, The OP said in his question that "I have an excel file with numbers from 4 till 12 digits". I was assuming that he could fix that in excel first and then take it into excel. What do you think?
    – Prasanna
    Jan 30, 2017 at 7:49
  • Thank you so much. Actually the formula gives an error for me but working first on excel after access works well for my issue.
    – Umut
    Jan 30, 2017 at 8:34
  • Can you update your question with the error you get? It works for me without any error
    – Prasanna
    Jan 30, 2017 at 8:35
  • My Office is not in english. Probably the error comes from there.
    – Umut
    Jan 30, 2017 at 8:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .