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:
Excel formula to remove text and leave numbers

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:
Custom function to remove text and keep numbers in Excel

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), “”)

En Excel 2019, recuerda que debes convertirla en una fórmula de matriz pulsando las teclas Ctrl + Shift + Enter juntas.

Para Excel 365 y 2021:

TEXTJOIN(“”, TRUE, IF(ISERROR(MID(cell, SEQUENCE(LEN(cell 1) *1), MID(cell, SEQUENCE(LEN(cell)), 1), “”)

Por ejemplo, para eliminar los 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 mantienen los caracteres de texto:
Excel formula to remove numbers from a text string

Como se muestra en la captura de pantalla anterior, la fórmula elimina los caracteres numéricos de cualquier posición de una cadena: al principio, al final y en medio. Sin embargo, hay una advertencia: si una cadena comienza con un número seguido de un espacio, ese espacio se mantiene, lo que produce un problema de espacios iniciales (como en B2).

Para deshacerse de los espacios extra antes del texto, envuelva la fórmula en la función TRIM de la siguiente manera

=TRIM(TEXTJOIN(“”, TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), “”)))

Ahora, ¡los resultados son absolutamente perfectos!
Remove numbers and trim spaces before text

Cómo funciona esta fórmula:

En esencia, la fórmula funciona igual que la explicada en el ejemplo anterior. La diferencia es que, del array final servido a la función TEXTJOIN, hay que eliminar los números, no el texto. Para hacerlo, utilizamos la combinación de las funciones IF e ISERROR.

Como recuerdas, MID(…)+0 genera un array de números y errores #VALUE! que representan caracteres de texto en las mismas posiciones:

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

La función ISERROR captura los errores y pasa la matriz resultante de valores booleanos a IF:

{FALSO;FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO}

Cuando la función IF ve TRUE (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 IF ve FALSE (un número), lo sustituye por una cadena vacía:

{“”;””;””;” “;”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))

Custom function to remove numbers from a string in Excel

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)
Splitting numbers and text into separate columns

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.
    Remove characters in Excel
  • On the add-in’s pane, select the source range, choose the Remove character sets option, and pick either Text characters or Numeric characters in the drop-down list.
  • Hit Remove and enjoy the result 🙂
    Remove numeric characters
  • 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

    {
    “@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”
    }

    Excel: artículos destacados

    • Combinar varias hojas en una sola
    • Combinar archivos de Excel en uno solo
    • Comparar dos archivos / hojas de trabajo
    • Combinar 2 columnas en Excel
    • Comparar 2 columnas en Excel para encontrar coincidencias y diferencias
    • Cómo combinar dos o más tablas en Excel
    • CONCATENAR en Excel: combinar cadenas de texto, celdas y columnas
    • Crear un calendario en Excel (desplegable e imprimible)
    • 3 formas de eliminar los espacios entre palabras en las celdas de Excel

    var b20CategorySlug = “remove-text-numbers-from-string-excel”;

    Tabla de contenidos

    Scroll to Top