Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Data extract

Posted on 2013-11-10
4
288 Views
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
0
Comment
Question by:evend
  • 3
4 Comments
 
LVL 81

Accepted Solution

by:
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.
0
 
LVL 81

Expert Comment

by:byundt
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))
0
 

Author Comment

by:evend
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.
0
 
LVL 81

Expert Comment

by:byundt
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:
=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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

790 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