Hemos aprendido cómo eliminar valores numéricos de una celda en Excel 2016 y versiones anteriores. Las fórmulas que usamos eran un poco complejas, pero ahora Excel 2019 y 365 están en el juego.
Excel 2019 y 365 introducen algunas funciones nuevas que facilitan la tarea de eliminar caracteres no numéricos y recuperar solo valores numéricos en una nueva celda. Usaremos fórmulas que nos puedan ayudar a hacerlo, de manera más conveniente.
Fórmula genérica
| =UNIRSE A TEXTO("",CIERTO,SI ERROR(MEDIO(texto_desordenado,SECUENCIA(NumChars), 1) +0, "")) |
Jumbled_text: Este es el texto fuente del que desea extraer todos los valores numéricos.
NumChars: Este es el número total de caracteres que desea procesar. El jumbled_text no debe tener más caracteres que este número (caracteres y números combinados).
Veamos un ejemplo para aclarar las cosas.
Ejemplo: eliminar caracteres no numéricos y extraer todos los números
Así que aquí tenemos un texto desordenado. Este texto contiene algunos números y algunos caracteres no numéricos. Necesito deshacerme de los caracteres no numéricos y obtener valores numéricos solo en la columna D.
No espero que el número total de caracteres en el texto mezclado sea más de 20. Entonces, el valor de NumChars es 20 aquí. Puede aumentar este número si lo necesita.
Aplique aquí la fórmula genérica anterior para eliminar los caracteres no numéricos.
| =UNIRSE A TEXTO("",CIERTO,SI ERROR(MEDIO(C3,SECUENCIA(20),1)+0,"")) |
Y cuando presionas el botón Enter. Obtienes todos los caracteres no numéricos eliminados. Arrastre hacia abajo esta fórmula para eliminar los caracteres de la cadena de todas las celdas de la columna C3.
¿Como funciona?
Primero veamos cómo se resuelve esta fórmula paso a paso.
| 1-> TEXTJOIN("",CIERTO,SI ERROR(MEDIO(C3,SECUENCIA(20),1)+0,"")) |
| 2-> UNIRSE A TEXTO("",CIERTO,SI ERROR(MEDIO("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> UNIRSE A TEXTO("",CIERTO,SI ERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> UNIRSE A TEXTO("",CIERTO,SI ERROR({1; 2; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; 1; 2; # ¡VALOR!; 1; 2; 3; # ¡VALOR!; # ¡VALOR!;…; # ¡VALOR!}+0,"")) |
| 5-> UNIRSE A TEXTO("",CIERTO,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Como puede ver, la fórmula comienza a resolverse desde adentro. Al principio se resuelve la función SECUENCIA. Ya que pasamos 20. Devuelve una matriz de números que comienzan del 1 al 20.
Esta matriz se sirve a la función MID como número inicial. La función mid va a cada índice de la cadena y divide cada carácter. Puedes ver eso en el paso 3.
A continuación, agregamos 0 a cada carácter. En Excel, si intenta agregar un número a caracteres no numéricos, el resultado es # ¡VALOR! Error. ¡Entonces obtenemos una matriz de números y #VALOR! Errores. Los caracteres no numéricos se han ido ahora.
La siguiente función SI.ERROR reemplaza todos los errores de #VALOR con "" (en blanco). Ahora nos quedamos con valores numéricos y espacios.
Finalmente, esta matriz se sirve a la función TEXTJOIN. La función TEXTJOIN los concatena y obtenemos una cadena que solo contiene números.
Mejorando la Fórmula
La fórmula anterior utilizó un número codificado para procesar el número de caracteres (tomamos veinte). Pero es posible que desee que sea dinámico. En ese caso, acertó, usaremos la función LEN. Se necesitará un número exacto de caracteres para su procesamiento. Entonces la fórmula será.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(texto_desordenado)),1)+0,"")) |
Aquí, la función LEN detectará automáticamente el número exacto de caracteres en la cadena alfanumérica. Esto aliviará la carga de determinar el número máximo de caracteres.
Alternativa de la función SECUENCIA
Si no desea utilizar la función SECUENCIA, puede utilizar una combinación de la función FILA e INDIRECTA para generar números secuenciales.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,HILERA(INDIRECTO("1:"&LEN(texto_jumbled))),1)+0,"")) |
El INDIRECTO convertirá el texto ("1:20") en el rango real y luego la función FILA enumerará todos los números de fila del 1 al 20. (20 es solo por ejemplo. Puede ser cualquier número).
Así que sí, chicos, así es como pueden copiar los caracteres no numéricos de una cadena alfanumérica en Excel. Espero haber sido lo suficientemente explicativo y este artículo te haya ayudado. Si tiene alguna pregunta sobre este tema o cualquier otro tema de Excel / VBA. Hasta entonces sigue sobresaliendo.
Dividir números y texto de una cadena en Excel 2016 y versiones anteriores: Cuando no teníamos la función TEXTJOIN, usábamos las funciones IZQUIERDA y DERECHA con algunas otras funciones para dividir caracteres numéricos y no numéricos de una cadena.
Extraiga texto de una cadena en Excel usando la función IZQUIERDA y DERECHA de Excel: Para eliminar texto en Excel de la cadena, podemos usar la función IZQUIERDA y DERECHA de Excel. Estas funciones nos ayudan a cortar cadenas de forma dinámica.
Elimine los espacios iniciales y finales del texto en Excel: Los espacios iniciales y finales son difíciles de reconocer visualmente y pueden estropear sus datos. Eliminar estos caracteres de la cadena es una tarea básica y más importante en la limpieza de datos. Así es como puede hacerlo fácilmente en Excel.
Eliminar caracteres de la derecha: Para eliminar caracteres a la derecha de una cadena en Excel, usamos la función IZQUIERDA. Sí, la función IZQUIERDA. La función IZQUIERDA retiene el número dado de caracteres de IZQUIERDA y elimina todo de su derecha.
Eliminar caracteres no deseados en Excel: Para eliminar caracteres no deseados de una cadena en Excel, usamos la función SUSTITUIR. La función SUSTITUIR reemplaza los caracteres dados con otro carácter dado y produce una nueva cadena alterada.
Cómo eliminar texto en Excel comenzando desde una posición en Excel: Para eliminar texto de una posición inicial en una cadena, usamos la función REEMPLAZAR de Excel. Esta función nos ayuda a determinar la posición inicial y el número de caracteres a despojar.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo utilizar la función BUSCARV de Excel| Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar Excel Función COUNTIF| Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.