Jump to content
gonzalo.valle

Remover letras y caracteres y dejar numeros

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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";"")

:ph34r:

Share this post


Link to post
Share on other sites

hola a todos.. :wub:

_________________________________

 

Hace 2 horas, Cristian 1985 dijo:

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

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"}

:blink:

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? :rolleyes:

o mal?  :wacko:

saludos,

 

 

Share this post


Link to post
Share on other sites

¡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:

image.png.c04624a89612421f8dc831d06ee44d7a.png

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 2Esto 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:

image.png.04e8e1c57812d6fab4effccd6a65ecf2.png

image.png.f3cb9e42c8c5decfb87ead3ef66487cb.png

image.png.9515c074b6c0f2b649e19ed6ca07de47.png

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!

 

Share this post


Link to post
Share on other sites
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... :D

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

Responsable: Sergio Andrés Celemín

Finalidad: Moderar y responder comentarios de usuarios. Recuerda que la información que facilites es pública, y los datos que incluyas los leerá cualquier visitante de esta web, así como el avatar que poseas.

Legitimación: Consentimiento del interesado.

Destinatarios: Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso,
rectificación, supresión, oposición y demás derechos legalmente establecidos a
través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.




×
×
  • Create New...

Important Information

Privacy Policy