# How to split a number with leading zeroes into separate digits in Excel

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?

• 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
• 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
• 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. Sometimes leading zeros are there only as a result of formatting, for example: 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: 