Extraction formula needed

  • 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

  • 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.




    [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]


    [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]



    [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]



    [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]



    The Mcode for Power Query is as follows

  • 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) :D

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!