Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Funcion SPLIT EXTRAER NUMEROS de un CADENA TEXTO Excel VBA #528

funcion split separar cadenas de texto

Como Separar Cadenas de Texto con la Función Split

En este post se muestra como separar una cadena de texto o String en cadena de caracteres más pequeñas, teniendo en cuenta un separador o indicador común para separar las cadenas de caracteres, la macro de Excel VBA analiza la cadena de caracteres pudiendo extraer número o texto.

Requieres aprender a manejar Excel, 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, te lo recomiendo no te arrepentirás.

  
 

Mira la macro en acción, una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

FUNCION SPLIT – forma de extraer número o texto de una cadena de caracteres mayor

En el ejemplo se muestra como se puede separar la cadena de texto que se encuentra en la columna D del ejemplo en cadenas de texto más pequeñas, en este caso se va a separar todos los datos delimitados por la barra vertical o «pipe».

La macro recorrerá fila por fila extrayendo los números de la cadena, que están separados por pipe y los va a colocara en la columna A, B y C.

Explicación del código de la función split para separar o extraer registros de una cadena de texto

La macro se declara una variable de tipo array o matriz que es donde se guardarán todos los datos obtenidos para luego grabarlos en la hoja de Excel, de la siguiente forma:

Dim cade() As String

El código de la macro de Excel, en segundo lugar establece cual es la última fila con datos para realizar un bucle entre la primer y última fila con datos, lo hace con el siguiente código:

uf = d.Range(«D» & Rows.Count).End(xlUp).Row

Posteriormente se realiza un bucle entre la primer y última fila con datos, recorriendo todas las filas de la columna «D», que es donde está la cadena de caracteres a analizar, se usa el siguiente código:

For x = 2 To uf
….. mi código
Next x

Luego se carga en la variable «Tex» cual es el texto de la celda que recorre en cada momento el bucle, así:

Tex = d.Cells(x, 4)

Posteriormente en la matriz o array se guardan los datos extraídos o cadenas separadas cuyo delimitador o separador de datos es «pipe», de la siguiente forma:
cade() = Split(Tex, «|»)

Por último se recorre cada uno de los datos de la matriz obtenida y contenida en la variable «cade», escribiendo en cada celda de las columnas A, B y C, los datos correspondientes que acaban de ser obtenidos, se usan los siguientes códigos:


For i = LBound(cade) To UBound(cade) ‘elemento cadena datos
d.Cells(filac, col) = CLng(cade(i))
col = col + 1
Next i


Descarga el Libro Como usar la Función Split

Descarga el libro usado en este ejemplo desde el final del post puedes hacerlo gratis y usarlo sin ninguna restricción, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Código del para extraer datos de una cadena de caracteres con función Split

Código que se ingresa en un módulo de VBA

Sub extraecaracteres()
‘*********** by marcrodos **** https://macrosenexcel.com *** https://www.youtube.com/c/programarexcel?sub_confirmation=1 ********
Application.ScreenUpdating = False
Dim cade() As String
‘split del rango de datos a eliminar
filac = 2
col = 1
Set d = Sheets(«Hoja1»)
uf = d.Range(«D» & Rows.Count).End(xlUp).Row
For x = 2 To uf
Tex = d.Cells(x, 4)
cade() = Split(Tex, «|»)
For i = LBound(cade) To UBound(cade) ‘elemento cadena datos
d.Cells(filac, col) = CLng(cade(i))
col = col + 1
Next i
filac = filac + 1
col = 1
Next x
MsgBox («Los datos se extrajeron con éxito»), vbInformation, «https://macrosenexcel.com»
Application.ScreenUpdating = True
End Sub

Sub Borra()
Set d = Sheets(«Hoja1»)
uf = d.Range(«D» & Rows.Count).End(xlUp).Row
d.Range(«A2:C» & uf).Clear
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.

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

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
5 based on 1 votes

Entradas relacionadas

Deja una respuesta

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

Comentarios (42)

Hi there, the whole thing is going well here and ofcourse every one is sharing data, that’s really good,
keep up writing.

Responder

Howdy! Quick question that’s entirely off topic.
Do you know how to make your site mobile friendly?
My weblog looks weird when browsing from my iphone4.
I’m trying to find a template or plugin that might be able to resolve this issue.
If you have any suggestions, please share. Cheers!

Responder

It’s an awesome piece of writing designed for all the web users; they will obtain advantage from it I
am sure.

Responder

Hello, I enjoy reading all of your post. I like to write a little comment to support you.

Responder

I am really grateful to the holder of this web page who
has shared this enormous article at at this place.

Responder

Helpful info. Fortunate me I discovered your website by
accident, and I’m stunned why this accident did not happened
earlier! I bookmarked it.

Responder

Hurrah, that’s what I was exploring for, what a information! existing here at this web site,
thanks admin of this site.

