Does anyone know a way to split 12 digit number into separate digits starting from 0 using Excel.

Example :012345678901 is split as 0,1,2,3,4,5,6,...

I've use this formula =MID($A1,column()-1,1) but it is unable to read the 1st digit 0. It keeps appearing as 1,2,3,4,5,... instead of 0,1,2,3,4,5,... and also if there is a number starting with 00.

Is there any formula that its able to read 0 or 00 as the first number followed by the rest?

  • 1
    Is the leading zero showing in the original cell? It sounds like Excel is treating it as a number instead of text. As a number, there's no leading zero. You can guarantee it's treated as text by starting the entry with an apostrophe. – fixer1234 Jan 12 '16 at 2:20
  • Excel numbers don't show leading zeros. If you want leading zeros, you need text not numbers. – teylyn Jan 12 '16 at 2:21

Most likely your number is being treated as a number instead of text. My guess that you have that column formatted as text. Try using the following to force Excel to treat that text as text instead of a number:

=MID($A1 & "", COLUMN() - 1, 1)

The only change I made was to add the & "". This concatenates an empty string to the value in A1 and that forces Excel to treat it as text instead of a number.

  • This won't help if Excel handled the original input as a number. The leading zeros will already be gone and converting back to text won't restore them. – fixer1234 Jan 12 '16 at 2:41
  • 1
    Indeed. But judging by the question, he already has them set up and displaying the leading zeroes. That's why I assume he has that column formatted as text. – Jason Clement Jan 12 '16 at 2:50
  • 1
    If I enter the number with the leading apostrophe, Excel treats it as text, but if I enter it into a column formatted as text, Excel keeps the leading zero, but the formula treats it as a number. – Jason Clement Jan 12 '16 at 2:51
  • I use LO Calc, and with that, the string functions work correctly if the cell was formatted as text. So I assumed the same behavior in Excel. I just cranked up an old Excel 2007, and the string functions work correctly there, also. So I'm not able to replicate Excel treating a text entry as a number. – fixer1234 Jan 12 '16 at 3:17

The formula works only for text values, as explained in the comments above.

A1 has text with a leading zero, A2 has a number and Excel does not show leading zeros for numbers.

enter image description here


Sometimes leading zeros are there only as a result of formatting, for example:

enter image description here

This can explain the mismatch between the value in the cell and the value in the Formula Bar.

You can convert this into a twelve character string by using this short User Defined Function:

Public Function txet(r As Range) As String
   txet = r.Text
End Function

It is used like:

enter image description here

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.