webb visitor list - any methods to convert search engines query strings?

G

Gunnar Johansson

Hi,

I don't know if this is the best group, but since I'm intending to create
the solution in VBA, i guess it's a start.

I have a table with visitors of a web site and there I get the search
strings if they come to this website directly from a query thorugh google,
altavista, msn etc. I want to have their search words clearly written in the
column.

Now there is difficult to see what it is because there is signs before and
after the words and also UTF translations from language-specific signs.

Is there a easy way to convert this within VBA? I found a intresting article
about this made by a Gerry Pattersson, but unfortunately it was pearl he
made it with. Look: http://www.pgts.com.au/pgtsj/pgtsj0307a.html

Is that the only approach? To recognise and translate prat by part and
sserch engine by serch engine? No "UTF object" and "Convert method"....?-)

Here are som examples:

1# Google " translate search string ":
http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+google+search+string+queries&meta=2 # with swedish signs added " translate search string å ä ö " : http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+search+string+å+ä+ö&meta=3 # The same from altavista " translate search string å ä ö ": http://se.altavista.com/web/results...ng+å+ä+ö&kgs=1&kls=1Suggestions?RegardsGunnar
 
T

Tim Williams

You could try the Windows API:
http://vbnet.mvps.org/index.html?code/internet/urlescape.htm

or
http://www.devx.com/vb2themax/Tip/19353

tim




Gunnar Johansson said:
Hi,

I don't know if this is the best group, but since I'm intending to create
the solution in VBA, i guess it's a start.

I have a table with visitors of a web site and there I get the search
strings if they come to this website directly from a query thorugh google,
altavista, msn etc. I want to have their search words clearly written in the
column.

Now there is difficult to see what it is because there is signs before and
after the words and also UTF translations from language-specific signs.

Is there a easy way to convert this within VBA? I found a intresting article
about this made by a Gerry Pattersson, but unfortunately it was pearl he
made it with. Look: http://www.pgts.com.au/pgtsj/pgtsj0307a.html

Is that the only approach? To recognise and translate prat by part and
sserch engine by serch engine? No "UTF object" and "Convert method"....?-)

Here are som examples:

1# Google " translate search string ":
Click to expand...
http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+google+search+string+queries&meta=2 #
with swedish signs added " translate search string å ä ö " :
http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+search+string+å+ä+ö&meta=3 #
The same from altavista " translate search string å ä ö ":
http://se.altavista.com/web/results...ng+å+ä+ö&kgs=1&kls=1Suggestions?RegardsGunnar
 
D

David McRitchie

Hi Gunnar,

Taking Tim's second reference and the following:

Function from_between(str As String, delim1 As String, delim2 As String) As String
Dim strx As String, i As String
i = InStr(1, str, delim1, 1)
from_between = ""
If i = 0 Then Exit Function
strx = Mid(str, i + Len(delim1))
i = InStr(1, strx, delim2, 1)
If i = 0 Then
from_between = strx
Else
from_between = Left(strx, i - 1)
End If
End Function

=urldecodeex(from_between(A1, "&q=", "&"))

And copied down, yields the following:don't know if the characters will come through correctly

(empty)
translate google search string queries
(empty)
translate search string å ä ö
(empty)
translate search string å ä ö

From your data:http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+google+search+string+queries&meta=2 #
with swedish signs added " translate search string å ä ö " :
http://www.google.se/search?hl=sv&ie=UTF-8&q=translate+search+string+å+ä+ö&meta=3 #
The same from altavista " translate search string å ä ö ":
http://se.altavista.com/web/results...ng+å+ä+ö&kgs=1&kls=1Suggestions?RegardsGunnar

Incidentally the =urlcodeex(a1) by itself produces:http://www.google.se/search?hl=sv&ie=UTF-8&q=translate google search string queries&meta=2 #
with swedish signs added " translate search string å ä ö " :
http://www.google.se/search?hl=sv&ie=UTF-8&q=translate search string å ä ö&meta=3 #
The same from altavista " translate search string å ä ö ":
http://se.altavista.com/web/results?itag=wrx&q=translate search string å ä ö&kgs=1&kls=1Suggestions?RegardsGunnar
 
Top