May 04 2021 03:21 AM
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 |
May 04 2021 03:49 AM
SolutionLet'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.
Feb 02 2023 09:17 AM
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
Feb 02 2023 11:24 AM
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?
Feb 06 2023 09:59 AM
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
Feb 06 2023 11:37 AM
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.
Feb 06 2023 04:53 PM
Feb 07 2023 05:02 AM
Feb 07 2023 05:05 AM
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
Feb 07 2023 03:59 PM - edited Feb 07 2023 04:06 PM
sort formula works on my side if you need convert number to text by c39="" & A39 before sort if u want sort by text
May 04 2021 03:49 AM
SolutionLet'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.