sorting a column with number & letters

E

excelrookie

Hello All. This is my first post. I have a column that needs to b
sorted. However, each number has a letter in front of it. Can someon
help me sort this example:

B196
B2
B205
B243
B27

I need it sorted by letter then by number smallest to largest.

Thanks al
 
F

Frank Kabel

Hi
I would use two helper columns for this. e.g.
B2:
=LEFT(A2,1)

C2:
=--MID(A2,2,15)
copy down for all rows and use column B + C for sorting
 
T

Thore

I just tried the normal "Sort-Function" (Data-->Sort...)
and it works fine. Just select the entire column and
choose "ascending".
Rgds,
Thore
 
H

hj

Hi,
The problem is that the number portion of your data is
now being treated as text. You need to break the letter
and number portions apart in separate columns, and then do
a two-column sort on the separated parts.
IF the entries are all 1 letter followed by a number,
do this (assuming data begins in A1):
In a new column to hold the letter, enter =LEFT(A1,1)
In a new column to hold the number, enter =VALUE(RIGHT
(A1,LEN(A1)-1))
Then sort all the data on the two new columns. MAKE
SURE you select all the data - not just the columns you're
sorting - otherwise the data records will get scrambled!
If the format of the original data varies from 1 letter
followed by a number, you'll have to adjust the formulas
that break the number apart from the letter.
If you want more explanation, post back. Let us know
how it goes.

Hope this helps.
 
R

Rollin_Again

I would add two helper columns and split the original data so that th
letter appeared in one of the new columns and the number in the other.


The original data in cell A1 appears as *B196*

To Get the letter *B* by itself use the following formula in cell B1
=LEFT(A1,1

To Get the number *196* by itself use the following formula in cell C1
=MID(A1,2,255

Copy the formula down for all rows so that each value has its seperate
letters and numbers appearing in seperate columns (B,C)

Next Select all the data including the new helper columns and clic
*DATA --> SORT.* Choose Column B from the *"Sort By"* dropdown for you
first sort criteria (make sure ascending order radio button is checked
and then choose Column C in the second drop down menu labeled *"The
By"* (also make sure ascending order radio button is checked)


Rolli
 
E

ElsiePOA

Another option:

Insert one helper column and then use the Data, Text to Column
feature. In Text to columns, choose a fixed width option of one.

This will move the number portion of the data to the helper column.

Then you can sort your data, using the helper column as the sor
column.

After sorting you can recombine the columns using A1&B1.

When the data is recombined, use the copy and paste special - value
procedure and you will be back where you started except the data wil
be sorted
 
Top