Remover letras y caracteres y dejar numeros

[gonzalo.valle 0]

Hola,

Alguno me podría ayudar con una formula para remover las letras y caracteres de una celda y que queden solo numeros?

Por ejemplo, necesito una formula para aplicar en una celda que contiene lo siguiente:

18008IC06001212L

Y que me devuelva lo siguiente:

1800806001212

 

[Cristian 1985 228]

Hola Adjunto una opción con una UDF.

Saludos!

Fuente

Foro.xlsm

[gonzalo.valle 0]

Gracias Cristian

Habra alguna opción sin VB?

Gracias!

[Snake 300]

Hola a ambos:

Dejo el adjunto con una fórmula que me encontré por ahí.. y que estoy tratando de entender exactamente como hace lo que hace.. jaja

Saludos.

 

 

SoloNumeros.xlsx

[Cristian 1985 228]

Sería como dice Snake o una super requete recontra larga fórmula como

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A2;"A";"");"B";"");"C";"");"D";"");"E";"");"F";"");"G";"");"H";"");"I";"");"J";"");"K";"");"L";"");"M";"");"N";"");"Ñ";"");"O";"");"P";"");"Q";"");"R";"");"S";"");"T";"");"U";"");"V";"");"W";"");"X";"");"Y";"");"Z";"")

[gonzalo.valle 0]

FENOMENAL!

Muchas gracias Snake!

[johnmpl 2,434]

¡Hola, a todos!

Dejo otra opción con fórmula matricial en el adjunto.  ¡Bendiciones!

SoloNumeros.xlsx

[Cristian 1985 228]

Hace 12 horas, johnmpl dijo:

¡Hola, a todos!

Dejo otra opción con fórmula matricial en el adjunto.  ¡Bendiciones!

SoloNumeros.xlsx

Hola Jhon, no entiendo por que agregas la función VNA (valor neto) en esa fórmula jajajaja

[Silvia 1,809]

hola a todos.. 

_________________________________

 

Hace 2 horas, Cristian 1985 dijo:

Hola Jhon, no entiendo por que agregas la función VNA (valor neto) en esa fórmula jajajaja 

1. Cuando utilizó extraer, dejó toda la extracción volteada.

así:  {"L";"2";"1";"2";"1";"0";"0";"6";"0";"C";"I";"8";"0";"0";"8";"1"}

2. Luego, al agregar porcentaje, la dejó con errores donde habían letras

{#¡VALOR!;0.02;0.01;0.02;0.01;0;0;0.06;0;#¡VALOR!;#¡VALOR!;0.08;0;0;0.08;0.01}

3. Con  si. error, acomodó los errores y le quedó así

{"";0.02;0.01;0.02;0.01;0;0;0.06;0;"";"";0.08;0;0;0.08;0.01}

como ves, además de estar volteado, esta multiplicado por % 

4 y para corregir eso, utiliza vna:

1800806001212.01

5 y para corregir el decimal, utilizó Entero.

6. y por si acaso, si se pasa del largo numerico aceptable, (supongo que por eso), le agregó 

&""

para convertirlo en texto..

 

a ver John.. interpreté bien? 

o mal?  

saludos,

 

 

[Silvia 1,809]

Hace 35 minutos , Silvia dijo:

4 y para corregir eso, utiliza vna:

1800806001212.01

......y la clave para que funcione vna es  -0.9

[Silvia 1,809]

ya lo confundí a Snake 

[Snake 300]

Hasta me mareé

 

[JSDJSD 319]

Otra opción con macros

Discriminar números de texto.xlsb

[johnmpl 2,434]

¡Hola, a todos!

Explicaré el por qué el uso de la fórmula con un ejemplo más pequeño.  Al entenderse, puede ser extrapolable a un conjunto de caracteres un poco más grande (hasta 15 dígitos, límite numérico de Excel).

Una cosa importante a tener en cuenta es que, en mi sistema, mi separador decimal es la coma ( , ) ; mi separador de argumentos es el punto y coma ( ; ), y mi separador de constante matricial de filas también es el punto y coma ( ; ).

Esta es la fórmula completa, la cual separo por colores para entender un poco su razón de ser cuando se desmenuce, por partes:

= ENTERO(VNA(-0,9;;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;"")))&""

 

