I'm running Excel 2010 using a output file from our database software that exports a list of our affiliate stores. The affiliate stores have a 9 digit code and a number of these codes start with "0".

When this column is converted from text to numbers, Excel removes the initial digit if it starts with "0" and the result is that the 9 digit number becomes an 8 digit number. Is there a way to prepend a "0" to a number in a cell?

The ultimate goal is to run a script on these codes as the first 4 digits represent the region where the affiliate store is.

  • 1
    It's bad design... You should seperate esch value into one column. – davidbaumann Apr 11 '14 at 1:36
  • Does this column need to be converted from text to numbers? – Garrulinae Apr 11 '14 at 1:42
  • Please select an answer as solution. – davidbaumann Dec 7 '15 at 19:44

2 ways of looking at this:

1) You simply want to see the number displaying 9 digits. In this case:

Go to Format Cells > Custom. Under Type, input "000000000" where there used to be "General" to set minimum number of digits displayed = 9

2) You need to verify with the first 4 digits:

To convert the number to a string, use the TEXT() Function:

    Value = 2

TEXT(Value, "00") = 02

To completely solve your question, simply obtain the first four digits by

   =(LEFT(TEXT(Value, "000000000"),4)

Good luck.


To get your region, just usw this formula: =left(A1;length(a1)-6)

You can also import this field as text.

In fact, this way it is no good db design.


For the scenario you have two ways first one as below

=Text(A1,"000000000") use this formula keep total digit ( example 9)

second one Press Control+1 select custom in the list - you can see the right side list custom make it as many digit you need

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.