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.

 

[Peter_SSs]

I am not sure I would go with a UDF solution when such a relatively simple formula is available.

I agree, & if it happens that the number of dashes before the required extract is fixed at 5 then the formula to extract the number becomes simpler too:

=MID(SUBSTITUTE(A1,"-",REPT(" ",100)),500,100)+0


.. and back to the case where the number of dashes before may not be fixed at 5 & Rick your normal desire to be minimalist, I missed some blue highlighting that can be omitted from this formula.

.. if you need the number to be a real number, just add 0...

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

 

[Rick Rothstein]

.. and back to the case where the number of dashes before may not be fixed at 5 & Rick your normal desire to be minimalist, I missed some blue highlighting that can be omitted from this formula.

...if you need the number to be a real number, just add 0...

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

Another good point! Excel will absorb any leading or trailing spaces when it adds the zero to the "text number".

 

[Peter_SSs]

.. and back to the case where the number of dashes before may not be fixed at 5 & Rick your normal desire to be minimalist, I missed some blue highlighting that can be omitted from this formula.

.. if you need the number to be a real number, just add 0...

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

Actually, this is not quite right. I was testing with 'space multiples' of 100 per my other formula in post #51, not multiples of 300 as Rick had done.
In order to omit the TRIM() and have the 0+ coerce the text string to a number, I think those 'space multiples' need to be 255 or less.

 

[Rick Rothstein]

Actually, this is not quite right. I was testing with 'space multiples' of 100 per my other formula in post #51, not multiples of 300 as Rick had done.
In order to omit the TRIM() and have the 0+ coerce the text string to a number, I think those 'space multiples' need to be 255 or less.

The ability to add a number to a text string consisting of a number surrounded by spaces has a limit (on the text string) of 255 characters? I don't think I have ever read that before, but I just tested it and you are correct... 255 works and 256 fails. I just learned another new Excel limit that I was not aware of. I don't remember the origins of my using the TRIM function in that formula construction before, but I bet it had to do with testing involving large numbers of spaces without (at the time) fully understanding the #VALUE! errors I had seen. Very interesting.

 

[no100yang]

Hey,
I need help.
I have strings

QWER A31(10)C1A2.1/4.F.a.SDA3.Z
WIENS EA532Q5B3/2j9fak

How do I extract the numbers between A and C on first string and the number between EA and Q on second. Basically, the numbers on the second part of each line, and ends at the end of the next starting non-number value. In this case I want to extract 31(10) and 532. The first Set of characters, QWER and WIENS are random length characters. If the numbers between the two characters have ( ), I want the number and the ( ) as well.

Please help,

Thanks

 

[Rick Rothstein]

Give this UDF (user defined function) a try..

Function GetNumber(S As String) As String
  Dim X As Long, Z As Long, Txt As String
  Txt = Split(S)(1)
  For X = 1 To Len(Txt)
    If Mid(Txt, X, 1) Like "[0-9(]" Then
      For Z = X + 1 To Len(Txt)
        If Mid(Txt, Z, 1) Like "[!0-9()]" Then
          GetNumber = Mid(Txt, X, Z - X)
          Exit Function
        End If
      Next
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetNumber just like it was a built-in Excel function. For example,

=GetNumber(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

Welcome to the MrExcel board!

Another UDF to try.

If the first 'part' of each line never contains any digits or parentheses then try

Function GetNum(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "[\d\(\)]+"
    GetNum = .Execute(s)(0)
  End With
End Function

If the first 'part' could contain digits or parentheses then change the two blue lines to these

.Pattern = "( \D*)([\d\(\)]+)"
GetNum = .Execute(s)(0).SubMatches(1)

 

[no100yang]

Thanks Rick and Peter! They worked perfectly!

Another question,

QWER A31(10)C1A2.1/4.F.a.SDA3.Z
WIENS EA532Q5B3/2j9fak
AWOIJ AG412W(P)2S3.9/2asind


if I want to extract the number between two characters, example from above, between C and A on first string, between Q and B on second string and between W(P) and S on the 3rd string.
They should be the second set of number(s) in the string. Meaning, after the first number, like what you've helped me on, the second set of numbers are what I need now. They should be 1, 5 and 2 respectively. The character before the second set of number could have parentheses.

Thanks a lot!

 

[Rick Rothstein]

The following UDF will handle any value... all you have to do is specify which value you want as the second argument. For your original question, the second argument would be 1 for the first number... for your current question, the second argument would be 2 for the second number, and so on. You can let Excel figure that out for you if you want to pull all the numbers by using the UDF like this. Assuming your text is in cells A1, A2, A3 on down, put this formula in B1 and copy it across for as many columns as you think you will ever have numbers for, then copy all those formulas down to the end of the data in Column A.

=GetNumber($A1,COLUMN(A1))

Here is the UDF code...

Function GetNumber(ByVal S As String, Optional NumericalFieldNumber As Long = 1) As String
  Dim X As Long, Arr() As String
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9()./]" Then Mid(S, X) = " "
  Next
  S = Replace(Replace(Replace(Replace(Replace(S, "( ", " "), " )", " "), "()", " "), ". ", " "), " .", " ")
  Arr = Split(Application.Trim(S))
  If NumericalFieldNumber <= UBound(Arr) + 1 Then GetNumber = Arr(NumericalFieldNumber - 1)
End Function

 

[egboliou33]

I was able to figure this out for cell 1: =MID(A44,FIND(",",A44)+1,FIND(":",A44)-FIND(",",A44)-1)

I'm still working on the others.