Responder

Hello there, just became aware of your blog through Google,
and found that it’s truly informative. I am going to
watch out for brussels. I will be grateful if you continue
this in future. Lots of people will be benefited from your
writing. Cheers!

Responder

Hey there, You’ve done an incredible job. I’ll certainly digg it and
personally suggest to my friends. I am sure they will be benefited from
this website.

Responder

I was able to find good information from your blog posts.

Responder

I do not know whether it’s just me or if perhaps everyone
else encountering issues with your blog. It seems like some of the written text
on your content are running off the screen. Can somebody else please provide
feedback and let me know if this is happening to them too?
This may be a problem with my browser because I’ve had
this happen previously. Appreciate it

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

If you are going for most excellent contents like myself, just visit
this website all the time for the reason that it gives
feature contents, thanks

Responder

Good day! This post couldn’t be written any better! Reading through this post reminds me of my good old room mate!
He always kept talking about this. I will forward this page to him.
Fairly certain he will have a good read. Thanks for sharing!

Responder

Every weekend i used to visit this web page, because i wish
for enjoyment, since this this web page conations actually
good funny data too.

Responder

You’re so interesting! I do not believe I’ve read through a single thing like this before.
So wonderful to discover another person with genuine thoughts on this subject.
Really.. many thanks for starting this up. This site is something that’s needed on the internet,
someone with some originality!

Responder

It’s not my first time to go to see this site, i am browsing this site
dailly and take nice facts from here all the time.

Responder

What’s up to all, the contents present at this web page are in fact
amazing for people experience, well, keep up the nice
work fellows.

Responder

For newest information you have to go to see web
and on internet I found this website as a finest web site for most up-to-date updates.

Responder

Toller Artikel…

Responder

certainly like your website but you have to take a look at the spelling on quite
a few of your posts. A number of them are rife with spelling issues and I to find
it very troublesome to inform the truth then again I’ll certainly
come again again.

Responder

Very good post! We will be linking to this particularly great article
on our site. Keep up the good writing.

Responder

Somebody essentially help to make significantly posts I might state.

That is the first time I frequented your web page and to this point?
I surprised with the research you made to make this actual post incredible.
Great job!

Responder

What a stuff of un-ambiguity and preserveness of precious experience about unpredicted emotions.

Responder

This piece of writing offers clear idea in support of the new
viewers of blogging, that in fact how to do blogging.

Responder

I couldn’t resist commenting. Very well written!

Responder

Nice response in return of this question with real arguments and telling all concerning
that.

Responder

For newest information you have to pay a quick visit world wide web
and on world-wide-web I found this web page as a finest site for latest updates.

Responder

I think this is among the most vital information for me.
And i’m glad reading your article. But should remark
on few general things, The website style is great, the articles is really great : D.
Good job, cheers

Responder

Hey! I know this is somewhat off topic but I was wondering if you knew where I could
locate a captcha plugin for my comment form?

I’m using the same blog platform as yours and I’m having problems finding one?

Thanks a lot!

Responder

I quite like reading an article that can make people think.

Also, many thanks for allowing me to comment!

Responder

What’s up, after reading this remarkable paragraph i am as well glad to share my familiarity here with mates.

Responder

Quality articles is the important to attract the visitors to visit the site, that’s what this web page is providing.

Responder

Hi it’s me, I am also visiting this site on a regular basis, this
website is really pleasant and the visitors are genuinely sharing good thoughts.

Responder

Thank you for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your next write ups thank you once again.

Responder

Aw, this was an incredibly nice post. Taking a few
minutes and actual effort to make a great article… but what can I say… I procrastinate a whole
lot and never seem to get nearly anything done.

Responder

Thanks for sharing such a nice idea, article is nice, thats why i have read it fully

Responder

Does your website have a contact page? I’m having problems locating it but,
I’d like to shoot you an e-mail. I’ve got some suggestions for your blog
you might be interested in hearing. Either way, great blog and
I look forward to seeing it improve over time.

Responder

Everything is very open with a really clear explanation of the issues.
It was really informative. Your site is very useful. Thanks for
sharing!

Responder

I have to thank you for the efforts you have put in writing
this site. I am hoping to view the same high-grade blog posts by you in the future as well.

In fact, your creative writing abilities has inspired me to get my own site now 😉

Responder

My coder is trying to persuade me to move to .net from PHP.
I have always disliked the idea because of the expenses.
But he’s tryiong none the less. I’ve been using Movable-type on various websites for about a year
and am concerned about switching to another platform. I have heard very good
things about blogengine.net. Is there a way I can import all my wordpress content into
it? Any help would be greatly appreciated!

Responder

I all the time used to read piece of writing in news papers but now as I am a user of internet therefore from now I am using net for content, thanks to web.

Responder