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.

 

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

-Sorry, I was responding to another comment. Please disregard.

 

[snb_]

In A1: QWER A31(10)C1A2.1/4.F.a.SDA3.Z
In B1 =F_snb(A1,2)

In a macromodule

Function F_snb(c00, y)
  c00 = Replace(c00, " ", "")

  For j = 1 To Len(c00)
     If Val(Mid(c00, j)) > 0 Then
        c01 = c01 & " " & Format(Val(Mid(c00, j)))
        j = j + Len(Format(Val(Mid(c00, j)))) - 1
     Else
        c01 = c01 & Mid(c00, j, 1)
     End If
  Next
  
  F_snb = Val(Split(c01)(y))
End Function

 

[Rick Rothstein]

In A1: QWER A31(10)C1A2.1/4.F.a.SDA3.Z
In B1 =F_snb(A1,2)

In a macromodule

Function F_snb(c00, y)
  c00 = Replace(c00, " ", "")

  For j = 1 To Len(c00)
     If Val(Mid(c00, j)) > 0 Then
        c01 = c01 & " " & Format(Val(Mid(c00, j)))
        j = j + Len(Format(Val(Mid(c00, j)))) - 1
     Else
        c01 = c01 & Mid(c00, j, 1)
     End If
  Next
  
  F_snb = Val(Split(c01)(y))
End Function

Your code does not return what the OP asked for in Message #55 for the first "number"... he wanted "31(10)" returned whereas your function returns just "31". As for other symbols, we are left up in the air... in my lastest code (Message #59), I assumed forward slashes would remain with the numbers whereas you did not, but we will need the OP to come back and clarify how the numbers and symbols should actually be grouped (if at all).

 

[Peter_SSs]

The character before the second set of number could have parentheses.

You mention that the character before could have parentheses but this time you haven't mentioned whether the second set set of numbers could also have parentheses like the first set. Like Rick, I'm assuming there could be parentheses as well so AWOIJ AG412W(P)2(3)S3.9/2asind would yield "2(3)" for the second set.

Function GetNum(S As String, Optional WhichNum As Long = 1) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "((\(\d+\))|\d+)+"
    GetNum = .Execute(S)(WhichNum - 1)
  End With
End Function

Formula in B2 is copied across and down.

Excel Workbook
	A	B	C
1	String	Set 1	Set 2
2	QWER A31(10)C1A2.1/4.F.a.SDA3.Z	31(10)	1
3	WIENS EA532Q5B3/2j9fak	532	5
4	AWOIJ AG412W(P)2S3.9/2asind	412	2
5	AWOIJ AG412W(P)2(3)S3.9/2asind	412	2(3)
Sheet1

Rick, I'd recommend referencing the column(s) the formulas are actually in when constructing the worksheet formula.
As it stands, subsequently inserting a new column A or B would mean your function returns incorrect results.

 

[Scottb6776]

I have a similar issue, I have been scratching my head with this for days.

I am needing to only extract the series that is in bold and underlined. The problem with those series is, there are hyphens, spaces, texts, numbers. Any way this is possible?

[TABLE="width: 889"]
<TBODY>[TR]
[TD]Sender: </SPAN>SBS R-10034793-001000 Inconsistency between a document field and the prof. seg</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-00128 INS Too Cap - NIKON Laser N00/6199 is blocked</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-40002-1 -5 -1 AUTOBODY POLISHING Settlement Receiver Type SBS Not Allowed


I thank you in advanced. </SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[Rick Rothstein]

I have a similar issue, I have been scratching my head with this for days.

I am needing to only extract the series that is in bold and underlined. The problem with those series is, there are hyphens, spaces, texts, numbers. Any way this is possible?

[TABLE="width: 889"]
<tbody>[TR]
[TD]Sender: SBS R-10034793-001000 Inconsistency between a document field and the prof. seg
[/TD]
[/TR]
[TR]
[TD]Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post
[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-00128 INS Too Cap - NIKON Laser N00/6199 is blocked
[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-40002-1 -5 -1 AUTOBODY POLISHING Settlement Receiver Type SBS Not Allowed


I thank you in advanced.
[/TD]
[/TR]
</tbody>[/TABLE]

** DUPLICATE QUESTION **

You asked this same question here...

http://www.mrexcel.com/forum/excel-...th-numeric-extraction-string.html#post4074554

and I responded with a couple of question that you need to answer. You should not post the same question in multiple threads (theoretically, you should not jump on the end of someone else's thread, but your question seemed related enough to the above quoted link for me not to admonish you for doing that... there). The problem with multiple postings is the volunteers here will not know about each thread the question is in and quite possibly waste their time providing you with the same answer that you got at the other thread... that is not fair to the volunteers you are asking to help you... so please don't multi-post like that in the future.

 

[sivani_007]

i was wondering if anyone can please help with the formula that can extract ALL PERCENTAGES DATA E.G 70% viscose 25% nylon 5% elastane.

Data is divided like this
80% cotton 20% nylon.
Petite sizes 6 - 18 length 34"/86.5 cm<br/>Regular sizes 6R - 22R length 36.5"/93 cm<br/>Tall sizes 8T - 18T length 38"/96.5 cm<br/><br/>70% viscose 25% nylon 5% elastane.


result should be like
80% cotton 20% nylon.
70% viscose 25% nylon 5% elastane.

 

[_DBB_]

I have a similar issue I can't resolve.

A1 : SS1 \nLocation: Village 1 \nTutor: AGP
B1 : SS2 \nLocation: Village 2 \nTutor: GS4

I'm trying to extract the Location Name - Village 1 Village 2 etc. and have managed to substitute fixed characters with =SUBSTITUTE(SUBSTITUTE(A1," \nLocation: ","|")," \nTutor: ","|") and tried to rework what Ron has done, but get lost in the formula!

Thanks in advance for your assistance.

 

[_DBB_]

Just to add to this - the SS1 and AGP fields are variable in length, the only constants are \nLocation: and \nTutor:

Thanks

 

[crane315]

This one is really powerful. I just changed the number from "3" to "2", the extracted characters changed accordingly. So this formula literally can be used to extract the characters between any two "_"s. Thanks.