0

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?

Improve this question
2
  • 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, 2016 at 2:20
  • Excel numbers don't show leading zeros. If you want leading zeros, you need text not numbers.
    – teylyn
    Jan 12, 2016 at 2:21

3 Answers 3

Reset to default
2

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.

Improve this answer
4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 3:17
1

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

Improve this answer
0

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

Improve this answer

You must log in to answer this question.

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