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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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))
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.
byundtMechanical EngineerCommented:
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:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.