Ver Video – Cómo extraer números de una cadena de texto en Excel (usando la fórmula y VBA)
No existe una función incorporada en Excel para extraer los números de una cadena en una celda (o viceversa – eliminar la parte numérica y extraer la parte de texto de una cadena alfanumérica).
Sin embargo, esto puede hacerse usando un cóctel de funciones de Excel o algún código simple de VBA.
Déjame primero mostrarte de qué estoy hablando.
Suponga que tiene un conjunto de datos como el que se muestra a continuación y quiere extraer los números de la cadena (como se muestra a continuación):
El método que elijas dependerá también de la versión de Excel que utilices:
- Para las versiones anteriores a Excel 2016, es necesario utilizar fórmulas ligeramente más largas
- Para el Excel 2016, puedes usar la recién introducida función TEXTJOIN
- El método VBA puede ser utilizado en todas las versiones de Excel
Haga clic aquí para descargar el archivo de ejemplo
Glosario
Extraer números de la cadena en Excel (Fórmula para Excel 2016)
Esta fórmula sólo funcionará en Excel 2016 ya que utiliza la recién introducida función TEXTJOIN.
Además, esta fórmula puede extraer los números que están al principio, al final o en medio de la cadena de texto.
Ten en cuenta que la fórmula de TEXTJOIN que se trata en esta sección te daría todos los caracteres numéricos juntos. Por ejemplo, si el texto es «El precio de 10 billetes es de 200 dólares», te dará 10200 como resultado.
Supongamos que tienes el conjunto de datos como se muestra a continuación y quieres extraer los números de las cadenas en cada celda:
A continuación se muestra la fórmula que te dará la 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 necesitas usar ‘ Control + Shift + Enter ‘ en lugar de usar 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?
Déjame romper esta fórmula e intentar explicarte cómo funciona:
- ROW(INDIRECT(«1:»&LEN(A2))) – esta parte de la fórmula daría una serie de números empezando desde uno. La función LEN de la fórmula devuelve el número total de caracteres de la cadena. En el caso de «El costo es de 100 dólares», devolverá 19. Las fórmulas se convertirían así en ROW(INDIRECT(«1:19»). La función ROW devolverá entonces 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 serie de errores o números #VALOR! basados en la cadena. Todos los caracteres de texto en la cadena se convierten en errores #VALOR! y todos los valores numéricos se mantienen tal cual. Esto ocurre cuando 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 errores #VALOR! y sólo 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 la cadena que quedan (que son sólo los números) e ignora la cadena vacía.
Pro Tip: Si quieres comprobar la salida de una parte de la fórmula, selecciona la celda, pulsa F2 para entrar en el modo de edición, selecciona la parte de la fórmula de la que quieres la salida y pulsa F9. Verás el resultado instantáneamente. Y luego recuerda presionar Control + Z o pulsar la tecla Escape. NO pulse la tecla Intro.
Descargar el archivo de ejemplo
También puedes usar 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 IF se usa para comprobar si el conjunto que obtenemos de la función MID son errores o no. Si es un error, mantiene el valor, o lo reemplaza con un espacio en blanco.
Luego se usa TEXTJOIN para combinar todos los caracteres del texto.
Precaución : Aunque esta fórmula funciona muy bien, utiliza una función volátil (la función INDIRECTA). Esto significa que en caso de que la uses con un conjunto de datos enorme, puede tomar algún tiempo para darte los resultados. Es mejor crear una copia de seguridad antes de usar esta fórmula en Excel.
Extraer números de la cadena en Excel (para Excel 2013/2010/2007)
Si tienes el Excel 2013. 2010. o 2007, no puedes usar la fórmula de TEXTJOIN, por lo que tendrás que usar una fórmula complicada para hacerlo.
Supongamos que tienes un conjunto de datos como el que se muestra a continuación y quieres extraer todos los números de la cadena en cada celda.
656q0-1.92 0.672-3.616t1.856-2.976 2.72-2.208 3.296-1.408 3.616-0.448q2.816 0 5.248 1.184t3.936 3. 456 1.504 5.12q0 1.728-0.32 3.36t-1.088 3.168-1.792 2.656-2.56 1.856-3.392 0.672q-1.216 0-2.4-0.576t-1.728-1. 568q-0,16 0,704-0,48 2,016t-0,448 1,696-0,352 1,28-0,48 1,248-0,544 1,12-0,832 1,408-1,12 1,536l-0,224 0. 096-0,16-0,192q-0,288-2,816-0,288-3,36 0-1,632 0,384-3,68t1,184-5,152 0,928-3,616q-0,576-1,152-0,576-3,008 0 1.504 0.928-2.784t2.368-1.312q1