Este tutorial le enseñará cómo separar texto de números en Excel usando fórmulas nativas y funciones personalizadas.
Imagínese esto: recibe datos sin procesar para el análisis y descubre que los números se mezclan con el texto en una columna. En la mayoría de las situaciones, sin duda será más conveniente tenerlos en columnas separadas para un examen más detallado.
En caso de que esté trabajando con datos homogéneos, probablemente podría usar las funciones IZQUIERDA, DERECHA y MID para extraer la misma cantidad de caracteres desde la misma posición. Pero ese es un escenario ideal para las pruebas de laboratorio. En la vida real, lo más probable es que trate con datos diferentes en los que los números aparecen antes del texto, después del texto o entre textos. Los siguientes ejemplos proporcionan soluciones exactamente para este caso.
Cómo eliminar texto y mantener números en celdas de Excel
La solución funciona en Excel 365, Excel 2021 y Excel 2019
Microsoft Excel 2019 introdujo algunas funciones nuevas que no están disponibles en versiones anteriores, y vamos a usar una de esas funciones, a saber, TEXTJOIN, para quitar caracteres de texto de una celda que contiene números.
La fórmula genérica es:
TEXTJOIN(«», VERDADERO, SI ERROR(MEDIO(celda, FILA(INDIRECTO( «1:»&LEN(celda))), 1) *1, «»))
En Excel 365 y 2021, este también funcionará:
TEXTJOIN(«», VERDADERO, SI ERROR(MEDIO(celda, SECUENCIA(LARGO(celda)), 1) *1, «»))
A primera vista, las fórmulas pueden parecer un poco intimidantes, pero funcionan 🙂
Por ejemplo, para eliminar texto de los números en A2, ingrese una de las siguientes fórmulas en B2 y luego cópiela en tantas celdas como sea necesario.
En Excel 365 – 2019:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2))), 1) *1, ""))
En Excel 2019, debe ingresarse como una fórmula de matriz con Ctrl + Shift + Enter. En matriz dinámica de Excel, funciona como una fórmula normal que se completa con la tecla Intro.
En Excel 365 y 2021:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))
Como resultado, todos los caracteres de texto se eliminan de una celda y los números se conservan:
Cómo funciona esta fórmula:
Para comprender mejor la lógica, comencemos a investigar la fórmula desde adentro:
Utiliza ROW(INDIRECT(«1:»&LEN(string))) o SEQUENCE(LEN(string)) para crear una secuencia de números correspondientes al número total de caracteres en la cadena de origen, y luego alimenta esos números secuenciales a la función MID como los números iniciales. En B2, esta parte de la fórmula tiene el siguiente aspecto:
MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}, 1)
La función MID extrae cada carácter de A2 comenzando con el primero y los devuelve como una matriz:
{"2";"1";"0";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}
Esta matriz se multiplica por 1. Los valores numéricos sobreviven sin cambios, mientras que la multiplicación de un carácter no numérico da como resultado un #¡VALOR! error:
{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
La función IFERROR maneja estos errores y los reemplaza con cadenas vacías:
{2;1;0;"";"";"";"";"";"";"";"";"";"";"";""}
Esta matriz final se sirve a la función TEXTJOIN, que concatena los valores que no están vacíos en la matriz (argumento ignorar_empty establecido en TRUE) usando una cadena vacía («») para el delimitador:
TEXTJOIN("", TRUE, {2;1;0;"";"";"";"";"";"";"";"";"";"";"";""})
Consejo. Para Excel 2016 – 2007, también existe una solución, pero la fórmula es mucho más compleja. Puedes encontrarlo en este tutorial: Cómo extraer números en Excel.
Función personalizada para eliminar texto de números
La solución funciona para todas las versiones de Excel.
Si está utilizando una versión anterior de Excel o encuentra que las fórmulas anteriores son demasiado difíciles de recordar, nada le impide crear su propia función con una sintaxis más simple y un nombre fácil de usar como RemoveText. La función definida por el usuario (UDF) se puede escribir de dos maneras:
Código VBA 1:
Aquí, miramos cada carácter en la cadena de origen uno por uno y verificamos si es numérico o no. Si es un número, el carácter se agrega a la cadena resultante.
Function RemoveText(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If True = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveText = sRes End Function
Código VBA 2:
El código crea un objeto para procesar una expresión regular. Usando RegExp, eliminamos todos los caracteres que no sean los dígitos 0-9 de la cadena de origen.
Function RemoveText(str As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[^0-9]"
RemoveText = .Replace(str, "")
End With
End Function
En hojas de trabajo pequeñas, ambos códigos funcionarán igual de bien. En hojas de trabajo grandes donde la función se llama cientos o miles de veces, el código 2 que usa VBScript.RegExp funcionará más rápido.
Los pasos detallados para insertar el código en su libro de trabajo se pueden encontrar aquí: Cómo insertar código VBA en Excel.
Cualquiera que sea el enfoque que elija, desde la perspectiva del usuario final, la función para eliminar texto y dejar números es tan simple como esto:
QuitarTexto(cadena)
Por ejemplo, para eliminar caracteres no numéricos de la celda A2, la fórmula en B2 es:
=RemoveText(A2)
Simplemente cópielo en la columna y obtendrá este resultado:
Nota. Tanto las fórmulas nativas como la función personalizada generan un cadena numérica. Para convertirlo en un número, multiplique el resultado por 1, agregue cero o envuelva la fórmula en la función VALOR. Por ejemplo:
=RemoveText(A2) + 0
=VALUE(RemoveText(A2))
Cómo eliminar números de una cadena de texto en Excel
La solución funciona en Excel 365, Excel 2021 y Excel 2019
Las fórmulas para eliminar números de una cadena alfanumérica son bastante similares a las discutidas en el ejemplo anterior.
Para Excel 365 – 2019:
TEXTJOIN(«», VERDADERO, SI(ESRR(MEDIO(celda, FILA(INDIRECTO( «1:»&LEN(celda) )), 1) *1), MEDIO(celda, FILA(INDIRECTO(«1:»&LEN( celda))), 1), «»))
En Excel 2019, recuerde convertirlo en un fórmula matricial presionando las teclas Ctrl + Shift + Enter juntas.
Para Excel 365 y 2021:
TEXTJOIN(«», VERDADERO, SI(ESERROR(MEDIO(celda, SECUENCIA(LARGO(celda 1) *1), MEDIO(celda, SECUENCIA(LARGO(celda)), 1), «»))
Por ejemplo, para quitar números de una cadena en A2, la fórmula es:
=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))
O
=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), ""))
Como resultado, se eliminan todos los números de una celda y se conservan los caracteres de texto:
Como se muestra en la captura de pantalla anterior, la fórmula elimina los caracteres numéricos de cualquier posición en una cadena: al principio, al final y en el medio. Sin embargo, hay una advertencia: si una cadena comienza con un número seguido de un espacioese espacio se mantiene, lo que produce un problema de espacios iniciales (como en B2).
Para deshacerse de exceso espacios antes del textoajuste la fórmula en la función TRIM de esta manera:
=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))
¡Ahora, tus resultados son absolutamente perfectos!
Cómo funciona esta fórmula:
En esencia, la fórmula funciona igual que la explicada en el ejemplo anterior. La diferencia es que, desde el arreglo final servido a la función TEXTJOIN, debe eliminar números, no texto. Para hacerlo, usamos la combinación de las funciones IF y ISERROR.
Como recordará, MID(…)+0 genera una matriz de números y #¡VALOR! errores que representan caracteres de texto en las mismas posiciones:
{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
La función ISERROR detecta los errores y pasa la matriz resultante de valores booleanos a IF:
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
Cuando la función IF ve VERDADERO (un error), inserta el carácter de texto correspondiente en la matriz procesada con la ayuda de otra función MID. Cuando la función SI ve FALSO (un número), lo reemplaza con una cadena vacía:
{"";"";"";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}
Esta matriz final se pasa a TEXTJOIN, por lo que concatena los caracteres de texto y genera el resultado.
Función personalizada para eliminar números del texto
La solución funciona para todas las versiones de Excel.
Teniendo en cuenta que una fórmula robusta debe mantenerse simple, compartiré el código de la función definida por el usuario (UDF) para eliminar cualquier carácter numérico.
Código VBA 1:
Function RemoveNumbers(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If False = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveNumbers = sRes End Function
Código VBA 2:
Function RemoveNumbers(str As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers2 = .Replace(str, "")
End With
End Function
Como es el caso de la QuitarTexto función, es mejor usar el segundo código en hojas de trabajo grandes para optimizar el rendimiento.
Una vez que se agrega el código a su libro de trabajo, puede eliminar todos los caracteres numéricos de una celda usando esta función personalizada:
EliminarNúmeros(cadena)
En nuestro caso, la fórmula en B2 es:
=RemoveNumbers(A2)
Para recortar los espacios iniciales, si los hay, anide la función personalizada dentro de TRIM como lo haría con una fórmula nativa:
=TRIM(RemoveNumbers(A2))
Dividir números y texto en columnas separadas
En una situación en la que desea separar texto y números en dos columnas, sería bueno tener el trabajo hecho con una sola fórmula, ¿de acuerdo? Para esto, simplemente fusionamos el código de las funciones RemoveText y RemoveNumbers en una sola función, llamada SplitTextNumbers, o simplemente Split, o lo que quieras 🙂
Código VBA 1:
Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String Dim sNum, sText, sChar As String sCurChar = sNum = sText = "" For i = 1 To Len(str) sCurChar = Mid(str, i, 1) If True = IsNumeric(sCurChar) Then sNum = sNum & sCurChar Else sText = sText & sCurChar End If Next i If True = is_remove_text Then SplitTextNumbers = sNum Else SplitTextNumbers = sText End If End Function
Código VBA 2:
Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
With CreateObject("VBScript.RegExp")
.Global = True
If True = is_remove_text Then
.Pattern = "[^0-9]"
Else
.Pattern = "[0-9]"
End If
SplitTextNumbers = .Replace(str, "")
End With
End Function
Nuestra nueva función personalizada requiere dos argumentos:
SplitTextNumbers(cadena, es_remove_text)
Donde is_remove_text es un valor booleano que indica qué caracteres eliminar:
- VERDADERO o 1: elimina el texto y conserva los números
- FALSO o 0: eliminar números y mantener el texto
Para nuestro conjunto de datos de muestra, las fórmulas toman esta forma:
Para eliminar caracteres no numéricos:
=SplitTextNumbers(A2, TRUE)
Para eliminar caracteres numéricos:
=SplitTextNumbers(A2, FALSE)
Consejo. Para evitar un problema potencial de espacios iniciales, recomiendo siempre envolver la fórmula que elimina números en la función TRIM:
=TRIM(SplitTextNumbers(A2, FALSE))
Para aquellos a los que no les gusta complicar las cosas innecesariamente, mostraré nuestra propia forma de eliminar texto o números en Excel.
Suponiendo que nuestra Ultimate Suite se agregue a su cinta de Excel, esto es lo que debe hacer:
- En la pestaña Datos de Ablebits, en el grupo Texto, haga clic en Remover > Eliminar personajes.
- En el panel del complemento, seleccione el rango de origen, elija la opción Eliminar juegos de caracteres y elija Caracteres de texto o Caracteres numéricos en la lista desplegable.
- Presiona Quitar y disfruta el resultado 🙂
Consejo. Si los resultados contienen algunos espacios iniciales, la herramienta Recortar espacios los eliminará en un abrir y cerrar de ojos.
Así es como eliminar texto o caracteres numéricos de una cadena en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
descargas disponibles
(archivo .xlsm)
Ultimate Suite – versión de prueba (archivo .zip)