Excel Data extract

Posted on 2013-11-10
Last Modified: 2013-11-10
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
Question by:evend
  • 3
LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39637600
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.
LVL 81

Expert Comment

ID: 39637628
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))

Author Comment

ID: 39637703
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.
LVL 81

Expert Comment

ID: 39637731
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


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Remove row and column 3 45
Cascading dropdown 9 26
Copy value from a certain cell 5 24
Get details of a Background Image applied to a sheet in excel 7 11
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now