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 con nuevos juguetes, me refiero a funciones.
Excel 2019 y 365 introducen algunas funciones nuevas (TEXTJOIN y SEQUENCE) que pueden facilitar la tarea de eliminar caracteres numéricos y recuperar solo valores no 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(ISERROR(MEDIO(texto_desordenado,SECUENCIA(NumChars), 1) +0),MEDIO(texto_desordenado,SECUENCIA(NumChars), 1), ""))) |
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: elimine los caracteres numéricos y extraiga todos los alfabetos y caracteres no numéricos.
Así que aquí tenemos un texto alfanumérico. Este texto contiene algunos números y algunos caracteres no numéricos. Necesito deshacerme de los caracteres numéricos y obtener alfabetos y otros valores de caracteres únicamente, en la columna D.
No espero que el número total de caracteres en el texto mezclado sea más de 100. Por lo tanto, el valor de NumChars es 100 aquí. Puede aumentar o disminuir este número si lo necesita.
Aplique aquí la fórmula genérica anterior para eliminar los caracteres numéricos.
| =UNIRSE A TEXTO("",CIERTO,SI(ISERROR(MEDIO(B3,SECUENCIA(100),1)+0),MEDIO(B3,SECUENCIA(100),1),""))) |
Cuando presiona el botón Intro, se eliminan todos los caracteres numéricos y solo quedan los valores alfabéticos. Arrastre hacia abajo esta fórmula para eliminar los números de la cadena de todas las celdas de la columna B.
¿Como funciona?
Primero veamos cómo se resuelve esta fórmula paso a paso.
| 1-> TEXTJOIN("",CIERTO,SI(ISERROR(MEDIO(B3,SECUENCIA(100),1)+0),MEDIO(B3,SECUENCIA(100),1),""))) |
| 2-> UNIRSE A TEXTO("",CIERTO,SI(ISERROR(MEDIO("Este1 es … sitio",{1,2,3,… 100},1)+0),MEDIO(B3,SECUENCIA(100),1),""))) |
| 3-> UNIRSE A TEXTO("",CIERTO,SI(ISERROR({"T"; "h"; "i"; "s"; "", "1"… ""; ""}+0),MEDIO(B3,SECUENCIA(100),1),""))) |
| 4-> UNIRSE A TEXTO("",CIERTO,SI(ISERROR({# ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; 1…; # ¡VALOR!}),MEDIO(B3,SECUENCIA(100),1),""))) |
| 5-> UNIRSE A TEXTO("",CIERTO,SI({VERDADERO; VERDADERO; VERDADERO; VERDADERO; FALSO…; VERDADERO},MEDIO(B3,SECUENCIA(100),1),""))) |
| 6-> UNIRSE A TEXTO("",CIERTO,{"Esta";""… .;""}) |
| 7-> "Este es el sitio web número uno" |
Para facilitar la lectura, no he escrito toda la matriz. Usé puntos (…) para indicar matrices largas.
Como puede ver, la fórmula comienza a resolverse desde adentro. Al principio se resuelve la función SECUENCIA. Ya que hemos pasado 100 como número de caracteres. Devuelve una matriz de números que van del 1 al 100.
Esta matriz se sirve a la función MID como número inicial. La función mid va a cada índice en una cadena y divide cada carácter en una matriz. Puede verlo en el paso 3. No he mostrado toda la matriz ya que ocupará demasiado espacio. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
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. {# ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; 1…; # ¡VALOR!}
Esta matriz se sirve al ISERROR función. Como sabe, la función ESERROR devuelve VERDADERO para los errores y FALSO para los valores que no son de error. Por lo tanto, obtenemos una matriz de VERDADERO y FALSO. VERDADERO para caracteres no numéricos y FALSO para números. {VERDADERO; VERDADERO; VERDADERO; VERDADERO; FALSO…; VERDADERO}.
Lo mismo sucede con las declaraciones de la sección TRUE de IF (MEDIO(B3,SECUENCIA(100),1)). Devuelve una matriz de todos los caracteres de una cadena alfanumérica en B3.
Ahora, para cada declaración VERDADERA, la función SI devuelve el carácter correspondiente de la matriz de la sección verdadera. Para FALSO, SI devuelve un espacio en blanco (""). Ahora tendrá una matriz que no contiene ningún carácter numérico. {"Esta";""… .;""}.
Finalmente, esta matriz es servida por el UNIRSE A TEXTO función. Esta función une textos dados entre sí, ignorando los valores en blanco, con un delimitador dado. Por lo tanto, obtenemos una cadena que no contiene ningún carácter numérico. Este es el sitio web número uno.
Mejorando la Fórmula
La fórmula anterior usa un número codificado para procesar el número de caracteres (tomamos 100). Pero es posible que desee que sea dinámico. En ese caso, lo has adivinado bien, puedes usar la función LEN. Se necesitará el número exacto de caracteres para su procesamiento. Entonces la fórmula será.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(texto_desordenado), 1) +0), MID (texto_desordenado, SECUENCIA (LEN(texto_desordenado),1),""))) |
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, IF (ISERROR (MID (jumbled_text,HILERA(INDIRECTO("1:"&LEN(NumChars))), 1) +0), MID (texto_desordenado,HILERA(INDIRECTO("1:"&LEN(NumChars))),1),""))) |
El INDIRECTO convertirá el texto ("1: 100") en el rango real y luego la función FILA enumerará todos los números de fila del 1 al 100. (100 es solo un 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.
Cómo eliminar caracteres no numéricos de las celdas en Excel 2019: Para eliminar caracteres no numéricos de una cadena alfanumérica en Excel, usamos la nueva función TEXTJOIN. La eliminación de caracteres no numéricos de una cadena puede ayudarnos a limpiar nuestros datos para un mejor análisis de datos. Así que así es como lo haces
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.
