Sort columns and cells by Name in one cell, then Extension in another

nsnyder2112

New Member
Joined
Jan 21, 2022
Messages
3
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
I am working with a system that sets up a list by creating a description then by filename with extensions. However, there is no continuity in the filename with extension sorting:

What I get:

ThisIsFile01.jpg - File01
ThisIsFile01.cab - File01
ThisIsFile02.cab - File02
ThisIsFile02.jpg - File02
ThisIsFile03.jpg - File03
ThisIsFile03.cab - File03

You can see the extensions follow no order. Yes, the descriptions are listed second but whoever created the program sorted this way first, which is fine as it helps.

This is what I would like to be able to create:

ThisIsFile01.cab - File01
ThisIsFile01.jpg - File01
ThisIsFile02.cab - File02
ThisIsFile02.jpg - File02
ThisIsFile03.cab - File03
ThisIsFile03.jpg - File03

Is there a way to set up a formula or sort process to make this happen? I have tried some of the tips and tricks with the RIGHT and LEFT but have been unsuccessful so far. Is this a VB Script thing?

Any help would be great.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi, providing the separator is always " - " , you can use the following macro, then just click any data cell in the column & run the macro.

VBA Code:
Sub SortFileNames()
Dim rng As Range, rng1 As Range
Dim arr() As Variant, arr1() As Variant
Dim h As Long, LR As Long, AC As Long

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    LR = FindLastRow(ActiveSheet.Cells)
    AC = ActiveCell.Column
   
    Set rng = Range(Cells(2, AC), Cells(LR, AC))
    Set rng1 = Range(Cells(2, AC), Cells(LR, AC + 2))
   
   
    arr = rng.Value
    arr1 = rng1.Value

    For h = LBound(arr1) To UBound(arr1)
        arr1(h, 2) = Left(arr1(h, 1), InStr(arr1(h, 1), " -") - 1)
        arr1(h, 3) = Right(arr1(h, 1), Len(arr1(h, 1)) - InStr(arr1(h, 1), " -") - 2)
    Next h
   
    arr1 = Application.WorksheetFunction.Sort(arr1, 3, 1)
    arr1 = Application.WorksheetFunction.Sort(arr1, 2, 1)
   
    For h = LBound(arr1) To UBound(arr1)
        arr(h, 1) = arr1(h, 1)
    Next h
   
    rng.Value = arr
   
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Sub

Function FindLastRow(rg As Range) As Long
   
    On Error GoTo eh
   
    FindLastRow = rg.Find("*", , LookAt:=xlPart, LookIn:=xlFormulas _
            , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Exit Function
eh:
   If Err.Number = 91 Then
        MsgBox "No data found for range [" & rg.Address & "]. Last row will be set to first row of range."
    End If
    FindLastRow = rg.Cells(1, 1).row
End Function
 
Upvote 0
Hi, providing the separator is always " - " , you can use the following macro, then just click any data cell in the column & run the macro.

VBA Code:
Sub SortFileNames()
Dim rng As Range, rng1 As Range
Dim arr() As Variant, arr1() As Variant
Dim h As Long, LR As Long, AC As Long

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    LR = FindLastRow(ActiveSheet.Cells)
    AC = ActiveCell.Column
  
    Set rng = Range(Cells(2, AC), Cells(LR, AC))
    Set rng1 = Range(Cells(2, AC), Cells(LR, AC + 2))
  
  
    arr = rng.Value
    arr1 = rng1.Value

    For h = LBound(arr1) To UBound(arr1)
        arr1(h, 2) = Left(arr1(h, 1), InStr(arr1(h, 1), " -") - 1)
        arr1(h, 3) = Right(arr1(h, 1), Len(arr1(h, 1)) - InStr(arr1(h, 1), " -") - 2)
    Next h
  
    arr1 = Application.WorksheetFunction.Sort(arr1, 3, 1)
    arr1 = Application.WorksheetFunction.Sort(arr1, 2, 1)
  
    For h = LBound(arr1) To UBound(arr1)
        arr(h, 1) = arr1(h, 1)
    Next h
  
    rng.Value = arr
  
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Sub

Function FindLastRow(rg As Range) As Long
  
    On Error GoTo eh
  
    FindLastRow = rg.Find("*", , LookAt:=xlPart, LookIn:=xlFormulas _
            , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Exit Function
eh:
   If Err.Number = 91 Then
        MsgBox "No data found for range [" & rg.Address & "]. Last row will be set to first row of range."
    End If
    FindLastRow = rg.Cells(1, 1).row
End Function
JW00, I appreciate the response. However, after reading the interest of the " - " and the as long as the items are sorted with that, I have determined that I have poorly described what I have versus what I need. I would be grateful if you could determine your script versus the following description:

A B
1 ThisIsFile01.jpg File01
2 ThisIsFile01.cab File01
3 ThisIsFile02.cab File02
4 ThisIsFile02.jpg File02
5 ThisIsFile03.jpg File03
6 ThisIsFile03.cab File03

Please try and imagine the separated cell blocks above on the worksheet. They would be turned into the following:

A B
1 ThisIsFile01.cab File01
2 ThisIsFile01.jpg File01
3 ThisIsFile02.cab File02
4 ThisIsFile02.jpg File02
5 ThisIsFile03.cab File03
6 ThisIsFile03.jpg File03

The beginning of the sort would be by Column B and the filename descriptions. The second sorting would be by actual filename in Column A with the extensions promoting a secondary alphanumeric consideration. I hope this makes more sense - rather than using a specific character as a separator it's actually three separate sorts over the columns. Column B is by filename description; Column A is by extension then by filename.

Again, I am grateful for any help you can provide and I realize that I may need to better understand what I need before I post. Regards.
 
Upvote 0
Hi & welcome to MrExcel.
You could use a formula in col C & then sort on col B, C & A
+Fluff 1.xlsm
ABC
1ABC
2ThisIsFile01.cabFile01cab
3ThisIsFile01.jpgFile01jpg
4ThisIsFile02.cabFile02cab
5ThisIsFile02.jpgFile02jpg
6ThisIsFile03.cabFile03cab
7ThisIsFile03.jpgFile03jpg
Data
Cell Formulas
RangeFormula
C2:C7C2=MID(A2,FIND(".",A2)+1,100)
 
Upvote 0
Solution
Fluff, your slightly more simplistic formula worked perfect. It does add a few steps but they are easy and not terribly time-consuming. I'm grateful.

A last thanks to JW00, as well, for a possible fix. I have not yet tried it but I have several projects that may be helped by the script.
 
Upvote 0
Glad your problem is sorted, regarding you misleading me, I think I mislead myself as much as you mislead me.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top