• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting numbers and letters cells

B

bines53

Active Member
Hello ,

I have one line of numbers and letters I want to sort by Alphabet another line,
For example :5,2,C,A,U ,9
Sort by alphabet, meaning that order :2,5,9,A,C,U

Thank you !
 
Hi ,

When you say one line of numbers and letters , do you mean that all of it is in one cell ?

When posting data , please post it with reference to Excel cell / column addresses.

Narayan
 
Hi ,

The easiest manual way is to copy them and then do a Paste Special Transpose in another area of the worksheet , do an individual column sort , and then copy paste back to the original area.

Narayan
 
Hi ,

The "formula" that you have posted is not actually a formula as we know it in Excel ; it may be a UDF , which has a fair amount of code behind it.

The point is whether your requirement is repetitive ? If so , it may be worth developing the code to do it. If not , it is not worth investing the time and effort required.

Narayan
 
Hi bines53,

Try this, select your data A1:F1

Go to Data Tab > Sort > Options, select Left to Right > OK > OK
 
Hi bines53,

Considering your data {5;2;C;A;U;9} in A1:A6. Use below formula in B1 and copy till B6 which will be used as helper column.
=SUM(IF($A$1:$A$6<$A1,1,0))+COUNTIF($A$1:$A$6,$A1)

Note this is an array formula, so must execute with Ctrl+Shift+Enter.

Next in column C in C1 use below formula to get sorted list. Copy till C6.
=INDEX($A$1:$A$8,MATCH(ROWS($C$1:C1),$B$1:$B$8,0))

Execute with Ctrl+Enter. (Normal Formula)

Note this formula will not work with blanks & duplicates.

Regards,
 
You must log in or register to reply here.
Back
Top