up vote 0 down vote favorite
share [g+] share [fb]

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?

link|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
feedback

2 Answers

up vote 2 down vote

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.

link|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
feedback
up vote 2 down vote

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.

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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