La idea inicial es formar los números adecuados para que la función VNA haga el trabajo importante: armar el conglomerado numérico. Estos números deben cumplir con dos condiciones:

• Armarse de forma invertida en su conglomerado.
• Estar divididos por 100.

¿La razón? se explicará cuando se vea la parte de VNA.

 

Para el ejemplo explicado, tendremos el texto en la celda A2, y este será: 1X43YZ2

Hay que saber que la tecla F9, lo que hace es evaluar una fórmula o una referencia dentro de una fórmula en Excel.  Si se seleccionan partes de la fórmula, y se presiona F9, se observarán todas las constantes matrices y/o resultados que se evidenciarán en la explicación que haré a continuación.  Vale la pena aclarar que se debe seleccionar de forma correcta cada parte (número de paréntesis correcto, sobre todo), puesto que si no la evaluación generará un error.

Empezando de adentro hacia afuera, empezamos por la parte azul:

= FILA(A$1:INDICE($A:$A;LARGO(A2)))

Esta parte genera una constante matricial de filas. Cabe notar que este tipo de matrices suele construirse con la combinación de FILA(INDIRECTO(...)), o también con FILA(DESREF(...)) pero, por la volatilidad de INDIRECTO y DESREF, prefiero evitarlas.

= FILA(A$1:INDICE($A:$A;LARGO(A2)))

= {1;2;3;4;5;6;7}

 

Necesitamos invertir esta matriz en su orden.  Para hacerlo, lo combinamos con la función LARGO y sumamos 1:

= LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1

= 7 - {1;2;3;4;5;6;7} + 1

= {6;5;4;3;2;1;0} + 1

= {7;6;5;4;3;2;1}

 

Ahora, realizamos la extracción de cada caracter del texto, y para eso, usamos la función EXTRAE:

= EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)

= EXTRAE(A2;{7;6;5;4;3;2;1};1)

= {"2";"Z";"Y";"3";"4";"X";"1"}

 

Seguido a esto, como necesitamos que cada número quede dividido por 100, lo que podemos hacer es usar el símbolo %, el cual se puede también interpretar como un operador, el cual, lo que hace es dividir por 100.  Hay que entender algunas cosas aquí:

• La matriz anterior está comprendida por textos, puesto que cada elemento está entre comillas
• Cualquier operación matemática realizada a un texto:  Si es un texto numérico, lo convierte en número; mientras que si es un texto, genera un error #¡VALOR!.

Usando entonces el operador %:

= EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%

= EXTRAE(A2;{7;6;5;4;3;2;1};1)%

= {"2";"Z";"Y";"3";"4";"X";"1"}%

= {0,02;#¡VALOR!;#¡VALOR!;0,03;0,04;#¡VALOR!;0,01}

 

Envolviendo esta matriz con la función SI.ERROR, nos encargamos que esos errores #¡VALOR! se conviertan en cadenas de texto vacías.

= SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;"")

= SI.ERROR({0,02;#¡VALOR!;#¡VALOR!;0,03;0,04;#¡VALOR!;0,01};"")

= {0,02;"";"";0,03;0,04;"";0,01}

 

Ahora bien, viene la parte de VNA, el cual es el que hace la magia:  Esta es una función financiera, y aunque lo que vamos a hacer no tiene que ver con finanzas, con un artilugio hace lo que necesitamos: Unir solo los números de una cadena de texto.

La función VNA (Valor Neto Actual o Valor Neto Presente), tiene como fórmula de ejecución la siguiente:

Siendo:

vi = Cada valor a operar

t = tasa de interés

i = período evaluado

n = cantidad de valores a sumar

 

Sintaxis de la función VNA:

=VNA(tasa;valor1;[valor2];...)

En nuestra función:

= VNA(-0,9;;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;""))

• la tasa es -0,9. Este valor ha sido escogido estratégicamente para nuestro propósito.
• el valor 1 es 0 (el hecho de dejar un argumento vacío de esta forma, es lo mismo que si hubiese un cero ahí).  Para verlo mejor, comento que:

Esto:

= VNA(-0,9;;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;""))

