Cómo eliminar el texto y dejar los números en Excel o viceversa

Este tutorial le enseñará cómo separar el texto de los números en Excel utilizando fórmulas nativas y funciones personalizadas.

Imagínese esto: recibe datos en bruto para su análisis y descubre que los números están mezclados con el texto en una columna. En la mayoría de las situaciones, sin duda será más conveniente tenerlos en columnas separadas para examinarlos con más detalle.

En caso de que trabaje con datos homogéneos, probablemente podría utilizar las funciones IZQUIERDA, DERECHA y MEDIA para extraer el mismo número de caracteres de 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 tenga que lidiar con datos disímiles en los que los números van antes del texto, después del texto o entre el texto. Los siguientes ejemplos proporcionan soluciones exactamente para este caso.

• Eliminar el texto de los números
• Eliminar los números y mantener el texto
• Separar el texto de los números en dos columnas
• Herramienta especial para eliminar números o texto en Excel

Cómo eliminar el texto y mantener los números en las 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 utilizar una de ellas, concretamente TEXTJOIN, para eliminar los caracteres de texto de una celda que contiene números.

La fórmula genérica es:

TEXTJOIN("", TRUE, IFERROR(MID(cell, ROW(INDIRECT( "1:"&LEN(cell))), 1) *1, ""))

En Excel 365 y 2021, ésta también funcionará:

TEXTJOIN("", TRUE, IFERROR(MID(cell, SEQUENCE(LEN(cell)), 1) *1, ""))

A primera vista, las fórmulas pueden parecer un poco intimidantes, pero funcionan 🙂

Por ejemplo, para eliminar el texto de los números en A2, introduce una de las siguientes fórmulas en B2, y luego cópiala hacia abajo 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, se debe introducir como una fórmula de matriz con Ctrl + Shift + Enter. En Excel de matrices dinámicas, funciona como una fórmula normal completada con la tecla Enter.

En Excel 365 y 2021:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

Como resultado, se eliminan todos los caracteres de texto de una celda y se mantienen los números:

Cómo funciona esta fórmula:

Para entender mejor la lógica, vamos a empezar a investigar la fórmula desde dentro:

Se utiliza ROW(INDIRECT("1:"&LEN(cadena))) o SEQUENCE(LEN(cadena)) para crear una secuencia de números correspondiente al número total de caracteres de la cadena de origen, y luego alimentar esos números secuenciales a la función MID como los números de partida. 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 uno de los caracteres de A2 empezando por 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 lugar a un error de #Valor!

{2;1;0;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!}

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 no vacíos de la matriz (el argumento ignore_empty se establece en TRUE) utilizando 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 encontrarla en este tutorial: Cómo extraer números en Excel.

Función personalizada para eliminar el texto de los números

La solución funciona para todas las versiones de Excel

Si utilizas una versión antigua de Excel o consideras que las fórmulas anteriores son demasiado difíciles de recordar, nada te impide crear tu propia función con una sintaxis más sencilla y un nombre fácil de usar como RemoveText. La función definida por el usuario (UDF) puede escribirse de dos maneras:

Código VBA 1:

Aquí, miramos cada carácter de la cadena fuente uno por uno y comprobamos si es numérico o no. Si es un número, el carácter se añade a la cadena resultante.

Function RemoveText(str As String)
Dim sRes As String

sRes = "N -"
For i = 1 To Len(str)
If True = IsNumeric(Mid(str, i, 1)) Then
sRes = sRes & Mid(str, i, 1)
End If
Siguiente i
RemoveText = sRes
Fin de la función

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 dígitos 0-9 de la cadena fuente.

Function RemoveText(str As String) As String
Con CreateObject(N-"VBScript.RegExp")
.Global = True
.Patrón = "[^0-9]"
RemoveText = .Replace(str, "")
Finalizar con
Fin de la función

En hojas de cálculo 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 utiliza 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.

Sea cual sea el enfoque que elijas, desde la perspectiva del usuario final, la función para eliminar el texto y dejar los números es tan simple como esto:

EliminarTexto(cadena)

Por ejemplo, para eliminar los caracteres no numéricos de la celda A2, la fórmula en B2 es

=EliminarTexto(A2)

Sólo tienes que copiarla en la columna, y obtendrás este resultado:

Nota. Tanto las fórmulas nativas como la función personalizada dan como resultado una cadena numérica. Para convertirla en un número, multiplique el resultado por 1, o añada cero, o envuelva la fórmula en la función VALOR. Por ejemplo

=QuitarTexto(A2) + 0

