LinkBack URL
About LinkBacks
How do I sort rows according to a logical numerical sequence with a letter preceding the number?
I want to achieve this sequence of rows:
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11
P12
Instead Excel sorts like this:
P1
P10
P11
P12
P2
P3
P4
P5
P6
P7
P8
P9
How do I sort rows according to a logical numerical sequence with a letter preceding the number?
I want to achieve this sequence of rows:
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11
P12
Instead Excel sorts like this:
P1
P10
P11
P12
P2
P3
P4
P5
P6
P7
P8
P9
Can you make the entires P01, P02 P11 etc?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Heyoka
Welcome to the Forum!!
You could create a Helper Column that strips the "P" from the Cell Values and Sort on the Helper Column or, alternately (depending on Volume), create a Custom Sort.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
="P"&TEXT(RIGHT(F8,LEN(F8)-1),"00") and drag down.
After that you can sort on that column.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Welcome to the board.
You could use a UDF to pad the numbers to equal length, then sort on that column:
Row\Col P1 P01 B1: =PadNum(A1, 2) P10 P10 P11 P11 P12 P12 P2 P02 P3 P03 P4 P04 P5 P05 P6 P06 P7 P07 P8 P08 P9 P09
Function PadNum(sInp As String, Optional ByVal iLen As Long = 1) As String ' shg 2003-1115 ' Expands numbers in a string to iLen characters for sorting; e.g., ' PadNum("13A1U3", 2) = "13A01U03" ' PadNum("1.2.3.15", 3) = "001.002.003.015" ' Numbers are not shortened below their minimal representation: ' PadNum("1.123.2.3", 2) = "01.123.02.03" ' Returns unpadded values if iLen = 1 or omitted ' PadNum("01.123.02.03") = "1.123.2.3" ' All non-numeric characters are returned as-is Dim sFmt As String Dim iChr As Long Dim sNum As String Dim sChr As String Dim bNum As Boolean sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0") For iChr = 1 To Len(sInp) + 1 ' the +1 flushes a trailing number sChr = Mid(sInp, iChr, 1) If sChr Like "#" Then bNum = True sNum = sNum & sChr Else If bNum Then bNum = False PadNum = PadNum & Format(CDbl(sNum), sFmt) sNum = vbNullString End If PadNum = PadNum & sChr End If Next iChr End Function
Entia non sunt multiplicanda sine necessitate
@shg
I like it...it's in my Tool Box...with proper attribution...
Glad you like it, John. I use it frequently.Originally Posted by jaslake
I am really liking this function also but have a followup question. It seems that when you have the same letters preceeding the number (but a different number scheme), it is not sorting like you would expect. BSD48xxx and BSD62XXX should come before BSD175XX:
BSD13-8.1
BSD13-13
BSD13-14
BSD175-1
BSD175-4
BSD48-1
BSD48-2
BSD48-8
BSD62-1
BSD62-1.1
Hank
Col A is list
In column B cell 1 enter =Value(Substitute(A1,"P","")) copy down to match list.
Sort both columns on col B Ascending.
Delete / clear col B if of no further use.
You need to pad for the largest number:
Row\Col BSD13-8.1 BSD013-008.001 B2: =PadNum(A2, 3) BSD13-13 BSD013-013 BSD13-14 BSD013-014 BSD48-1 BSD048-001 BSD48-2 BSD048-002 BSD48-8 BSD048-008 BSD62-1 BSD062-001 BSD62-1.1 BSD062-001.001 BSD175-1 BSD175-001 BSD175-4 BSD175-004
ah ha, I knew you would know the answer!! Thx.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks