0

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?

1
  • 1
    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, 2011 at 9:09

2 Answers 2

2

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.

2
  • 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, 2011 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, 2011 at 12:05
2

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.

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

You must log in to answer this question.

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