# Sorting numbers in Excel 2003 between B1234 and B12345 by inserting a leading zero

I have two series of numbers in the format `B1234` and `B12345`. When I sort in ascending order, I get all the numbers starting with `B12345` and then all of those from `B1234` on.

To counteract this problem, I inserted zero in between `B1234` to get `B01234`. When the numbers are converted to this format, I am able to get the desired result.

Can you help me inserting zeros?

-
Not an answer to your current problem, but in future you might consider splitting the prefix out into a separate cell from the numbers, using a custom format for the numbers, e.g "00000" and then using "concatenate" to rejoin the letters to the numbers. – Tog Nov 28 '11 at 9:09

Make a second column, and if your numbers are in column A, put this in B1

``````=IF(LEN(RIGHT(A1,LEN(A1)-1))< 5,LEFT(A1) & "0" & RIGHT(A1,LEN(A1)-1))
``````

and copy it all the way down. Once you have the numbers with the leading zeros, copy them all back into column A and delete B.

This assumes that your numbers start from a 4 digit number and not from single digits.

-
Thanks you very much. Could you help me out if there is a condition where it is less than 4 digit number. Should increase the number of zero in the formula or edit the formula. – Pradeep Nov 28 '11 at 11:20
@Pradeep Part of the problem is that your "number" is really a string. I think if you separated the "B" from the other digits, you could readily sort them without the extra steps. Otherwise, you'll have to check the number of digits and pad that many zeros (this is probably possible with a formula, but might require VBA if it gets any fancier). – jonsca Nov 28 '11 at 12:05

The following formula should work for any number of digits (up to five) after the initial letter. (B1, B12, B123, B1234, B12345)

`=LEFT(A1,1)&TEXT(MID(A1,2,99),"00000")`

Copy this into a new column and reference your column starting in A1. If not in A1, change both references before copying.

-
`=LEFT(A1,1)&TEXT(RIGHT(A1,LEN(A1)-1),"00000")` – wilson Nov 29 '11 at 3:30
@ Simon, this info is very useful. Thanks a lot. – Pradeep Nov 30 '11 at 5:19