Find and print specific amount of characters after some symbols

BartesGaming · Oct 24th 2016

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&nv=3367754797134467621&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&nv=3367754797123467622&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&nv=3367754797134467623&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
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...

**NBVC** · Oct 25th 2016

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

Code

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant


If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If


aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Display More

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

[COLOR="#0000FF"]=SUBSTITUTE(TRIM(aconcat(LEFT(TRIM(MID(SUBSTITUTE($A1,"snippet&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.

Find and print specific amount of characters after some symbols

Participate now!

Share

Users Viewing This Thread