Good Morning All,
I was wondering if there was a way to get excel to sort complex numbers correctly.
When sorting numbers like this volume 4 should be after volume 3 but ends up coming after volume 39.
LNBV-97-1135 vol. 4
LNBV-97-1135 vol. 40
Anyone know a way to get excel to sort numbers like this? I have a sheet with over 60,000 entries on it like this and sorting it is becoming a real pain.
Thanks in advance for any help
are they all the same format? ie are all of the 60,000 entries in the format of 18 chars before the volume number?
if so then in a free column (Ill assume B and your number is in A) put
this will make the numbers of 0-9 become 2 digits and therefore sortable.
Please Login or Register to view this content.
ie LNBV-97-1135 vol. 4 will become LNBV-97-1135 vol. 04
Drop the formula down, copy the column then paste special Values over the original column.
If someone has helped you then please add to their Reputation
Create a new column with this formula
=MID(A1,FIND("vol.",A1)+4,LEN(A1)-FIND("vol.",A1)+4)+0
and sort on that column
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
You could always create a new column and use this formula to extract all numbers after to . then sort on that column
Oh gosh, I must be a slow typer!Please Login or Register to view this content.
Blake 7
If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.
http://failblog.org/
Wow, Cheers to all of you guys! an incredible response in such a short time. Rep for all!
Originally Posted by Special-K
if you sort just on the numbers though you'll get totally different answers egYou could always create a new column and use this formula to extract all numbers after to . then sort on that column
Oh gosh, I must be a slow typer!Please Login or Register to view this content.
LNBV-97-1136 vol. 4 will be before LNBV-97-1135 vol. 5
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks