Extracting string of text from one field with different variables

[jl2017]

I am trying to figure out how to extract a portion of a filename from a field, when the filenames differ.  It is for a file database.  This would be easy if all the files are named the same, but we have multiple different filenames based on the type of file it is.  So the idea is I have a "shot_name" field that pulls in the name from the file I am importing into FileMaker.  For instance, here are two of the filenames...one is for a traditional shot and one is for a development shot.

Traditional Shot:  BOG8515_150731_Line-Up
Development Shot:  BDC_dev9150_150721_Line-Up

Essentially the only portion of the shot I want is the following for each:

Traditional Shot:  BOG8515
Development Shot:  BDC_dev9150

I believe there would only be these two variables I would encounter, however the end of the filename may change from _Line-Up to _Change from time to time.  Is there a way to strip out only the shot name?  Can't quite wrap my head around how I would do that in a calculation field.  Any tips or advice would be greatly appreciated!

[comment]

Would this be a correct statement: every filename is divided into several "tokens", separated by an underscore. You want to discard the last two "tokens".

If yes, try:

Let ( 
tokens = Substitute ( YourField ; "_" ; ¶ ) 
;
Substitute ( LeftValues ( tokens ; ValueCount ( tokens ) - 2 ) & ¶ ; [ "¶¶" ; "" ] ;  [ ¶ ; "_" ]  )
)

 

[B.Fehr]

According to your description, the only persistent fact at all is that you need the part "Before" the 2. occurrence of "_" (underline).

How about this CustomFunction?

http://www.briandunning.com/cf/642

 

[jl2017]

Actually the token calculation worked perfectly!  Thanks so much for your help!  Worked like a charm!

[jl2017]

Okay...so I ran into another problem.  This works for one field but I need to extract the date from the filename...basically the 150731 or 150721 from the different file formats as well.  Is this possible using a similar token calculation?  If so that would be awesome.

[comment]

I need to extract the date from the filename...basically the 150731 or 150721 from the different file formats as well.

That's a pretty trivial adjustment:

Let ( 
tokens = Substitute ( YourField ; "_" ; ¶ ) 
;
GetValue ( tokens ; ValueCount ( tokens ) - 1 )
)

 

[jl2017]

Amazing!  Thanks so much.  Worked perfectly.  Really confused about the positions but glad this worked.

[comment]

Really confused about the positions

Why? There are either 3 or 4 "tokens" in your string. The date is always the one before last, so ValueCount ( tokens ) - 1 is the one to get.