×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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.

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.

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.

Hi,

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Rick is using VBS, if that makes any difference.

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

Mine works fine with VBS if you change

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.

(OP)
I ended up removing the declaration.
Thank you.

Rick Stanich
CMM Programming and Consulting

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close