Como separar textos de números y números de texto usando una fórmula
Si alguna vez te preguntaste como hacer para separar textos de números o números de textos de una celda en Excel mediante una fórmula, hoy te traigo una sencilla solución, se trata de una función personalizada (macro) denominada =limpia() la cual hace sencillo el trabajo de separar teniendo en cuenta tres criterios, el tipo 1 separa o extrae solo los números, el tipo 2 separa o extrae todos los caracteres excepto los números y el tipo 3 extrae solo números, de esta forma nos permite efectuar separaciones de acuerdo al criterio que utilicemos, la función tiene estos argumentos =limpia(celda a separar, tipo).
Para tener esta función en tu Excel solo debes de copiar el siguiente código:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Function Limpia(cadena As String, Optional num_car_az As Byte = 1) Dim pat As String Select Case num_car_az Case 2: pat = "[0-9]" Case 3: pat = "[^a-z|ñ]" Case Else: pat = "[^0-9]" End Select With CreateObject("vbscript.regexp") .Global = True .IgnoreCase = True .Pattern = pat Limpia = .Replace(cadena, "") End With If num_car_az = 1 Then Limpia = CLng(Limpia) end if End Function |
Luego nos vamos a Excel, presiona ALT + F11 para acceder al editor de Visual Basic, insertamos un módulo.
Pegamos el código tal como se muestra en la imagen, luego cerramos la ventana del editor de visual basic.
Ahora desde las funciones personalizadas en la lista de funciones ya la tenemos instalada.
y listo, pero sabiendo de que algunos por más instrucciones que uno les deja no lo van a poder hacer dejo el archivo para su descarga.
DESCARGA EL ARCHIVO
Funcion Personalizada en excel para separar números de textos y viceversa.xls
Fuente: TodoExcel
SUSCRÍBETE GRATIS
Te convertiré en un experto en Excel, tengo la experiencia y todo el material que necesitas, mi ayuda es totalmente gratuita.
Hola, excelente aporte, una pregunta como puedo ampliar el rango ?? cuando uso mas de 9 números me sale #¡VALOR!
Gracias
Muy util y sencillo….
Excelente aporte. Muchísimas gracias por compartirlo.
Buenos dias,
Tengo un problema si intento guardar la macro en el libro personal, no me la reconoce.
¿A qué se debe?
(Como Macro para el libro actual si va bien)
Gracias y saludos.
Frank no comprendo tu consulta.
Funciona muy bien, muy buen aporte, muchas gracias
Hola! necesito separar texto de números, respetando los espacios.
Por ejemplo
HOTEL SAVOY/30-6234859-2 debería quedar HOTEL SAVOY
Podrías ayudarme? gracias!!
Gracias, muy buena ayuda….
GRACIAS, FUE DE MUCHA AYUDA
Muy buena página con soluciones de excel. Seguir así que ayudáis a mucha gente. Muchas gracias.
Difícilmente se encuentra personas que brindan servicios con gran desinterés. Las herramientas proporcionadas son muy útiles y prácticas. Muchas gracias. Bendiciones.
Son los putos amos del excel y la programación!!! 😉
Real mente funciona. Mucgas gracias por tu aporte.
Buen día, muy buen aporte. Lo que necesito una vez separados los números es sumar el total del contenido (función suma) y no me deja, abra alguna solución. Gracias….
Carlos para sumar todo debe ser numérico.
Hola, Agradezco eternamente la ayuda que nos brindan por este medio.
Me ha sido de mucha utilidad, soy estudiante aun y no tengo como pagar cursos profesionales.
Muchas gracias y sigan así.
Saludos
Gracias por sus palabras, estaremos publicando nuevos artículos interesantes, saludos.
gracias¡¡¡¡¡
Hola Enrique, te invito a seguir revisando mas artículos, también te pueden interesar. Saludos
Cordial saludo Gustavo,
Gracias por compartir el aporte. Funciona como lo esperado, sin embargo usando esta fórmula, intenten poner una cadena de texto que contenía el carácter # en el campo «A Separar» y no funcionó Por favor ¿Podrías Ayudarme?
Gracias
seguro no tiene números y/o caracteres especiales.
Me funcionó bien, pero el resultado me sale junto, es decir «JuanSanchez» y yo quisiera que tenga un espacio «Juan Sánchez» . No sé como solucionar eso. Gracias
tendrias que aplicar la celda a datos separados, en una celda Juan y en la otra sanchez.
WOW!!!!! Mil gracias… funciona perfecto!
Gracias !!
Sencillamente una excelente función, no domino el lenguaje visual basic, podrias ayudar a mejorarlo a mi necesidad? Quiero que cada 4 caracteres numéricos me haga un espacio, asi de una cadena de 16 caracteres (por ejemplo) me queden separados en 4 grupos de 4 numeros. Gracias antemano (Y) Felicidades por sus conocimientos
Lo tendré en cuenta amigo, esto toma algo de tiempo y me encuentro actualizando la web por ahora.
Buenas noches, si quisiera ver caracteres especiales y números por ejemplo: gasto 12-7
quiero obtener 12.7
Gracias,
Rodrigo no hay esa combinación.
Simplemente maravillosa, funciona excelente,sin duda un gran aporte
P-1 Almohadilla cervical de viaje 103.20 93.80
P-1 Almohadilla cervical de viaje 103.20 93.80
quiero por un lado el texto y por otro el numero
P-1 Almohadilla cervical de viaje 103.20 93.80
Cual seria el resultado????
Buenas tardes, cuando uso la función no me separa el número, lo duplica. permaneciendo el número anterior en la columna original.
Nelson, vuelve a revisar, si mira la imagen animada gif puedes ver la función en su correcto funcionamiento.
HOLA! QUIERO HACER LA MISMA FORMULA PERO EN INGLES. CUANDO LA HAGO CON LA FORMULA CLEAN SOLO ME SIRVE PARA LIMPIAR TEXTO NO ME PERMITE NADA MAS. 🙁
ALGUIEN SABE COMO PODRIA HACERLO FUNCIONAR?
La funcion no cambia de nombre por que es personalizada. Utiliza la misma =limpia
Hola, con el 1 no sirve, me pone #Value! ¿sabes porque?
Alma, puedes escribir aqui que quieres separar.
Lo mismo me pasa a mi, qué hago?
voy a revisar tu caso.
COMO SEPARAR 1.500 ML O 12.5 OZ
LA FORMULA ME LA DA ASÍ 1500 ML Y 125 OZ
PERO LA NECESITO ASÍ 1.500 ML Y 12.5 OZ
ME PUEDEN AYUDAR PARA QUE TOME ENCUENTA EL PUNTO Y NO LO OMITA
Cambia esto:
[^a-z|ñ]|.]»
Muchas gracias; una macro muy útil, acabo de usarla y fantástico.
He usado el comentario de Rodolfo para solucionar el problema de los decimales (dividir *100 la fórmula, gracias Rodolfo).
Entre todos podemos solucionar cualquier problema.
muchas gracias por tu generocidad
Gracias por tu gran ayuda, pero por ejemplo, esta fórmula con este texto da error, por qué puede ser?
MARLBORO RED BOX 2X200(2343487002)
Me gustaría extraer los números que están entre paréntesis 🙂
La función tiene un limite de 9 números juntos, en tu caso tienes 10, dentro de los paréntesis.
Hola, necesitaría que funcinose para más de 10 números juntos, que debo cambiar de la función? Gracais,
Excedes los limites de la función al parecer, déjame revisar.
Se puede aumentar ese limite?
Hola, quisiera saber cómo poder extraer el último caracter numérico contado de izquierda a derecha de una cadena alfanumérica de una celda, cuya longitud de texto es variable, por ejemplo:
En la serie AA-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie 2-AA-001A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie 02-AAA-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie AAA-001-A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie A-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie A-2A-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie 02-AA-1, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
En la serie A-01-AAA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
el 0 no es un número?
Gracias, sirvió a la perfección!
EXCELENTE! eres lo máximo!!!
se agradece la ayuda … buenisimo .
hola buenas tardes, alguien podría decirme como separa este tipo de información
050803 GRIS OXFORD
necesito que la numeración tenga un guion antes del penúltimo No. ejemplo 0508-03
y que el numero y texto estén en diferentes celdas
por ejemplo en la celda
celda E3 el numero numero
celda F3 el numero texto
si es uniforme todo, podría ayudarte, me confirmas.
Muchas gracias, de gran utilidad.
Excelente aporte, aprendí nuevas cosas y resolvio mi problema.
Buen dia, esta Macro esta excelente, sin embargo requiero separar los numeros extraidos por un espacio para identificar en que lugar se extrajo algun caracter dirente de un numero. Alguein me podria ayudar con este tema lo requiero con suma urgencia.
Es decir si tengo el siguente codigo:
wfr/*1154.7/*cef457bvru7882, necesito que la macro extraiga los numeros de la siguiente forma
1154 7 457 7882, que me deje un espacio entre los numero.
Gracias por su colaboracion
Muy buen aporte, lástima que si se trata de números con fracciones (p.ej. 100.15) el resultado de separar los números es 100,15.00 y de momento confunde un poco, pero de todos modos es un excelente aporte.
Gracias por compartir!!!
Hola Amigo, necesito de tu ayuda, como puedo separar numeros que estan en una celda separados por comas A1 (31,34,26,1,2,28)
quiero dejarlos en distintas celdas ejemplo en la
celda B2 el numero 31
celda C3 el numero 34
celda D3 el numero 26
no se si me explico de ser asi te agradeceris eternamente…:)
selecciona los datos, luego te vas a separar en columnas por «,» y listo.
Full Aporte.
Gracias, lo utilizare mucho. 🙂
Muy buen post! gracias!
Muy buena la función. Ahora cuando lo aplico en un archivo, me funciona con la opción 2 y 3, pero con la opción 1 me da error ~#!Valor! No encuentro a qué puede obedecer esto. Podrías ayudarme?
envíame un pantallazo al correo para poder ayudarte.
Excelente, me sirvio de mucho y funciono a la perfeccion.
Excelente Aporte !!
Saludos desde El Salvador
Me encanto esta ayuda, lo felicito y mil gracias por aportar con sus conocimientos aquienes aun tenemos un nivel básico-intermedio en este mundo
EXCELENTE!
Muchas gracias por el gran aporte.
Saludos desde República Dominicana
Me funcionó perfecto en un listado de 710 nombres con importes de dinero, está genial.
El último toque que podrías darle es que no ignore las comas de los centavos, resulta que casi todos los importes estaban con centavos, esto lo arregle dividiendo por 100 la fórmula, pero resulta algunas pocas cifras estaban sin centavos y sin poner ,00 . Poniendo ,00 a las pocas cifras que faltaban, solucionado.
Genial, por tiempo busque esto y ahora me han facilitado el trabajo, se los agradezco por su guia… Muy Buena.
Saludos desde Chile
ATTE
Williams San Martin
Buen día! Excelente función! Una pregunta, estoy intentando llamar a la función dentro del código de una macro con la expresión «WorkSheetFunction.Función(celda)» pero cuando uso la función creada «limpia» me da error. Cual es la forma correcta de llamar a la función creada??
Saludos
asi no se llaman a las funciones personalizadas. Simplemente llamalo Limpia(lo que quieras) y nada mas.
Bastante buena.. y práctica…y supersencilla…
Hola…buen dia……quisiera saber si me pueden hacer un calendario de actividad de auditoria…….
saludos
Buen Día Sebastiani, soy un aficionado del excel, por favor me gustaría que me recomendaras un libro, con el fin de apreder los pasos de programación para crear formularios efectivos en la aplicación.
Saludos desde venezuela