Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como buscar y reemplazar una cadena o texto por otro texto


(adsbygoogle = window.adsbygoogle || []).push({});

Me preguntan en un foro como reemplazar con macro una cadena de texto o string por otra, este ejemplo responde la pregunta, anteriormente se habían publicado ejemplos relacionados como son: como recorrer una cadena de texto y determinar el nombre de un archivorecorrer una cadena de texto estableciendo cual es la extensión de un archivo, como recorrer una cadena de caracteres reemplazando mayúsculas por minúsculasmacro que extrae cadenas de texto o string.

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  
En caso no hayas visto post anteriores relacionados con cadenas,  te recuerdo las principales funciones de VBA que permite manejar o trabajar con cadena de texto o string:

Len: que permite determinar el largo de una cadena
Mid: que permite extraer texto de una cadena de texto empezando en un determinado lugar y estableciendo la cantidad de caracteres a extraer
Instr: que permite determinar la posición de un carácter o cadena de caracteres
StrReverse: permite dar vuelta una cadena de caracteres
Right: permite extraer datos a la derecha
Left: permite extraer datos a la izquierda
Trim: permite eliminar espacios a la izquierda y derecha de una cadena de texto
LTrim: elimina espacios a la izquierda de una cadena de texto o string
RTrim: elimina espacios a la derecha de una cadena de texto o string

El ejemplo recorre desde la primer a la última fila con datos y en cada fila va reemplazando el punto por un valor vacío, para ello se apela al siguiente código:

Replace(a.Range(«A» & i), «.», vbNullString)

El código anterior busca en la cadena que está en la celda, que en cada momento recorre el bucle, buscando el caracter «.» (punto) y lo reemplaza por un valor vacío o nulo, descarga el ejemplo desde el final del post.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Formulario que requiere password para realizar procedimientobuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mailconectar Excel con Access y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}


Código que se inserta en un módulo
Sub Reemplazar()
Dim Tex As Variant, Car As Variant, Lar As Integer
Application.ScreenUpdating = False
On Error Resume Next
Set a = Sheets(«Hoja1»)
uf = Range(«A» & Rows.Count).End(xlUp).Row
For i = 1 To uf
a.Range(«A» & i) = Replace(a.Range(«A» & i), «.», vbNullString)
Next i
Application.ScreenUpdating = True
End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja una respuesta

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

Comentarios (18)

wonderful points altogether, you simply won a new reader.

What may you suggest in regards to your submit that you just made a
few days in the past? Any sure?

Responder

Hello there! I know this is kind of off topic but I was wondering if you knew where I could find a
captcha plugin for my comment form? I’m using the same blog platform as yours and
I’m having difficulty finding one? Thanks a lot!

Responder

Thank you for any other excellent post. Where else could anyone get that type
of information in such an ideal approach of writing?
I have a presentation next week, and I’m at the search for such info.

Responder

Hi to every single one, it’s really a fastidious for me to pay a quick visit this website, it includes useful Information.

Responder

Hi, i believe that i saw you visited my web site so i got here to go back the prefer?.I am
attempting to find things to enhance my website!I assume its good enough to use a few of your
ideas!!

Responder

Heya i’m for the primary time here. I found
this board and I to find It really helpful & it helped me out a lot.

I hope to offer one thing back and help others such as you helped me.

Responder

I’m excited to find this website. I need to to thank you for your time for this fantastic read!!
I definitely liked every little bit of it and I have you
bookmarked to see new things in your blog.

Responder

Thanks in favor of sharing such a fastidious thought, article is fastidious, thats why i
have read it completely

Responder

Hi everyone, it’s my first go to see at this web page, and piece of writing is truly
fruitful in favor of me, keep up posting these posts.

Responder

Excellent post! We are linking to this great post on our website.

Keep up the good writing.

Responder

Hi, i read your blog from time to time and i own a similar one and i was just wondering
if you get a lot of spam responses? If so how do you prevent it, any plugin or anything
you can advise? I get so much lately it’s driving me mad so any support is very much appreciated.

Responder

Hey I know this is off topic but I was wondering if you knew of any
widgets I could add to my blog that automatically tweet my
newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some
experience with something like this. Please let me know if you run into anything.

I truly enjoy reading your blog and I look forward to your new updates.

Responder

Hello would you mind stating which blog platform you’re using?

I’m going to start my own blog in the near future but I’m having
a difficult time making a decision between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your design and style seems different
then most blogs and I’m looking for something completely unique.

P.S Sorry for being off-topic but I had to ask!

Responder

This design is incredible! You obviously know how to keep a reader amused.
Between your wit and your videos, I was almost
moved to start my own blog (well, almost…HaHa!) Wonderful job.
I really loved what you had to say, and more than that, how you presented it.
Too cool!

Responder

For most up-to-date news you have to pay a visit world-wide-web and on internet I
found this website as a best site for most up-to-date updates.

Responder

always i used to read smaller posts which also clear their motive, and
that is also happening with this post which I am reading
now.

Responder

When I initially commented I appear to have clicked on the -Notify me when new comments
are added- checkbox and from now on whenever a comment is added I
receive 4 emails with the exact same comment.

There has to be a way you can remove me from that service?
Cheers!

Responder

It’s great that you are getting thoughts from this piece of writing
as well as from our discussion made at this place.

Responder