El tutorial muestra cómo extraer números de varias cadenas de texto en Excel mediante fórmulas y la herramienta Extraer.
Cuando se trata de extraer parte de una cadena de texto de una longitud determinada, Excel proporciona tres funciones de subcadena (izquierda, derecha y mitad) para manejar la tarea rápidamente. Cuando se trata de extraer números de una cadena alfanumérica, Microsoft Excel proporciona… nada.
Para obtener un número de una cadena en Excel, se necesita un poco de ingenio, un poco de paciencia y un montón de funciones diferentes anidadas entre sí. O bien, puede ejecutar la herramienta Extraer y hacer el trabajo con un clic del mouse. A continuación encontrará todos los detalles sobre ambos métodos.
Cómo extraer el número del final de la cadena de texto
Cuando tiene una columna de cadenas alfanuméricas donde el número viene después del texto, puede usar la siguiente fórmula para obtenerlo.
DERECHA (celda, LEN (celda) – MAX (SI (ES NÚMERO (MEDIO (CELDA, FILA (INDIRECTO («1:» & LEN (celda))), 1) * 1) = FALSO, FILA (INDIRECTO («1:» &LARGO(celda))), 0)))
Nos detendremos en la lógica de la fórmula un poco más tarde. Por ahora, simplemente reemplace celda con una referencia a la celda que contiene la cadena original (A2 en nuestro caso) e ingrese la fórmula en cualquier celda vacía en la misma fila, digamos en B2:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))
Esta fórmula obtiene el número solo desde el final. Si una cadena también tiene números al principio o en el medio, se ignoran:
La extracción se realiza con la función DERECHA que pertenece a la categoría de funciones de Texto. La salida de esta función es siempre texto. En nuestro caso, el resultado es un subcadena numéricaque en términos de Excel también es texto, no número.
Si necesita que el resultado sea un número (que puede usar en cálculos posteriores), luego envuelva la fórmula en la función VALOR o realice una operación aritmética que no cambie el resultado, digamos, multiplique por 1 o agregue 0. Para detectar errores en las cadenas que no contienen un número único, utilice la función IFERROR. Por ejemplo:
=IFERROR(VALUE(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))), "")
o
=IFERROR(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0))) +0, "")
Nota. En Dynamic Array Excel (Office 365 y 2021), ingresa la fórmula de la forma habitual con la tecla Intro. En Excel 2019 y versiones anteriores, solo funciona como una fórmula de matriz, así que recuerde presionar Ctrl + Shift + Enter para completarla.
Cómo funciona esta fórmula:
Para extraer un número de una cadena alfanumérica, lo primero que debe saber es dónde comenzar la extracción. La posición del último carácter no numérico en una cadena se determina con la ayuda de esta fórmula engañosa:
MAX(SI(ESNÚMERO(MEDIO(A2, FILA(INDIRECTO(«1:»&LEN(A2))), 1)*1)=FALSO, FILA(INDIRECTO(«1:»&LEN(A2))), 0) )
Para entender la lógica, investiguemos desde adentro:
La combinación ROW(INDIRECT(«1:»&LEN(A2))) crea una secuencia de números correspondientes al total de caracteres en la cadena de origen (A2), y servimos estos números secuenciales a MID como los números iniciales:
MEDIO(A2, {1;2;3;4;5;6;7;8}, 1)
La función MID extrae cada carácter individual de A2 y los devuelve como una matriz:
{«0″;»5″;»-«;»E»;»C»;»-«;»0″;»1»}
Como MID es una función de texto, su salida siempre es texto (como puede notar, todos los caracteres están entre comillas). Para convertir los numéricos en números, multiplicamos la matriz por 1 (la doble negación –MID() tendrá el mismo efecto). El resultado de esta operación es una matriz de números y #¡VALOR! errores que representan caracteres no numéricos:
ESNUMERO({0;5;#VALOR!;#VALOR!;#VALOR!;#VALOR!;0;1})
La función ESNUMERO evalúa cada elemento de la matriz y da su veredicto en forma de valores booleanos: VERDADERO para números, FALSO para cualquier otra cosa:
{VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO}
Esta matriz pasa a la prueba lógica de la función SI, donde cada elemento de la matriz se compara con FALSO:
SI({VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO}=FALSO, FILA(INDIRECTO(«1:»&LEN(A2))), 0)
Para cada FALSO (valor no numérico), otra función ROW(INDIRECT()) devuelve su posición relativa en la cadena. Por cada VERDADERO (valor numérico), se devuelve un cero. La matriz resultante se ve de la siguiente manera:
{0;0;3;4;5;6;0;0}
El resto es fácil. La función MAX encuentra el número más alto en la matriz anterior, que es la posición del último valor no numérico en la cadena (6 en nuestro caso). Simplemente, reste esa posición de la longitud total de la cadena devuelta por LEN, y pase el resultado a RIGHT para que sepa cuántos caracteres extraer del lado derecho de la cadena:
DERECHO(A2, LONGITUD(A2) – 6)
¡Hecho!
Cómo extraer el número desde el principio de la cadena de texto
Si está trabajando con registros donde el texto aparece después del número, puede extraer el número desde el comienzo de una cadena utilizando esta fórmula genérica:
IZQUIERDA (celda, COINCIDIR (FALSO, ES NÚMERO (MEDIO (celda, FILA (INDIRECTO («1:» & LEN (celda) + 1)), 1) * 1), 0) -1)
Con la cadena original en A2, usa la siguiente fórmula para obtener el número:
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)
No importa cuántos dígitos haya en el medio o al final, solo se extrae el número inicial:
Nota. En Excel 365 y Excel 2021, debido a la compatibilidad con matrices dinámicas, una fórmula regular funciona bien. En Excel 2019 y versiones anteriores, debe presionar Ctrl + Shift + Enter para convertirlo explícitamente en un fórmula matricial.
Cómo funciona esta fórmula:
Aquí, nuevamente usamos la combinación de las funciones ROW, INDIRECT y LEN para crear una secuencia de números igual al total de caracteres en la cadena de origen más 1 (el papel de ese carácter adicional se aclarará un poco más adelante).
FILA(INDIRECTO(«1:»&LARGO(A2)+1))
MID y ESNUMERO hacen el mismo trabajo que en el ejemplo anterior: MID extrae caracteres individuales y ESNUMERO los convierte en valores lógicos. La matriz resultante de VERDADERO y FALSO va a la función COINCIDIR como una matriz de búsqueda:
COINCIDIR(FALSO, {VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO}, 0)
COINCIDIR calcula una posición relativa del primer FALSO, dándonos la posición del primer carácter no numérico en la cadena (3 en A2). Para extraer los números anteriores, restamos 1 desde la posición del primer carácter de texto y servimos la diferencia al argumento num_chars de la función IZQUIERDA:
IZQUIERDA (A2, 3-1)
Ahora, volvamos a un carácter «extra» en la secuencia generada por ROW(INDIRECT()+1)). Como ya sabe, esta secuencia proporciona los puntos de partida para la función MID. Sin +1, MID extraería exactamente tantos caracteres como hay en la cadena original. Si la cadena contiene solo números, ESNUMERO devolverá solo VERDADERO, mientras que COINCIDIR necesita al menos un FALSO. Para asegurarnos de eso, agregamos un carácter más a la longitud total de la cadena, que la función MID convertiría en una cadena vacía. Por ejemplo, en B7, MID devuelve esta matriz:
{«1″;»2″;»3″;»4″;»»}
Nota. Como es el caso con la función DERECHA, IZQUIERDA también devuelve un subcadena numérica, que técnicamente es texto, no número. Para obtener el resultado como un número en lugar de una cadena numérica, anide la fórmula en la función VALOR o multiplique el resultado por 1 como se muestra en el primer ejemplo.
Cómo obtener un número desde cualquier posición en una cadena
Si su tarea implica extraer un número de cualquier parte de una cadena, puede utilizar la siguiente fórmula alucinante publicada en Foro de MrExcel:
=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)
Donde A2 es la cadena de texto original.
Desglosar esta fórmula requeriría un artículo separado, por lo que simplemente puede copiarlo en su hoja de trabajo para asegurarse de que realmente funcione 🙂
Sin embargo, al examinar los resultados, es posible que observe un inconveniente insignificante: si la cadena de origen no contiene un número, la fórmula devuelve cero, como en la fila 6 de la captura de pantalla anterior. Para solucionar esto, puede envolver la fórmula en la instrucción IF, cuya prueba lógica verifica si la cadena de origen contiene algún número. Si es así, la fórmula extrae el número; de lo contrario, devuelve una cadena vacía:
=IF(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))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")
Como se muestra en la siguiente captura de pantalla, la fórmula mejorada funciona maravillosamente (felicitaciones a Alex, nuestro gurú de Excel, por esta mejora):
A diferencia de todos los ejemplos anteriores, el resultado de esta fórmula es número. Para asegurarse de esto, observe los valores alineados a la derecha en la columna B y los ceros iniciales truncados.
Extraiga el número de la cadena de texto con Ultimate Suite
Como acaba de ver, no existe una fórmula trivial de Excel para extraer un número de una cadena de texto. Si tiene dificultades para comprender las fórmulas o ajustarlas para sus conjuntos de datos, puede que le guste esta forma sencilla de obtener números de una cadena en Excel.
Con nuestra Ultimate Suite agregada a su cinta de Excel, así es como puede recuperar rápidamente un número de cualquier cadena alfanumérica:
- Vaya a la pestaña Datos de Ablebits > grupo Texto y haga clic en Extracto:
- Seleccione todas las celdas con las cadenas de origen.
- En el panel de la herramienta Extraer, seleccione el Extraer números boton de radio.
- Dependiendo de si desea que los resultados sean fórmulas o valores, seleccione la Insertar como fórmula cuadro o déjelo sin seleccionar (predeterminado).
Mi consejo es seleccionar esta casilla si desea que los números extraídos se actualicen automáticamente tan pronto como se realicen cambios en las cadenas de origen. Si desea que los resultados sean independientes de las cadenas originales (por ejemplo, en caso de que planee eliminar los datos de origen en un momento posterior), no seleccione esta casilla.
- Haga clic en el Insertar resultados botón. ¡Hecho!
Como en el ejemplo anterior, los resultados de la extracción son númeroslo que significa que puede contar, sumar, promediar o realizar cualquier otro cálculo con ellos.
En este ejemplo, hemos optado por insertar los resultados como valoresy el complemento hizo exactamente lo que se le pidió:
Si se seleccionó la casilla de verificación Insertar como fórmula, observaría un fórmula en la barra de fórmulas. ¿Tienes curiosidad por saber cuál? Simplemente descargue la versión de prueba de Ultimate Suite y compruébelo usted mismo 🙂
(archivo .xlsx)
Ultimate Suite – versión de prueba (archivo .zip)