Al trabajar con herramientas de ofimática, comúnmente se opta por extraer una parte de un texto para poder hacerlo más digerible y presentarlo en un formato más amigable a la vista.
En el caso de las URLs ésta extracción no sólo nos facilita la visualización, si no que también nos permite segmentar la secuencia más importante sobre la cual nos vamos a enfocar para obtener información de relevancia para muchas actividades relacionadas al SEO, como lo es el flujo de tráfico que tienen ciertos dominios, el posicionamiento en buscadores, análisis de contenido, entre otras cosas.
Varios profesionistas encargados de algún tipo de actividad relacionada al análisis de datos se han visto en más de una ocasión en la necesidad de trabajar con un gran número de URLs. Desde diseñadores, desarrolladores, comerciantes, empresarios y entusiastas del SEO, han recurrido a Excel como herramientas principal para organizar dichos análisis, por lo que se han visto en la necesidad de implementar fórmulas y scripts que automaticen el proceso de extracción de datos.
¿Para qué sirve extraer el nombre de dominio de una url con excel?
Extraer el nombre de dominio de una URL te permitirá realizar tareas básicas de manipulación con dicha información, tales como agrupar nombres por longitud, ordenar por alfabeto, contar el número de dominios únicos, remover o añadir prefijos, entre otras.
De igual manera te permitirá afinar tus técnicas de promoción y mejorar tu SEO, ya que al filtrar los dominios de grandes listas de información, podrás utilizarlos para recolectar información de tráfico, comparar resultados con tus competidores, encontrar enlaces rotos, eliminar enlaces procesadores, combinar listas, etc.
Excel es una herramienta ideal para realizar este tipo de procesos, ya que es ágil, potente, fácil de utilizar y muy popular. Al utilizar Excel podrás automatizar un proceso bastante tedioso, el cual puede llegar a resultar muy costoso en tiempo y otros recursos si se realiza de manera manual.
Extraer el nombre de dominio con la fórmula
Existen varias fórmulas que te permitirán extraer el nombre de dominio de una URL, la que uses dependerá de la complejidad de tus URLs y del formato que utilicen. La principal ventaja de hacer uso de fórmulas para extraer este tipo de información es la simplicidad, ya que evita la necesidad de crear macros o utilizar editores externos.
Para poder aplicar una fórmula basta con seguir los siguientes pasos:
- Seleccionar la casilla donde quieres desplegar el nombre de dominio
- Insertar la fórmula seleccionada
- Presionar enter
- Arrastrar la fórmula a todas las celdas donde se quiera aplicar la misma acción (Dando clic sobre el icono “+” que aparece en la esquina inferior derecha de la celda)
Incluyendo http / https (protocolo de urls)
Si nuestra lista de direcciones constará únicamente de URLs con diagonal al final del nombre de dominio, una de las fórmulas más sencillas a utilizar sería:
=LEFT(url_completa,FIND(text_a_encontrar,url_completa,posicion_del_caracter_donde_empezara_a_buscar))
Ésta fórmula te permitirá obtener un listado de URLs que incluya el protocolo de transferencia, como lo es http, https, ftp, smtp, o algún otro. Por ejemplo, podemos usar la siguiente fórmula:
=LEFT(A1,FIND("/",A1,9))
Con ella básicamente estamos indicando que nuestra URL completa se encuentra ubicada en la casilla A1, y la función LEFT se aplica para extraer el nombre de dominio del valor de dicha celda.
Por su parte la función FIND, es utilizada para encontrar la posición de la siguiente diagonal que se encuentre ubicada después del noveno carácter (También puede utilizarse la función SEARCH o su equivalente en español HALLAR). La razón por la que se empieza en la novena posición, es para evitar las dos primeras diagonales, las cuales son parte del protocolo (https://).
Si quisiéramos obtener la URL sin la diagonal al final se puede optar por una variante de esta fórmula, la cual incluye un -1 al final, para así extraer el último carácter.
=LEFT(A1,SEARCH("/",A1,9)-1)
Si tu versión de Excel utiliza la funciones en español, entonces podrá lucir de la siguiente manera:
=IZQUIERDA(A1,HALLAR("/",A1,9))
Si bien esta fórmula resulta bastante sencilla, debemos decir que tiene algunas deficiencias.
Si por alguna razón la URL a analizar no tiene una diagonal al final del nombre de dominio, ésta no será capaz de obtener el valor.
De igual manera fallaría en el supuesto caso de que la longitud del protocolo más el nombre de dominio sea menor a 9 caracteres, aunque eso por el momento es imposible, puesto que el dominio debe estar compuesto por 3 caracteres como mínimo, más la extensión y el protocolo.
Excluyendo http / https
Para excluir el protocolo de nuestros nombres de dominio, podemos recurrir a fórmulas que utilicen la función MID, de esa manera podremos extraer partes del texto que se encuentran entre dos caracteres. Por ejemplo:
=MID(A1,FIND(":",A1)+3,FIND("/",A1,9)-FIND(":",A1)-3)
En esta fórmula, la primera función SEARCH se encarga de encontrar la posición del primer carácter “:”, después se le añaden 3 posiciones para evitar las dos diagonales y de esa manera llegar al primer carácter del nombre de dominio.
FIND(":",A1)+3
Después de esto se determina la cantidad de caracteres que tiene el dominio, eso se logra encontrando la posición de la diagonal después del nombre de dominio, a la cual se le resta la posición del primer carácter del nombre de dominio.
Esta posición se obtiene localizando el primer carácter “:” y restándole 3 posiciones con la finalidad de descontar cada diagonal utilizada.
FIND("/",A1,9)-FIND(":",A1)-3
Sin www
Si necesitas obtener el nombre de dominio sin el prefijo www, puedes utilizar una fórmula como la siguiente:
=MID(SUBSTITUTE(A1,"www.",""),SEARCH(":",A1)+3,SEARCH("/",SUBSTITUTE(A1,"www.",""),9)-SEARCH(":",A1)-3)
Con esta fórmula obtenemos tanto el dominio como el subdominio de un sitio, excluyendo tanto el protocolo como el www. Funciona correctamente con extensiones poco comunes como .cc, .co.uk, etc.
La primera instancia de la función SUBSTITUTE se usa para proveer una URL sin el prefijo “www”, mientras que la segunda instancia es usada para calcular cuántos caracteres existen en el nombre dominio. Por su parte MID es usada para extraer el segmento entre el carácter “:” + 3 posiciones y la diagonal al final del nombre de dominio.
Otra opción un poco más compleja puede ser:
=IF(ISERROR(FIND("//www.",A1)), MID(A1,FIND(":",A1,4)+3,FIND("/",A1,9)-FIND(":",A1,4)-3), MID(A1,FIND(":",A1,4)+7,FIND("/",A1,9)-FIND(":",A1,4)-7))
Con este tipo de fórmulas se puede especificar más a detalle lo que se desea hacer con cada uno de los componentes de la URL.
La desventaja que presentan es que el valor inicial necesita tener cierta estructura específica, de no ser así el valor regresado por la fórmula no será el deseado.
Por ejemplo si se deseara obtener el dominio aún cuando la URL no cuenta con una diagonal al final del nombre de dominio, se puede usar una fórmula como la siguiente:
=MID(SUBSTITUTE(A1,"www.",""),SEARCH(":",A1)+3,IF(ISERROR(SEARCH("/",A1,9)),LEN(SUBSTITUTE(A1,"www.",""))-SEARCH(":",A1)-2,SEARCH("/",SUBSTITUTE(A1,"www.",""),9)-SEARCH(":",A1)-3))
A la función MID se le proveé una URL a la cual se le remueve el prefijo “www” con la función SUSTITUTE, indicando que extraiga los caracteres del texto empezando desde el primer carácter del nombre de dominio (+3 caracteres después de los dos puntos “:”).
=MID(SUBSTITUTE(A1,"www.",""),SEARCH(":",A1)+3
Si quieres otra opción, que funciona más o menos bajo la misma tónica, también se puede utilizar una fórmula como ésta:
=SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), "")&"/", FIND("/", REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), "")&"/"), LEN(A1), ""), "www.", "")
Extraer el nombre de dominio con la función definida por el usuario
Si se busca crear una función definida por el usuario para extraer el nombre de dominio de una serie de URLs, se debe recurrir al uso de vba (Visual Basic for Applications).
Para poder definir este tipo de funciones se debe seguir los siguientes pasos:
-
- Pulsar la combinación de teclas Alt+F11, eso abrirá la ventana del editor VBA.
- En el menú elegimos la opción “Insertar” > “Módulo”, y se mostrará la ventana de módulo.
- Dentro de la ventana de módulo ingresamos el código de nuestra función para extraer el dominio, la cual llamaremos Extraccion_de_Dominio. Dicho código estará escrito en lenguaje Visual Basic. Ejemplo:
Function Extraccion_de_Dominio(ByVal URL As String) As String
If InStr(URL, "//") Then
URL = Mid(URL, InStr(URL, "//") + 2)
End If
If Left(URL, 4) Like "[Ww][Ww][Ww0-9]." Then
URL = Mid(URL, 5)
End If
ExtractDomain = Split(URL, "/")(0)
End Function
- Guardamos y cerramos el editor con la combinación de teclas Alt+Q, o desde “Archivo” > “Cerrar y volver a Excel”.
- Finalmente aplicamos la función utilizando su nombre en la celda de la siguiente manera =extraccion_de_dominio(A2).
Detectar páginas indexadas y no indexadas
Una buena forma de poner en práctica los métodos de manipulación de URLs es determinando cuáles son las páginas indexadas y no indexadas de un dominio.
Para ello es necesario tener disponible la colección completa de URLs de un sitio, para ello puedes recurrir al sitemap, utilizar un crawler, una extensión de navegador o como último recurso puedes realizar una recolección manual.
Para obtener los enlaces a todas las páginas que están indexadas podemos hacer uso de herramientas externas como SEO Spider Tool, Ahrefs, o extensiones de navegador como Link Grabber.
Una vez que tengas ambas listas disponibles, lo que debes hacer es copiar cada una en una columna distinta en el archivo de Excel. De esa manera nos quedarían las URLs del índice de Google en la columna A y las del sitemap en la columna B.
Crearemos un rango con los valores de las direcciones web del índice de Google, al cual llamaremos “URLs”. Para hacerlo debemos seleccionar todas las celdas sobre la columna A, desde A1 hasta donde termine la lista, una vez hecho esto damos clic con el botón derecho del mouse y elegimos la opción “Asignar nombre a un rango”.
Una vez que tenemos el rango creado, el siguiente paso es aplicar esta fórmula en una tercera columna, la cual sería C.
=IF(+COUNTIF(URLs,B1),"Indexada","No indexada")
En este caso “URLs” es el nombre que se le dio al rango previamente, y sobre él se realizara la comparativa con las celdas de la columna B, empezando por B1 que es donde se encuentra la primera URL del sitemap. Una vez que se hace la comparativa, en las celdas de la columna C aparecerá el valor “Indexada” si esa misma URL está presente en el rango “URLs” o “No indexada” en caso de no aparecer.
Conclusiones
El uso de estas técnicas te permitirá extraer dominios de forma sencilla, lo que hagas con dicha información ya dependerá de ti.
La automatización de estos procesos te permitirá ser más productivo e invertir el tiempo ganado en otras actividades que ayuden a mejorar tu desempeño, incluido la práctica de Excel y el descubrimiento de nuevas utilidades y funcionalidades.