Macro Excel para extraer hipervínculos y anchor text

Hoy traigo un par de macros excel, muy pequeñas y sencillas, que posibilitan dos funciones super útiles, en mi opinión, sobre todo cuando tenemos excel de muchos datos con palabras que contienen enlaces.

De esta forma, podremos extraer ambas cosas, en las celdas contiguas, para poder trabajar con esa información. Se me ocurre, hablando de SEO, de una lista de anchor text enlazados, que nos harían perder horas y horas, en caso de tener que ir uno por uno accediendo al enlace, copiándolo y luego pegándolo en otra celda.

Pues bien, con nuestro excel de datos abierto, podemos atajar el problema con la siguiente solución, por pasos:

1. Pulsamos ALT+F11 y abrimos el editor VBA.

2. Una vez ahí, nos vamos al menú horizontal, “insertar” + “modulo”. Añadiremos copiando y pegando, la función “extraer_anchor”:

Function Extraer_Anchor(Rango As Range)
Dim anchor As String
texto = Rango.Hyperlinks(1).Name
Extraer_Anchor = texto
End Function

Esta función lo que hará será extraer el anchor text que contiene un vínculo y mostrarlo sin vínculo en la celda que indiquemos.

Visualmente el editor VBA se vería como sigue:


 

 

 

 

3. Ahora ya podemos cerrar el editor VBA pulsando ALT+Q, o bien, accediendo al menú horizontal en “Archivo” y “Cerrar y Volver a Excel”.

4. Y voilá! Ya podemos hacer uso de esta función llamada “extraer_anchor”, tal y como muestra el pantallazo

excel extraer anchor


Cuyo resultado sería este:

extraer anchor text con vba

Ahora si queremos hacer lo contrario, es decir, extraer el hipervínculo en vez del anchor text, repetimos el proceso desde el primer paso, pero en este caso añadimos la función “extraer_hipervinculo”:

Function Extraer_Hipervinculo(Rango As Range)
Dim Hipervinculo As String
Hipervinculo = Rango.Hyperlinks(1).Address
Extraer_Hipervinculo = Hipervinculo
End Function

Y siguiendo con el ejemplo del pantallazo, la información extraída quedaría así:

Y la celda quedaría rellena con el vínculo de la celda D4, algo como:

Esto es todo amigos, todo el mundo a practicar con Excel, que tiene muuuuuuchas utilidades

Los que hayan leído hasta el final, se llevan de regalo, un recurso muy chulo para aprender excel

 

19 pensamientos en “Macro Excel para extraer hipervínculos y anchor text

  1. Pingback: URL

  2. Luis Ruiz

    Hola María José.

    Te felicito por este artículo, y por el blog en general. Y agradezco la cantidad de información que aportas a la comunidad.
    Solo comentar, ya que no se si es problema de la versión de Excel (Lo probé en Excel 2007), o del Visual Basic. El caso es que quise probar el tip y me puse manos a la obra. Sin embargo me surgió un error en el tema de Anchor. A la hora de hacer el Módulo, comprobé que declarabas una variable que luego no utilizabas, después usabas una variable, sin declararla, y por último aplicabas la propiedad “Name” de Hyperlinks. Yo, a sabiendas de esas “peculiaridades” lo probé y funcionaba, pero al probarlo me daba la url en vez del Anchor. Hice unas modificaciones en el código:

    Function Extraer_Anchor(Rango As Range)
    Dim texto As String
    texto = Rango.Hyperlinks(1).TextToDisplay
    Extraer_Anchor = texto
    End Function

    Y comprobé que así me daba el Anchor del enlace.
    Espero que te sirva. Un saludo

    Responder
    1. mjcachon Autor

      Hola! A priori me funcionó correctamente, pero quizás haya puesto mal el ejemplo!
      Lo revisaré, te agradezco la aportación y aviso :)

      Buen día ;)

      Responder
    2. ClaudioM

      Una observacion: Las funciones cambian según el Service Pack que tengan instalado. Hay funciones que sin service pack estan el ingles y al instalar el SP2 de Office pasan al castellano.

      Responder
    3. Roberto Franco

      hola buenas tardes
      sabes tengo una macro en la que busca mejor dicho “Filtra” un dato de la hoja uno y muestra toda la fila donde esta ubicada dicho dato en la hoja dos
      al final de la fila hay un hipervicunlo pero solo copia el texto pero no el enlace,, tal vez me puedas ayudar

      Responder
  3. Pingback: Formación autodidacta y recursos útiles | Grupo Piloto

  4. giuseppe

    Hola. Mi nombre es Giuseppe Cuttone. He visto por internet que has encontrado una solución para extraer el hipervinculo de una celda. Enhorabuena. La he utilizado y me funciona genial. Ahora tengo otro problema…
    Necesitaría extraer el “valor sangría” de una celda, concretamente el valor numérico (por ejemplo: cero, o uno o dos, etc…).
    La manera más facil para obtenerlo sería ponerse encima de la celda => boton derecho => formato celdas => alineación => sangría.
    Mi problema es que tengo miles de celdas de las cuales sacar el “valor sangría” de cada una de ellas. Hacerlo de una en una sería imposible…
    Me imagino la solución sería hacer una función Sangría, al igual que hiciste tu para sacar el hipervinculo.
    Es decir: 1) Ir al editor VBA. 2) Insertar modulo. 3) Poner la programación de la función Extraer_Sangría.
    Yo no tengo ni idea de esa programación. Tu me podrías ayudar? Muchísimas gracias de antemano. Un saludo

    Responder
    1. mjcachon Autor

      Hola, la verdad que es un problema que no se me ha planteado anteriormente, así a priori no sabría la solución.

      Pero seguro que puedes buscar en Google y en foros especializados o sites de excel, que te podrán ayudar.

      Saludos!

      Responder
  5. esmudo

    Muchas gracias Maria José !!
    Es justo lo que necesitaba ahora mismo por el dicho not provided.Estaba combinando informacion de distintas tablas y tenia que sacar los hipervinculos que me lanzaban a una herramienta pero no me daba el URL, ahora sacandolos en una celda puedo eliminar la primera parte del vinculo que me lleva a la herramienta y así quedarme con la URL de pagina de destino. GENIAL !!!

    Responder
  6. jose

    Hola María José, muy bueno el artículo, la verdad es que con esto voy a ahorrar mucho tiempo, pero me surge otra pregunta, ¿sabes como se podría comprobar si los enlaces están aún activos de forma masiva, sin tener que ir uno por uno?
    Gracias y un saludo,

    Responder
    1. mjcachon Autor

      ¿Te refieres a si responden con 200 o con 404?

      Bájate Excel SEO Tools, y podrás integrar en Excel muchísimas funcionalidades, por ejemplo esa.

      O sino, programas como Xenu o Screaming Frog, o web-sniffer a nivel online, te darán esa información.

      Saludos!

      Responder
  7. lorenzo adriá

    Hola, te agradecería mucho me ayudaras en lo siguiente: Quiero hacer una macro que obtenga las direcciones de las celdas de una hoja excel y luego poder cambiar parte de esa dirección.
    En concreto cada celda me direcciona a un archivo de G: y quiero cambiar todas las direcciones a D:

    Gracias por tu amabilidad

    Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>