Sorting data of various length's, numbers, letters and dash's

[Nicole]

Hello!

I am trying to sort a large sheet by number, in numerical order. The problem
that I've run into is that some of my records are short with numbers only,
some are very long with dash's, some have letters amongst them, etc.

Is there someway to sort as I want it to?

Thanks in advance!

 

[Luke M]

NOTE: Following method converts values to text, and can not be used in
mathematical functions.

Assuming data is in column A, in another column, input
=TEXT(A1,"#")
and copy down.
Copy this entire set of formula, right click on A1, paste special ->Values
You should not be able to sort the way you want, as XL will now treat
everything as text.

 

[Glenn]

Hello!

I am trying to sort a large sheet by number, in numerical order. The problem
that I've run into is that some of my records are short with numbers only,
some are very long with dash's, some have letters amongst them, etc.

Is there someway to sort as I want it to?

Thanks in advance!

Give specific examples of your data and how you want it sorted.

 

[Shane Devenshire]

Hi,

Numbers stored as text via the TEXT function can be used in some
calculations.

for example
=B1*5
will work if B1 is a text number.
=SUMPRODUCT(--B1:B200)
will work if B1:B200 are text numbers or number or a mix.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Please provide some sample data.

Cheers,
Shane Devenshire