Excel Sort only on numerical data

[Mickyg2024]

Good morning,

I am looking for a sort macro that only sorts the numerical data in a cell with the following example string

WF-V-23-0000

I'd like for it to only sort the numerical data from 23-0000 and not the alphabetical data.

thank you

 

[Mickyg2024]

Example

WF-O-23-0001

WF-O-23-0005

WF-V-23-0001

WF-V-23-0003

I'd like for it to look like this

WF-V-23-0001

WF-O-23-0001

WF-V-23-0003

WF-O-23-0005

 

[dreid1011]

Here's this for now, assuming your data is in A1 without headers and the range AA:AB is clear, because I couldn't figure out how to split a string into a 2d array at the moment. So this uses the worksheet to split and sort the values, and then put them back into column A. Hopefully someone else will come by with a solution not needing the worksheet. (I have more learning to do)

Private Sub NumericSort()
Dim str() As String
Dim i As Long, j As Long, lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row
ReDim str(1 To lRow)

For i = 1 To lRow
    Range("AA" & i & ":AB" & i).Value = Split(WorksheetFunction.Substitute(Range("A" & i).Value, "-", "@", 2), "@")
Next i

Range("AA1:AB" & lRow).Sort key1:=Range("AB1"), order1:=xlAscending, Header:=xlNo

For j = 1 To lRow
    str(j) = Range("AA" & j).Value & "-" & Range("AB" & j).Value
Next j

Range("A1:A" & lRow).Value = Application.Transpose(str)
Range("AA1:AB" & lRow).ClearContents

End Sub

 

[SunnyAlv]

Another option with Formula Excel, Try this

=CHOOSECOLS(SORT(HSTACK(LEFT($F$4:$F$7,LEN($F$4:$F$7)-4),RIGHT(F4:F7,4)),2,1),1)
&
CHOOSECOLS(SORT(HSTACK(LEFT($F$4:$F$7,LEN($F$4:$F$7)-4),RIGHT(F4:F7,4)),2,1),2)

 

[felixstraube]

Another option:

=CHOOSECOLS(SORT(HSTACK(A1:A4, VALUE(SUBSTITUTE(RIGHT(A1:A4, 7), "-", ""))), 2, 1, FALSE), 1)

 

[Rick Rothstein]

Give this a try...

=LET(s,SORT(A1:A4,,-1),SORT(HSTACK(LEFT(s,5),RIGHT(s,7)),2))