Removing all non-numbers from the last portion of string

[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.

[mancubus]

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.



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



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

[Paul_Hossler]

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?