Solved

Excel Data extract

Posted on 2013-11-10
4
292 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
[X]
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
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

751 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