sorting text and numerical data
Posted by Kiely hall on November 17, 2001 8:30 PM
I have an Excell spreadsheet that I would like to sort based on item numbers. All of the item numbers begin with the letter 'B' and begin with the number '100' they go up to about '2300'. Some of the item numbers are followed by a letter or multiple letters such as a,b,c or A & B. When I tell Excell to sort by the item number it sorts all of the itemnumbers just fine, but it sorts the item numbers without the numbers at the end and then after that sorts the item numbers with the letters at the end. I would basically like it to ignore the letters and just sort by the number. The Problem is that the letters must be in that column and none of the columns can be changed. Please help me if you can I'm really in a bind.
Thanks Kiely,
P.S. I am using excell 2000.
Posted by Bob Umlas on November 18, 2001 5:59 AM
Well, you can't sort this DIRECTLY, so you have to make SOME changes, albeit temporary. Assuming your data to be sorted begins in A1 and the values are like B1300ab,B255a,B750,B1022abc, etc, then this will work. In a parallel column, array enter (Ctrl/shift/enter) this formula:
=1*LEFT(MID(A1,2,100),MATCH(TRUE,ISERROR(1*MID(MID(A1,2,100),ROW($1:$20),1)),0)-1)
Then fill down, then sort on this new column, then clear the column.
HTH
