Sorting a Number with dash

[B_KRANTHI_KUMAR]

Hi,

Please help me how to sort a number with dash.

example

201-1
201-10
201-11
201-12
201-13
201-14
201-2
201-3
201-4
201-5
201-6
201-7
201-8
201-9

[#2 · Accepted solution]

@B_KRANTHI_KUMAR

Let's say the data start in A1.

Make sure that columns B and C are empty (insert columns if necessary).

Select the data.

On the Data tab of the ribbon, in the Data Tools Group, click Text to Columns.

Select Delimited, then click Next >.

Tick the check box for Other, and enter a dash (hyphen) in the box next to it.

Click Next >.

Enter or select B1 in the Destination box (to avoid overwriting the data.

Click Finish.

Select cell A1, or the entire range.

In the Sort & Filter group of the Data tab of the ribbon, click Sort (not the Sort A to Z  or Sort Z to A buttons).

Select Column B in the Sort By dropdown, then add a new level and select Column C.

Click Sort.

If you wish, you can now clear columns B and C.

[#3]

Problem Solved.
Thank you so much.

[#4]

@Hans Vogelaar 

I am looking for an easier way to do this with a spreadsheet that will be added to continually, our part number listing.

sample part numbers:

1973-1

2433

3505-101

3505-101M077

I was hoping for a custom format but do not see an existing one and have been unable to create one out that works.

Thanks,

John

[#5]

@johndzamba 

Your "part numbers" are text values. Custom number formats are - as the name implies - for numbers (and dates).

What exactly would you like to do?

[#6]

@Hans Vogelaar 

Yes sir they are. That was an attempt at making excel show them as entered.
This list is from our existing part number (or letter or whatever) database.
Most of our assigned part numers are just 4 numberical digits but we occasionally have to enter dash numbers to reflect a slightly confiuration of the same part and sometimes our customer adds an alphabetic character to denote special attributes such as "not machine insp required".
Other columns show Customer name and Customer part number.  When adding parts I look at the part numbers assigned to a specific customer and use that series then try to resort them by part number which is the way most folks here search the database.

thanks,

John

[#7]

@johndzamba 

The easiest solution would be to let the users enter the four-digit numeric code in one column, and any additional characters in the next column. The data will then be very easy to sort.

[#8]

could you show your raw data and expected result?
I am not sure what exactly would you like to do?

[#9]

@Hans Vogelaar 

Thank you found that solution but hoping to keep part numbers intact for this.

John

[#10]

@peiyezhu 

Here is a portion of the table, as I said I would like to be able to sort by any column and still sort by the part number column and see the data in the order it is in as attached.

Regards,

John

[#11]

sort formula works on my side if you need convert number to text by c39="" & A39 before sort if u want sort by text