Excel Data extract

I have a text file in a column where data is separated by asterisks and identifiers. I need to extract data by looking for the identifier" ID1 and then pulling out the data between the 3 and 4th asterisks after that identifier into a separate column.

Below that value would 9985.

Any thoughts on if this can be done?



DXS*0000000000*VA*V1/1*1 ST*001*0001 ID1*0**9985*** ID4*2*1 VA1*1812475*15185*1812475*15185 VA2*0*0*0*0 CA1*1299YR01040 *CF7500MDB *123**0 BA1*016882552499*VN2500/AE24 *1201**0 CA2*0*0*0*0 CA3*2026780*286565*204215*15360*2026780*286565*204215*15360 CA4*206165*990*206165*990 CA5*0 CA6*0 DA1*000000000001*VendScreenV1*30**0 DA2*600*4*600*4*0 TA2*0*0*0*0 LS*0001 PA1*1*150* PA2*3093*359400*3093*359400 PA5*240822*1221*4004 PA1*2*150* PA2*1948*239450*1948*239450 PA5*240813*1614*1827 PA1*3*150* PA2*2112*247925*2112*247925 PA5*240813*1615*1941 PA1*4*150* PA2*2447*289275*2447*289275 PA5*251005*2220*2343 PA1*5*175* PA2*2007*245775*2007*245775 PA5*240817*2320*2208 PA1*6*175* PA2*2234*273475*2234*273475 PA5*251002*1846*2037 LE*0001 EA2*DO*831 EA2*CR**0 EA7*167*167 MA5*SWITCH*UNLOCK*1,2,3,4,6**81. MA5*SEL1*1,2*2889*3439 MA5*SEL2*3*1646*1878 MA5*SEL3*4*1831*2014 MA5*SEL4*5*2217*2569 MA5*SEL5*6*1724*2143 MA5*SEL6**1752*1990 MA5*ERROR*CJ7* MA5*ERROR*TJ0* MA5*TUBE1**76*0*113*0 G85*96F2 SE*50*0001 DXE*1*1
evendAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
One method would be to use a formula like:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("*",A1,FIND("*",A1,FIND("ID1*",A1)+4)+1)+1,99),"*",REPT(" ",99)),99))

This formula assumes that ID1 is always followed by an asterisk.
0
 
byundtCommented:
If ID1 may not always be upper case, or if it may not be immediately followed by an asterisk, the following formula will work:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("*",A1,FIND("*",A1,FIND("*",A1,SEARCH("ID1",A1)+3)+1)+1)+1,99),"*",REPT(" ",99)),99))
0
 
evendAuthor Commented:
If I want to pull out other data with other field identifiers what do I need to adjust.
I adjusted the identifier and it worked.  But the data is not always in between the 3 and asterisk. Sometime it can be between 2 and 3 or 5 and 6.

Thank you!  You answered the first part.
0
 
byundtCommented:
If you want other field identifiers and offsets, you would be much better off using a user-defined function to return the value. Modifying the formula I gave you requires a strong understanding of how that formula works.

Using the user-defined function Parser, you could use a formula like:
=Parser(A1,"ID1",3)
This formula returns the third field after the one containing ID1.

I suggest using the function wizard to use the function the first time. Doing so preserves the capitalization. Parser is found under the last category: "User defined functions".

Note that there are two optional variables that will appear in the function wizard. You don't have to enter any values for these. They give you the ability to require matching the entire field for SearchText (bMatchPart = FALSE; default is TRUE) and to choose the separator between fields (default is an asterisk).

The code assumes that each asterisk terminates a different field. Two asterisks in succession mean that the second field is blank.

The following code should go in a regular module sheet.
Function Parser(DataString As String, SearchText As String, FieldOffset As Long, _
    Optional bMatchPart As Boolean = True, Optional separator As String = "*") As Variant
Dim v As Variant, vSubstrings As Variant
Dim i As Long, n As Long
Dim sMatch As String
Parser = "N/A"
vSubstrings = Split(DataString, separator)
On Error Resume Next
n = UBound(vSubstrings)
On Error GoTo 0
If n = 0 Then Exit Function

sMatch = SearchText
If bMatchPart = True Then sMatch = "*" & sMatch & "*"
v = Application.Match(sMatch, vSubstrings, 0) - 1   'MATCH function is 1-based. vSubstrings is 0-based.
If Not IsError(v) Then
    Parser = vSubstrings(CInt(v) + FieldOffset)
End If
End Function

Open in new window

ParserQ28290411.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.