Extraction formula needed

slick225 · May 22nd 2019

I'm in search of a formula that will extract specific content that will vary in length. I'm working with a file that tags, these tags come in the following variety: 8=, 49=, 150=, etc. and the values following these tags can vary in length. the tags have a specific meaning but the positioning of the tags may not always be the same.
I need a formula (not VB Code, as I'm not proficient in VB), that will allow me to locate a tag and display it's value. Note: after each value there is a space to separate the tags, so the formula needs to find a specific tag (example: 52=20190522) and report only 20190522 by displaying everything after the 52= and up until the next space.
Because the file has multiple tags, I need it to identify specific tags and report from that tags = character to the next space which ends the tag presented and starts the next tag.

Sample Data:
8=FIX.4.2 9=123 35=7 56=Testfile 52=20190522-12:35:56

Expected Results:
Cell A1 = FIX.4.2
Cell A2 = 123
Cell A3 = 7
Cell A4 = Testfile
Cell A5 = 20190522-12:35:56

Thank you in advance for any help

**alansidman** · May 22nd 2019

If you are using Excel 2010 or later this an easy step by step scenario using Power Query. Load your data into power query. Split each using delimiters.

Your end result will look like this.
[table="class:thin_grid"]

[tr][td]

v

[/td]

[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]

[/tr][tr]

[td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Column1.1.2[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Column1.2.2[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Column1.3.2[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Column1.4.2[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Column1.5.2[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]FIX.4.2[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]123[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]7[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]Testfile[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"]20190522-12:35:56[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"][/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"][/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"][/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"][/COLOR][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][COLOR="#000000"][/COLOR][/td]

[/tr]

[/table]

The Mcode for Power Query is as follows

Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumnUsingSpace = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    SplitFirstColumn = Table.SplitColumn(SplitColumnUsingSpace, "Column1.1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
    SplitColumn = Table.SplitColumn(SplitFirstColumn, "Column1.2", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(SplitColumn, "Column1.3", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.3.1", "Column1.3.2"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Column1.4", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.4.1", "Column1.4.2"}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Split Column by Delimiter4", "Column1.5", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.5.1", "Column1.5.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter5",{"Column1.2.1", "Column1.3.1", "Column1.4.1", "Column1.5.1", "Column1.1.1"})
in
    #"Removed Columns"

Display More

slick225 · May 23rd 2019

The Code provided is interesting but I wouldn't know how to apply it to my spreadsheet, I was looking for an Excel formula or formulas to accomplish the task.
However your approach did give me an idea, using the spaces as separator makes the data a bit more manageable, but that require me to save the tag message as a text file, then import it into Excel. It's a viable workaround at the moment since time is of the essence, but if you ( alansidman) or anyone select have a formula approach I'd love to read it.
Note: [USER="108665"]AlanSidman[/USER] thank you for your quick response, I'm just no where near your level to deploy your provided code (one day my friend, I'll be there)

**alansidman** · May 23rd 2019

Look at this link on what to do with the code I provided.

https://excel.solutions/2017/1…r-query-paste-code-video/

Extraction formula needed

Participate now!

Share

Users Viewing This Thread