Find the nth occurrence of a string and report value to the left.
Find the nth occurrence of a string and report value to the left.
(OP)
I have a file name in this format.
Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls
I am trying to find the 4th occurrence of the "_" (Under score) and obtain the value of the string to its Left, not including the "_".
Obtain this portion of the string: "Ruby Head_Rev 2_Sabre_Op 20"
Using Split I get a portion of the string to its Right (M0020-ALL - H23410), but not the entire string. I think Split is cutting off the string at the first "_" to the right?
For testing purposes I have added MsgBox's to see my result and also removed the SaveAs code.
I've tried incorporating Left into the Split code but I have been unsuccessful.
Any help is appreciated.
Rick Stanich
CMM Programming and Consulting
Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls
I am trying to find the 4th occurrence of the "_" (Under score) and obtain the value of the string to its Left, not including the "_".
Obtain this portion of the string: "Ruby Head_Rev 2_Sabre_Op 20"
Using Split I get a portion of the string to its Right (M0020-ALL - H23410), but not the entire string. I think Split is cutting off the string at the first "_" to the right?
For testing purposes I have added MsgBox's to see my result and also removed the SaveAs code.
I've tried incorporating Left into the Split code but I have been unsuccessful.
CODE --> VBS
Dim objXL, strMessage On Error Resume Next Set objXL = GetObject(,"Excel.Application") If Not TypeName(objXL) = "Empty" then 'MsgBox "The active workbook name is - " & objXL.ActiveWorkbook.Name 'for testing, retrieves active workbook name ok If Left(objXL.ActiveWorkbook.Name,18) = "Ruby Head_Rev 2_Op" Then 'uses generic file naming sFileName = Split(objXL.ActiveWorkbook.Name, "_")(3) MsgBox "Generic - " & sFileName 'for testing 'objXL.ActiveWorkbook.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO\workarea\results\Excel Files" & "\" & sFileName & "\" & objXL.ActiveWorkbook.Name End If If Left(objXL.ActiveWorkbook.Name,24) = "Ruby Head_Rev 2_Sabre_Op" Then 'uses Sabre file naming sFileName = Split(objXL.ActiveWorkbook.Name, "_")(4) MsgBox "Sabre - " & sFileName 'for testing 'objXL.ActiveWorkbook.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO\workarea\results\Excel Files" & "\" & sFileName & "\" & objXL.ActiveWorkbook.Name End If Else MsgBox "No active Excel file open." End If
Any help is appreciated.
Rick Stanich
CMM Programming and Consulting
RE: Find the nth occurrence of a string and report value to the left.
Try this...
CODE
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: Find the nth occurrence of a string and report value to the left.
CODE -->
Dim s As String, t() As String s = "Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls" t = Split(s, "_") ReDim Preserve t(3) s = Join(t, "_") MsgBox s
RE: Find the nth occurrence of a string and report value to the left.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Find the nth occurrence of a string and report value to the left.
Dim s As String, t() As String
to
Dim s, t
(or even <ahem> leave out the declaration altogether ...)
RE: Find the nth occurrence of a string and report value to the left.
Thank you.
Rick Stanich
CMM Programming and Consulting