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
Talk To Other Members
RE: Find the nth occurrence of a string and report value to the left.
Try this...
CODE
' Dim i As Integer, a, FileName1 As String Const DSH = "_" a = Split(objXL.ActiveWorkbook.Name, DSH) For i = 0 to 3 sFileName1 = a(i) & DSH Next sFileName1 = Left(sFileName1, Len(sFileName1) - 1)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 sRE: 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