Excel - Sort Part Nmubers 2

[jwillard]

I need to sort Part numbers with mixed numbers and text.
Any help would be greatly appreciated.

Currently the data sorts as follows
200
203
205
207
310
315
330
336
340
345
350
225-2T
250-2T
260-2T
270-2T
275-2T
300-2T
305-2T
306-2T
310-2T

Needed Result:

200
203
205
207
225-2T
250-2T
260-2T
270-2T
275-2T
300-2T
305-2T
306-2T
310
310-2T
315
330
336
340
345
350


Boycott Sony

 

[hext2003]

Have you tried putting them in two separate cells?

A column has the number part
B column has the -2T

 

[AnotherHiggins]

The problem is that the purely-numeric entries (like 200, 203) are stored as numbers. The alphanumeric entries are stored as text. numbers always sort before text (999 is before A).

You need to convert the entire column to TEXT.

Two methods:
___

1) Brute Force
In an adjacent column, use this formula:
[tab][COLOR=blue white]=Text(A1,"@")[/color]
Copy that formula down as far as needed.

You can Copy 'n' Paste Special > Values to replace the formulas with actual text strings, then delete the original column.

2) Text-To-Columns
Highlight the column that is giving you trouble. Go to Data > Text To Column. Choose Delimited or Fixed Width, press Next. Make sure there are no lines breaking up the data, press Next. Select Text, press Finish.
___

Either way you go, you can now sort as you want.

In the future, you can force an entry like 200 to be stored as text by entering it as '200. The apostrophe will not be displayed; it just tells Excel to store the entry as text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[pbrodsky]

Or make a new calculated column -VAL(oldcolumn) and sort on that.

 

[jwillard]

Thats a good idea but that leads me too, how do I pull everything from the dash over to another column.

Excuse my ignorance but im kind of a newby to formulas.

Boycott Sony

 

[AnotherHiggins]

pbrodsky,

Two problems with that:

1) VAL is not a native excel function. Maybe you mean Value?

and

2) Value would do the opposite of what is needed - it forces numeric entries stored as text back to numbers. Also, it errors out when trying to convert alphanumeric entries like 225-2T.

jwillard: if these are just part numbers, they really should be stored as text anyway, since you won't ever do arithmetic with them.

Also: to whom are you asking your question, jwillard? What have you tried so far?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[pbrodsky]

something like this works

=IF(ISERROR(FIND("-",D7)),D7,VALUE(LEFT(D7,FIND("-",D7)-1)))

 

[jwillard]

Thank you AnotherHiggins. Your option 1 worked great.

Appreciated the help from everyone.

Boycott Sony

 

[AnotherHiggins]

Glad you got it sorted!


And I really think it is best that you are keeping your part numbers stored in a single cell since they are unique identifiers for your data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jwillard]

AnotherHiggins,

If you have time to answer, I am curious....

I just used the formula that you gave me again - =Text(A1,"@") - and I was wondering how does the @ sign do this. I see no reference to the @ sign in the Excel help files. Also what exactly is the @ sign doing?
Is it just inserting a ' in the beginning of each cell or is there more to it?

Thanks, Jeff

Boycott Sony

 

[AnotherHiggins]

The @ just tells Excel to use the TEXT format.

You can see what formats to use in the quotes by manually formatting a cell, then going to Format > Cells > Number > Custom and observing what is in the Type box to the left.

You will see that:
- for General formatting, the Type box says General
- for Number formatting w/o decimals, the Type box says 0
- for Number formatting w/ 2decimals, the Type box says 0.00
- for Text formatting, the Type box says @
Etcetera.

But to tell you the truth, it really doesn't matter so much whether you put @, General or 0 in the quotes, because using the TEXT formula returns a text string no matter what format you put in quotes. And the important thing is that you are forcing all entries to be text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Welshbird]

anothierkiggins: I'll find this '@' useful too!

Hava spare star!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[AnotherHiggins]

You're too kind, Fee. Thanks!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.