Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-04-2020, 10:29 AM
Karen615 Karen615 is offline Aplha Numeric Sorting Windows 7 64bit Aplha Numeric Sorting Office 2010 64bit
Competent Performer
Aplha Numeric Sorting
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default Aplha Numeric Sorting

Can Excel sort alpha numeric characters?




I attached a spreadsheet that explains how I attempted to sort the cells and how I want it sorted.


Any help would be greatly appreciated.



Thank you,
Karen
Attached Files
File Type: xlsx AlphaNumericSort.xlsx (9.0 KB, 22 views)
Reply With Quote
  #2  
Old 06-05-2020, 04:27 AM
ArviLaanemets ArviLaanemets is offline Aplha Numeric Sorting Windows 8 Aplha Numeric Sorting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

You must have the column with alphanumeric values formatted as text. Then it'll sort exactly as you wanted.

Simply changing column format is not enough! When you format the column as text, it will behawe as all entries are texts, but really they remain numbers - you have to edit all entries to convert them really (select entry, press F2, Press Enter for every entry).

To convert all entries at one go:
Into some empty column enter formula like ="" & A2, and copy it down for all entries in column A;
Format column A as Text;
Copy the range with formulas;
Replace old entries in column A with values of copied formulas (PasteSpecial.Values);
Delete column with formulas.
Reply With Quote
  #3  
Old 06-14-2020, 09:51 AM
Karen615 Karen615 is offline Aplha Numeric Sorting Windows 7 64bit Aplha Numeric Sorting Office 2010 64bit
Competent Performer
Aplha Numeric Sorting
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

Thank you so much for your help.

Now if this data is being used for a pivot table, should I sort the raw data first or should it be sorted in the pivot table?


Your help is greatly appreciated.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Aplha Numeric Sorting Numeric Values jrt Word VBA 2 11-09-2014 01:28 PM
Aplha Numeric Sorting Use the dot key on the numeric keypad YooNaa Kim Word 2 09-09-2013 08:25 AM
Aplha Numeric Sorting Sorting Alphanumeric & Numeric values slovenc0417 Excel 2 06-07-2013 07:58 PM
Aplha Numeric Sorting Application.OnKey Numeric Plus? tinfanide Excel Programming 6 11-26-2012 08:41 PM
Aplha Numeric Sorting How to do an alpha numeric Sort By? Opul3nce Excel 1 10-15-2012 11:11 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft