在excel中从URL中提取GA UTM参数 [英] Extract GA UTM parameters from URL in excel

问题描述

我需要在excel的coluns中提取utm参数.成千上万的网址是这样的:

I need extract utm parameters in coluns in excel. Are thousands url's like this:

http://www.site.com.br/porta-montada-sanfonada?utm_source=google&utm_medium=display&utm_term=468x60&utm_content=34545&utm_campaign=nome_campanha

http://www.site.com.br/torneira-para-banheiro-mesa-bica-baixa-cromada?utm_source=facebook&utm_medium=carrossel&utm_term=120x600&utm_content=87648715&utm_campaign=nome_campanha

How i make this in excel?

解决方案

Assuming row 1 is a header row so that actual data starts in row 2, and that the URLs are in column A, then put this formula in cell B2 and copy over and down (I have it copied over to column I and down to row 3):

=IF((LEN($A2)-LEN(SUBSTITUTE($A2,"utm_","")))/4<COLUMN(A2),"","utm_"&SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,"utm_",REPT(" ",LEN($A2))),COLUMN(A2)*LEN($A2),LEN($A2))),"&",""))

Here are what my results look like using that data setup and formula:

这篇关于在excel中从URL中提取GA UTM参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！