Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

2 Answers 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.

share|improve this answer
    
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.

share|improve this answer
    
=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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.