Separating letters and numbers from same column?

M

Manfred

I have data that unfortunately arrives with letters and numbers unparced as
follows (the 3 letters and numbers are in the same column) :

ACG .938
ADF .84
ADX -.589
AFB .428
AKP .299
ALM .899
AMF .559
AMO -.701
AMU .905
AOF .927
APB .595
APF .162
APX .91
ARK .855
ASA .853
ASG -.302
ASP .969


Alas, this column is over six hundred rows long, and I need to sort it from
the highest to lowest number if possible. I've tried using the 'sort'
function to no avail. Is there a way to get Excel to distinguish the
letters and numbers, and to sort the letters on the basis of the
accompanying numbers?

Any help with this would be greatly appreciated.
 
N

Niek Otten

If your data is in column A, put this formula in column B and fill down as
far as needed:

=RIGHT(A1,LEN(A1)-4)

This gives you

0.938
0.84
-0.589
0.428
0.299
0.899
0.559
-0.701
0.905
0.927
0.595
0.162
0.91
0.855
0.853
-0.302
0.969


Copy this data, and Paste Special Values in the same place. Now sort column
A and B and specify B as the sort key.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
G

Gord Dibben

Manfred

Copy the column to Column B if you wish to retain original data.

Select column B and Data>Text to Columns>Delimited by "space">Finish

You will get one blank column and one with the letters and one with the
numbers.

Skip the import of the blank column.

Sort all columns by Column C(numbers)

Gord Dibben Excel MVP
 
M

Manfred

Niek,

Thank you for replying to my post.

--M


Niek Otten said:
If your data is in column A, put this formula in column B and fill down as
far as needed:

=RIGHT(A1,LEN(A1)-4)

This gives you

0.938
0.84
-0.589
0.428
0.299
0.899
0.559
-0.701
0.905
0.927
0.595
0.162
0.91
0.855
0.853
-0.302
0.969


Copy this data, and Paste Special Values in the same place. Now sort
column A and B and specify B as the sort key.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
Click to expand...
 
Top