=VALOR(EliminarTexto(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 comentadas en el ejemplo anterior.

Para Excel 365 – 2019:

TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT( "1:"&LEN(cell) )), 1) *1), MID(cell, ROW(INDIRECT("&LEN(cell)), 1), "")

{"";"";"";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}

Esta matriz final se pasa a TEXTJOIN, para que concatene los caracteres del texto y emita el resultado.

Función personalizada para eliminar los 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, voy a 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
Siguiente i
RemoveNumbers = sRes
Fin de la función

Código VBA 2:

Function RemoveNumbers(str As String) As String
Con CreateObject(N-"VBScript.RegExp")
.Global = True
.Patrón = "[0-9]"
RemoveNumbers2 = .Replace(str, "")
Finalizar con
Fin de la función

Como en el caso de la función RemoveText, el segundo código es mejor utilizarlo en hojas de trabajo grandes para optimizar el rendimiento.

Una vez añadido el código a su libro de trabajo, puede eliminar todos los caracteres numéricos de una celda utilizando esta función personalizada:

EliminarNúmeros(cadena)

En nuestro caso, la fórmula en B2 es

=QuitarNúmeros(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(QuitarNúmeros(A2))

Dividir números y texto en columnas separadas

Cuando quieras separar texto y números en dos columnas, sería bueno tener el trabajo hecho con una sola fórmula, ¿estás de acuerdo? Para ello, sólo tenemos que fusionar 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
Siguiente i

Si True = is_remove_text Entonces
SplitTextNumbers = sNum
Else
SplitTextNumbers = sText
Fin de la función
Fin de la función

Código VBA 2:

Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
Con CreateObject(N-"VBScript.RegExp")
.Global = True
If True = is_remove_text Then
.Patrón = "[^0-9]N-"
Else
Patrón = "0-9".
End If
SplitTextNumbers = .Replace(str, "")
End With
Fin de la función

Nuestra nueva función personalizada requiere dos argumentos:

DividirNúmerosDeTexto(cadena, is_remove_text)

Donde is_remove_text es un valor booleano que indica los caracteres a eliminar:

• TRUE o 1 – eliminar el texto y mantener los números
• FALSE o 0 – eliminar los números y mantener el texto

Para nuestro conjunto de datos de muestra, las fórmulas tienen esta forma:

Para eliminar los caracteres no numéricos:

=SplitTextNumbers(A2, TRUE)

Para eliminar los caracteres numéricos:

=SplitTextNumbers(A2, FALSE)

Consejo. Para evitar un posible problema de espacios a la izquierda, recomiendo siempre envolver la fórmula que elimina los números en la función TRIM:

=TRIM(SplitTextNumbers(A2, FALSE))

Special tool to remove numbers or text

For those who do not like complicating things unnecessarily, I’ll show our own way of removing text or numbers in Excel.

Assuming our Ultimate Suite is added to your Excel ribbon, this is what you do:

On the Ablebits Data tab, in the Text group, click Remove > Remove Characters.

Hit Remove and enjoy the result 🙂

Tip. If the results contain some leading spaces, the Trim Spaces tool will eliminate them in no time.

That’s how to remove text or numeric characters from a string in Excel. I thank you for reading and look forward to seeing you on our blog next week!

Available downloads

Remove text or numbers in Excel – examples (.xlsm file)
Ultimate Suite – trial version (.zip file)

You may also be interested in

• How to remove special/unwanted characters from Excel cells
• How to find and replace multiple values at once in Excel
• How to add text or character to every cell

{
"@context": "https://schema.org”,
"@type": "Article",
"url": "https://www.ablebits.com/office-addins-blog/2021/07/20/remove-text-numbers-from-string-excel/”,
"mainEntityOfPage": "https://www.ablebits.com/office-addins-blog/2021/07/20/remove-text-numbers-from-string-excel/”,
"inLanguage": "en-us",
"headline": "How to remove text and leave numbers in Excel or vice versa",
"description": "Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, it will certainly be more convenient to have them in separate columns for closer examination.",
"image": "remove-text-from-numbers.png",
"author": {
"@type": "Person",
"name": "Svetlana Cheusheva",
"url": "https://www.ablebits.com/office-addins-blog/author/svetlana-cheusheva/”
},
"publisher": {
"@type": "Organization",
"name": "Ablebits.com",
"logo": {
"@type": "ImageObject",
"url": "https://cdn.ablebits.com/_img/d-19/logo/ablebits-logo-desktop.svg”
}
},
"datePublished": "2021-07-20",
"dateModified": "2021-11-04"
}