Es igual a esto:

= VNA(-0,9;0;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;""))

¿Por qué dejar el 0 en el primer valor? esto es para que los cálculos inicien desde el segundo valor, es decir, que la i inicialmente valga 2.  Esto hará que el primer número sea entero, y los demás múltiplos de 10 al elevar la potencia.

• el valor 2 se compone de la constante matricial armada, la cual fue:

= {0,02;"";"";0,03;0,04;"";0,01}

Ahora bien, una cosa a tener en cuenta es que la función VNA ignora los textos, solo trabaja con los números que encuentre. Por lo que de ahora en adelante, podemos tener en cuenta nuestra constante matricial como:

= {0,02;0,03;0,04;0,01}

Entonces, siguiendo la fórmula previamente enseñada, tenemos que:

Que es el valor requerido.

 

¿Por qué usar la función Entero?

= ENTERO(VNA(-0,9;;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;"")))

En ocasiones, cuando se hacen operaciones con bastantes números, aparece lo que se llama en Excel la aritmética de punto flotante, la cual puede generar resultados no deseados.  Si se quiere profundizar sobre el tema, ver el siguiente link:

https://support.microsoft.com/es-co/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

 

¿Por qué concatenar una cadena de texto vacía al final? 

= ENTERO(VNA(-0,9;;SI.ERROR(EXTRAE(A2;LARGO(A2)-FILA(A$1:INDICE($A:$A;LARGO(A2)))+1;1)%;"")))&""

Esto se hace para evitar que Excel convierta el resultado en notación científica de forma automática cuando se realiza la fórmula.

 

Finalmente, hay que tener en cuenta que la fórmula es matricial CSE, por lo que se debe introducir con la combinación de teclas Ctrl + Shift + Enter, en vez de Enter o Ctrl + Enter.

Espero esta laaaaarga explicación ayude a los amantes de las fórmulas (como yo) a entender varios conceptos "formulianos" interesantes para diversos problemas.  ¡Bendiciones a todos!

 

[Snake 300]

Ya tengo tema para estudiar el fin de semana.. y yo que me quería ir de fiesta!!  jajaja

Gracias @johnmpl

 

[johnmpl 2,434]

Hace 45 minutos , Snake dijo:

Ya tengo tema para estudiar el fin de semana.. y yo que me quería ir de fiesta!! 

Siento arruinar tu finde... sorry... 

Ahora, para dar otra opción sin la limitante de 15 dígitos, teniendo office 365 (o el nuevo 2019 en su versión de prueba), se puede usar (matricial CSE también):

=CONCAT(SI.ERROR(--EXTRAE(A2;FILA(A1:INDICE(A:A;LARGO(A2)));1);""))

Ver adjunto con la aplicación de las dos opciones.  ¡Bendiciones!

SoloNumeros.xlsx

[Silvia 1,809]

Dos millones de gracias John..

 

[Cristian 1985 228]

Muy agradecido John, muy correcta tu explicación

[Gerson Pineda 3,650]

En 30/8/2018 at 10:38 , gonzalo.valle dijo:

Hola,

Alguno me podría ayudar con una formula para remover las letras y caracteres de una celda y que queden solo numeros?

Por ejemplo, necesito una formula para aplicar en una celda que contiene lo siguiente:

18008IC06001212L

Y que me devuelva lo siguiente:

1800806001212

 

Hola a todos!

Y para no quedarme fuera de la foto... una opción mas con Power Query [si, si ya se, que era con formulas, pero quien sabe y son muchos datos o de pronto en futuro cercano, todos utilicemos Power Query / BI]

 

Saludos a todos [y feliz finde!]

Remover caracteres Power Query_GP.rar

[johnmpl 2,434]

¡Hola de nuevo, a todos!

@Gerson Pineda, Por supuesto, PowerQuery es una gran herramienta para trabajar.  Dejo otra opción con esta herramienta.  ¡Saludos y Bendiciones!

Remover caracteres PQ _ JJVD.xlsx

[Gerson Pineda 3,650]

@johnmpl buen truco lo de la abecedario!

 

Saludos