Estrarre nomi di dominio dagli URL con una formula Excel

Ecco una formula Excel da utilizzare in coppia con Google Webmaster Tools, per conoscere il numero dei SITI (e non di pagine) che vi linkano secondo Google.

Google infatti nei suoi strumenti per webmaster, segnala solo quante e quali pagine (o URL) contengono un link verso il vostro sito, ma non dice a quanti siti tali pagine appartengano.

Spesso però è più utile conoscere il numero dei siti che vi offrono un backlink, e non il numero totale di pagine che vi linkano (ignorando di quanti siti si tratti).

La formula seguente permette di ricavare, in Excel, il nome di dominio (host name) a partire da una cella contenente un URL.

È una specie di espressione regolare tradotta con le funzioni di Excel:

=IF(LEFT(LEFT(SUBSTITUTE(B2,"http://www.",""),FIND("/",SUBSTITUTE(B2,"http://","")&"/")-1),4)="www.",MID(LEFT(SUBSTITUTE(B2,"http://",""),FIND("/",SUBSTITUTE(B2,"http://","")&"/")-1),5,256),LEFT(SUBSTITUTE(B2,"http://",""),FIND("/",SUBSTITUTE(B2,"http://","")&"/")-1))

E per chi ha la versione italiana del programma di Microsoft:

=SE(SINISTRA(SINISTRA(SOSTITUISCI(B2;"http://www.";"");TROVA("/";SOSTITUISCI(B2;"http://";"")&"/")-1);4)="www.";MID(SINISTRA(SOSTITUISCI(B2;"http://";"");TROVA("/";SOSTITUISCI(B2;"http://";"")&"/")-1);5;256);SINISTRA(SOSTITUISCI(B2;"http://";"");TROVA("/";SOSTITUISCI(B2;"http://";"")&"/")-1))

Nella formula, ogni occorrenza di “B2” va sostituita con il riferimento alla cella che contiene l’URL.

Ora andate in Google Webmaster Tools, nella scheda Link > Link esterni, ed esportate il report completo dei vostri backlink:

Google Webmaster Tools Link Esterni

Aprite Excel, ed applicate la formula al report dei link esterni scaricato da Google, ai valori nella seconda colonna: poi filtrate i duplicati (in fondo al post indico come farlo) e contate i valori rimasti.

Otterrete così il numero di domini o hostname unici che concorrono a formare la vostra link popularity, indipendentemente da quante pagine da ciascun dominio ospitino un backlink.

In pratica, nel totale avrete conteggiato un solo link per ogni dominio o sottodominio 🙂

Per completezza, segnalo le guide di Excel su come filtrare i valori univoci rimuovendo i duplicati. Se preferite, potete anche contare solo i valori univoci (senza cancellare i duplicati).

15 risposte a “Estrarre nomi di dominio dagli URL con una formula Excel”

  1. Ciao Maurizio,
    la formula è molto utile, specialmente se combinata con una tabella pivot, magari per visualizzare il numero di backlink per host name.
    Piccola segnalazione: la formula canna sugli host name in https 🙂

  2. @Emanuele: grazie della segnalazione! La formula è complessa e non posso riguardarla ora, ma una soluzione Quick And Dirty potrebbe essere un Find/Replace nei dati, in modo da sostituire https:/// con http:/// – la sostanza non cambia, in fondo.
    Se hai voglia di condividere la tabella pivot per visualizzare il numero di link per host name… 🙂

    @Francesco: sinceramente no, non l’ho provata su OpenOffice.
    Mi pare che su Google Spreadsheets NON funzioni.

  3. Ciao Petro,
    ecco come creare la tabella pivot con numero di link per hostname.

    – Selezionare dal menu file la voce “Inserisci” > “Tabella pivot” > “Seleziona tabella o intervallo”;

    – selezionare la colonna di link (compresa l’intestazione del campo) e la colonna degli host name (anch’essa compresa di nome campo);

    – premere OK, verrà visualizzata la finestra di composizione della tabella pivot in un nuovo foglio, visualizzando a sinistra i riquadri dei campi, a destra i nomi dei campi da inserire/elaborare;

    – trascinare il campo “host name” nel primo riquadro a sinistra della tabella ancora vuota;

    – cliccare sul campo “link” e trascinarlo in basso nel riquadro “Valori” della colonna di autocomposizione;

    – chiudere la colonna di autocomposizione ed il lavoro è pronto.

    Ciao!

  4. Ciao Emanuele,

    grazie mille per aver condiviso la procedura!

    Mi sembra che sia quella per Excel 2007, io uso una versione precedente: appena riesco la “traduco” e posto qui anche la procedura “legacy”.

    Sei stato gentilissimo, ti ringrazio davvero tanto!

    Aggiornamento: in Excel 2003 si inizia dal menu Dati, selezionando la voce Rapporto Tabella Pivot e grafico Pivot.
    Il resto del procedimento è pressochè identico a quello descritto da Emanuele, per qualche motivo io non riuscivo a riprodurlo. Adesso ce l’ho fatta 🙂

    Grazie ancora, Emanuele! A presto 😉

  5. Ciao.
    Questo articolo è molo utile, infatti io usavo google e vedevo solo le pagine che avevano un link al mio sito web, ma non vedevo i siti che mi linkavano.
    Ciao e grazie.

  6. molto interessante io sto appena provando ad utilizzarlo tramite openoffice e sembra andare, faccio un po di test poi vi dico versione ed eventuali differenze, ho dei problemini in certi casi quasi crasha per la gran quantita di domini web, forse è un floop.

  7. Grazie, mi hai risolto un grossissimo problema che avevo nel cercare di analizzare i backlinks della concorrenza. In questo modo mi sarà MOLTO più semplice lavorare e scoprire cosa combinano.

  8. Ciao, grazie per la dritta, è molto utile. Per contare agevolmente i domini ho aggiunto una terza colonna contente la seguente formula =SE(C1=C2;0;1) (semprechè la prima URL è in B2 e la prima formula per estrarre il dominio è in C2) in questo modo posso fare la somma totale della colonna. Ciao e buon lavoro.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *