MJCACHÓN

Macro Excel para extraer hipervínculos y anchor text

66 Flares 66 Flares ×

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

 

66 Flares Twitter 45 Facebook 3 Google+ 10 LinkedIn 8 Buffer 0 66 Flares ×

  21 comments for “Macro Excel para extraer hipervínculos y anchor text

  1. Pat
    17 enero, 2012 at 11:39

    anda que avisas que lo has publicado!
    :P

  2. Pingback: URL
  3. Luis Ruiz
    7 febrero, 2012 at 19:17

    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

    • mjcachon
      8 febrero, 2012 at 13:21

      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 ;)

    • ClaudioM
      19 febrero, 2012 at 4:16

      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.

    • Roberto Franco
      15 enero, 2013 at 4:15

      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

  4. giuseppe
    3 septiembre, 2012 at 11:10

    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

    • 18 octubre, 2012 at 22:59

      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!

  5. 18 septiembre, 2012 at 12:29

    Es la 10° vez más o menos que entro a este post para copiar el código para generar esta función y nunca había dicho gracias. Me parece que ya es hora…
    Saludos.

  6. 18 octubre, 2012 at 14:26

    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 !!!

    • 18 octubre, 2012 at 22:53

      Gracias! Me encanta que algo de lo que publico, pueda ser de utilidad de alguien :)

  7. JL
    26 julio, 2013 at 19:05

    Genial MUchas gracias por la aportacion me funciono perfecto en Mac

  8. Anonimo
    4 septiembre, 2013 at 18:40

    EXCELENTE!
    GRACIAS!

  9. jose
    3 octubre, 2013 at 20:43

    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,

    • 14 octubre, 2013 at 2:20

      ¿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!

  10. lorenzo adriá
    11 diciembre, 2013 at 19:11

    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

  11. 25 julio, 2014 at 13:47

    Juas, muchísimas gracias: no sabes las horas que me has ahorrado con el linkbuilding :D

  12. Henry
    25 noviembre, 2014 at 2:36

    Hola, buenas noches.
    Con las macros descritas desarma o separa un hipervínculo en sus dos componentes, nombre o descripción y URL o destino, ubicándolos en celdas diferentes.
    Deseo hacer exactamente lo contrario.
    En una columna tengo los nombres y en otra los destinos. Cómo uno las dos partes para obtener hipervínculos?
    Agradezco su ayuda.

Deja un comentario

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


66 Flares Twitter 45 Facebook 3 Google+ 10 LinkedIn 8 Buffer 0 66 Flares ×