# excel part number sort

How do I get excel to sort part numbers so I can find my part number grouped together well.
I need all the part numbers with 1 to show together even if its 100, 1234,1000, or 10000?

For example:
100
200
1234
1000
2000
10000
20000
200000
100000
###### Who is Participating?

Commented:
I meant

B1:  =TEXT(A1, "@")
...

But Stephen's LEFT does the same
0

Commented:
I those are "part numbers" (text), you should be storing them as text, by adding ' in front of them
Then they will sort as text (1's together).
For now, you can add a new column, and type this formula (If first is A1, and new column is B)
B1:  =TEXT(A1)
Copy down
Then select all your data, and sort by the new column
When Excel prompts (Sort Warning), choose the lower one (sort numbers and text separately)
0

Commented:
You could use =LEFT(A1,1) in the next column and sort by that one.
0

Commented:
You can also use the attached macro. Just select the range which you want to sort and then run this macro. You can also assign a shortcut key to it.

Regards
Shashank
``````Sub test()
Dim rng As Range, rng2 As Range
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
For Each rng2 In rng
rng2.Value = "x" & rng2.Value
Next rng2
ActiveSheet.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange rng
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For Each rng2 In rng
rng2.Value = Right(rng2.Value, Len(rng2.Value) - 1)
Next rng2
End Sub
``````
0

Author Commented:
This worked great and was easy to use.

Thank-you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.