Removing all non-numbers from the last portion of string

I have multiple filenames, for example, XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053XMK and YYYY_AA_CJDJD_XY_20180510_134455XMKUY. How do I remove "XMK" and "XMKUY" from the two strings so that I am left with XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053 and YYYY_AA_CJDJD_XY_20180510_134455 only? There are multiple variations from these but basically I just to remove all letters from the last portion of the string.

first copy your files (not all, 5-10 files maybe) to another location and test the following code with these files.

below code uses the following UDF to extract the numbers from a text string.

copy both to a standard code module.

PHP Code:

---

Function NumsOnly(ByVal InputString As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^\d]+"
        NumsOnly = .Replace(InputString, vbNullString)
    End With
End Function 


---

Code:

---

Sub vbax_62932_remove_alfa_from_last_bit_of_string()
   
    Dim fPath As String, old_bit As String, new_bit As String
    Dim fFiles, fnBits
    Dim j As Long
   
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
   
    fPath = "C:\my_folder\my_subfolder\" 'change full file path to suit
    fFiles = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & fPath & "*.xlsm"" /b /o:n").StdOut.ReadAll, vbCrLf)
           
    For j = LBound(fFiles) To UBound(fFiles) - 1
        fnBits = Split(fFiles(j), "_")
        old_bit = fnBits(UBound(fnBits))
        new_bit = NumsOnly(old_bit)
   
        Name fPath & fFiles(j) As fPath & Replace(fFiles(j), old_bit, new_bit)
    Next j
   
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

---

Quote:

---

Originally Posted by swaggerbox

I have multiple filenames, for example, XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053XMK and YYYY_AA_CJDJD_XY_20180510_134455XMKUY. How do I remove "XMK" and "XMKUY" from the two strings so that I am left with XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053 and YYYY_AA_CJDJD_XY_20180510_134455 only? There are multiple variations from these but basically I just to remove all letters from the last portion of the string.

---

Do you want to rename the files from XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053XMK.??? to XXXX_BB_CCCCCCCC_XYXYXY_20180507_123053.???

or just reformat the string of a filename?