Find and print specific amount of characters after some symbols

  • Hi all,


    I need to pull 19 numbers that are always after "snippet&nv=" in a row. Also there is more than one time this sentence 'snippet&nv=" in a row and I need all of the numbers (19 straight after this)...


    So ltes say that there is something like this:
    "snippet&amp;nv=3367754797134467621&amp;s=adx" target="_blank">Valkyrie</a>)</div></div></div><div><div class="gwt-Label H61PIGB-nb-a">Customer</div> <div class="H61PIGB-nb-b"><div class="snippet&amp;nv=3367754797123467622&amp;s=adx" target="_blank">Valkyrie</a>)</div></div></div><div><div class="gwt-Label H61PIGB-nb-a">Customer</div> <div class="H61PIGB-nb-b"><div class="snippet&amp;nv=3367754797134467623&amp;s=adx" target="_blank">Valkyrie</a>)</div></div></div><div><div class="gwt-Label H61PIGB-nb-a">Customer</div> <div class="H61PIGB-nb-b"><div class="gwt-"


    This is all i one row, yes it is a part of the HTML code :P
    So I would need to have a result like this:
    3367754797134467621,
    3367754797123467622,
    3367754797134467623,


    Sorry for this weird way of explaining but have no idea how to do it...

  • Re: Find and print specific amount of characters after some symbols


    In order to concatenate the results, we will need a udf.


    Add this UDF to your workbook (to get to VB editor, press ALT key + F11, then go to Insert Module and paste code).



    Then back in workbook, assuming first string is in A1 enter formula:


    [COLOR="#0000FF"]=SUBSTITUTE(TRIM(aconcat(LEFT(TRIM(MID(SUBSTITUTE($A1,"snippet&amp;nv=",REPT(" ",LEN($A1))),1+(LEN($A1)*(ROW($A$2:$A$10)-1)),LEN($A1)+19)),19)," "))," ",","&CHAR(10))[/COLOR]


    With the ROW($A$2:$A$10) it allows up to 9 sets to be extracted from the string... you can change that as necessary.


    The formula is an array formula and needs to be confirmed with CTRL+SHIFT+ENTER to work, before copying down.


    The CHAR(10) allows you to use the wrap text to format the cell with carriage returns.


    You will need to save the workbook as a .xlsm macro enabled workbook.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016