Ver video: Cómo extraer números de una cadena de texto en Excel (usando fórmula y VBA)
No hay una función incorporada en Excel para extraer los números de una cadena en una celda (o viceversa: elimine la parte numérica y extraiga la parte de texto de una cadena alfanumérica).
Sin embargo, esto se puede hacer usando un cóctel de funciones de Excel o algún código VBA simple.
Primero permítanme mostrarles de qué estoy hablando.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea extraer los números de la cadena (como se muestra a continuación):
El método que elija también dependerá de la versión de Excel que esté utilizando:
- Para versiones anteriores a Excel 2016, debe usar fórmulas un poco más largas
- Para Excel 2016, puede usar la función TEXTJOIN recién introducida
- El método VBA se puede utilizar en todas las versiones de Excel
Haga clic aquí para descargar el archivo de ejemplo
Extraer números de una cadena en Excel (fórmula para Excel 2016)
Esta fórmula funcionará solo en Excel 2016 ya que utiliza la función TEXTJOIN recién introducida.
Además, esta fórmula puede extraer los números que se encuentran al principio, al final o en el medio de la cadena de texto.
Tenga en cuenta que la fórmula TEXTJOIN cubierta en esta sección le daría todos los caracteres numéricos juntos. Por ejemplo, si el texto es "El precio de 10 boletos es de USD 200", le dará 10200 como resultado.Suponga que tiene el conjunto de datos como se muestra a continuación y desea extraer los números de las cadenas en cada celda:
A continuación se muestra la fórmula que le dará una parte numérica de una cadena en Excel.
= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), ""))
Esta es una fórmula de matriz, por lo que debe usar "Control + Mayús + Entrar"En lugar de utilizar Enter.
En caso de que no haya números en la cadena de texto, esta fórmula devolvería un espacio en blanco (cadena vacía).
¿Cómo funciona esta fórmula?
Permítanme romper esta fórmula e intentar explicar cómo funciona:
- FILA (INDIRECTO (“1:” & LARGO (A2))): esta parte de la fórmula daría una serie de números comenzando por uno. La función LEN en la fórmula devuelve el número total de caracteres en la cadena. En el caso de "El costo es de USD 100", devolverá 19. Las fórmulas se convertirían entonces en FILA (INDIRECTA ("1:19"). La función FILA entonces devolverá una serie de números - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1) * 1) - ¡Esta parte de la fórmula devolvería una matriz de #VALUE! errores o números basados en la cadena. Todos los caracteres de texto de la cadena se convierten en # ¡VALOR! errores y todos los valores numéricos permanecen como están. Esto sucede porque hemos multiplicado la función MID por 1.
- IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1) * 1), ””) - Cuando se usa la función IFERROR, eliminaría todos los #VALUE! errores y solo quedarían los números. La salida de esta parte se vería así: {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; 1; 0; 0}
- = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1) * 1), ””)) - La función TEXTJOIN ahora simplemente combina los caracteres de cadena que permanece (que son solo los números) e ignora la cadena vacía.
Descargar el archivo de ejemplo
También puede utilizar la misma lógica para extraer la parte de texto de una cadena alfanumérica. A continuación se muestra la fórmula que obtendría la parte de texto de la cadena:
= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1), ""))
Un cambio menor en esta fórmula es que la función SI se usa para verificar si la matriz que obtenemos de la función MID son errores o no. Si es un error, mantiene el valor; de lo contrario, lo reemplaza con un espacio en blanco.
Luego, TEXTJOIN se usa para combinar todos los caracteres de texto.
Precaución: Si bien esta fórmula funciona muy bien, utiliza una función volátil (la función INDIRECTA). Esto significa que, en caso de que use esto con un conjunto de datos enorme, puede llevar algún tiempo darle los resultados. Es mejor crear una copia de seguridad antes de usar esta fórmula en Excel.Extraer números de una cadena en Excel (para Excel 2013/2010/2007)
Si tiene Excel 2013. 2010. o 2007, no puede usar la fórmula TEXTJOIN, por lo que tendrá que usar una fórmula complicada para hacer esto.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea extraer todos los números de la cadena en cada celda.
La siguiente fórmula hará esto:
= SI (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2))), 1)) * FILA (INDIRECTO ("$ 1: $" & LARGO (A2))), 0), FILA (INDIRECTO ("$ 1: $" & LARGO (A2)))) + 1,1) * 10 FILA (INDIRECTO ("$ 1: $" & LEN (A2))) / 10), "")
En caso de que no haya ningún número en la cadena de texto, esta fórmula volvería en blanco (cadena vacía).
Aunque se trata de una fórmula de matriz, no necesito para usar "Control-Shift-Enter" para usar esto. Una simple entrada funciona para esta fórmula.
El crédito a esta fórmula es para el increíble foro de Mr. Excel.
Nuevamente, esta fórmula extraerá todos los números de la cadena sin importar la posición. Por ejemplo, si el texto es "El precio de 10 boletos es de USD 200", le dará 10200 como resultado.
Precaución: Si bien esta fórmula funciona muy bien, utiliza una función volátil (la función INDIRECTA). Esto significa que, en caso de que use esto con un conjunto de datos enorme, puede llevar algún tiempo darle los resultados. Es mejor crear una copia de seguridad antes de usar esta fórmula en Excel.Separe texto y números en Excel usando VBA
Si separar texto y números (o extraer números del texto) es algo que tiene que hacer con frecuencia, también puede usar el método VBA.
Todo lo que necesita hacer es usar un código VBA simple para crear una función definida por el usuario (UDF) personalizada en Excel, y luego, en lugar de usar fórmulas largas y complicadas, use esa fórmula VBA.
Permítame mostrarle cómo crear dos fórmulas en VBA: una para extraer números y otra para extraer texto de una cadena.
Extraer números de cadena en Excel (usando VBA)
En esta parte, le mostraré cómo crear la función personalizada para obtener solo la parte numérica de una cadena.
A continuación se muestra el código VBA que usaremos para crear esta función personalizada:
Función GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Siguiente i GetNumeric = Función de fin de resultado
Estos son los pasos para crear esta función y luego usarla en la hoja de trabajo:
- Vaya a la pestaña Desarrollador.
- Haga clic en Visual Basic (también puede usar el atajo de teclado ALT + F11)
- En el backend del VB Editor que se abre, haga clic con el botón derecho en cualquiera de los objetos del libro de trabajo.
- Vaya a Insertar y haga clic en Módulo. Esto insertará el objeto de módulo para el libro de trabajo.
- En la ventana Código del módulo, copie y pegue el código VBA mencionado anteriormente.
- Cierre el editor de VB.
Ahora, podrá utilizar la función GetText en la hoja de trabajo. Dado que hemos hecho todo el trabajo pesado en el código en sí, todo lo que necesita hacer es usar la fórmula = GetNumeric (A2).
Esto le dará instantáneamente solo la parte numérica de la cadena.
Tenga en cuenta que, dado que el libro de trabajo ahora tiene código VBA, debe guardarlo con la extensión .xls o .xlsm.
Descargar el archivo de ejemplo
En caso de que tenga que usar esta fórmula con frecuencia, también puede guardarla en su Libro de macros personal. Esto le permitirá utilizar esta fórmula personalizada en cualquiera de los libros de Excel con los que trabaja.
Extraer texto de una cadena en Excel (usando VBA)
En esta parte, le mostraré cómo crear la función personalizada para obtener solo la parte de texto de una cadena.
A continuación se muestra el código VBA que usaremos para crear esta función personalizada:
Función GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) Siguiente i GetText = Función de fin de resultado
Estos son los pasos para crear esta función y luego usarla en la hoja de trabajo:
- Vaya a la pestaña Desarrollador.
- Haga clic en Visual Basic (también puede usar el atajo de teclado ALT + F11)
- En el backend de VB Editor que se abre, haga clic con el botón derecho en cualquiera de los objetos del libro de trabajo.
- Vaya a Insertar y haga clic en Módulo. Esto insertará el objeto de módulo para el libro de trabajo.
- Si ya tiene un módulo, haga doble clic en él (no es necesario insertar uno nuevo si ya lo tiene).
- Si ya tiene un módulo, haga doble clic en él (no es necesario insertar uno nuevo si ya lo tiene).
- En la ventana Código del módulo, copie y pegue el código VBA mencionado anteriormente.
- Cierre el editor de VB.
Ahora, podrá utilizar la función GetNumeric en la hoja de trabajo. Dado que hemos hecho todo el trabajo pesado en el código en sí, todo lo que necesita hacer es usar la fórmula = GetText (A2).
Esto le dará instantáneamente solo la parte numérica de la cadena.
Tenga en cuenta que, dado que el libro de trabajo ahora tiene código VBA, debe guardarlo con la extensión .xls o .xlsm.
En caso de que tenga que usar esta fórmula con frecuencia, también puede guardarla en su Libro de macros personal. Esto le permitirá utilizar esta fórmula personalizada en cualquiera de los libros de Excel con los que trabaja.
En caso de que esté utilizando Excel 2013 o versiones anteriores y no tenga
