Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[Brad_Miller]

please can you assist me as well....

Trying to get a number out of a file name - "A4U-20MAY14-CI-004-W-10-A-N.mp4"
I need to get the 10 out of the file name - the 2 dashes from right to left are constant the number (10 in this case) can go up to 4 digits but will always be behind a dash.

TIA

 

[Rick Rothstein]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",300)),900),300))

The above formula returns numbers as text, if you need the number to be a real number, just add 0...

=0+TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",300)),900),300))

 

[Brad_Miller]

Thanks a mill - this was my attempt - LOL

=MID(A3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",A3)+1)+1)+1)+1)+1,(FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",A3)+1)+1)+1)+1)+1)-FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",$A$3,FIND("-",A3)+1)+1)+1)+1)-1))

 

[Brad_Miller]

Would you mind explaining the formula in English - so I can understand the FLOW/LOGIC?

 

[Peter_SSs]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",300)),900),300))

The above formula returns numbers as text, if you need the number to be a real number, just add 0...

=0+TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",300)),900),300))

Minor saving but can't the blue bit be left out?

 

[Rick Rothstein]

Minor saving but can't the blue bit be left out?

Hmm! You know, I do believe you are correct. Thanks for noting that.

 

[snb_]

or a UDF ?

in A1: "A4U-20MAY14-CI-004-W-10-A-N.mp4"

In B1: =F_snb(A1)

In a macromodule:

Function F_snb(c00)
   F_snb = Split(c00, "-")(5)
End Function

 

[Rick Rothstein]

or a UDF ?

in A1: "A4U-20MAY14-CI-004-W-10-A-N.mp4"

In B1: =F_snb(A1)

In a macromodule:

Function F_snb(c00)
   F_snb = Split(c00, "-")(5)
End Function

I don't know why, but I do not see your complete message in your original posting... the part in red is missing and the part in blue appears after your code, not before it.

Anyway, my reason for responding is to note the OP said "the 2 dashes from right to left are constant" which suggested to me that the number of dashes prior to it may not be fixed at 5 which, if that is the case, your code would need to be modified to count the number of dashes total and subtract two from it. With that said, I am not sure I would go with a UDF solution when such a relatively simple formula is available.

EDIT NOTE: Ignore the first paragraph above... now I see the text I said was missing.

 

[snb_]

You mean ?

Function F_snb(c00)
   sn=split(c00,"-")
    F_snb = sn(ubound(sn)-2)
 End Function

 

[Rick Rothstein]

You mean ?

Function F_snb(c00)
   sn=split(c00,"-")
    F_snb = sn(ubound(sn)-2)
 End Function

Yep! :wink: