Jump to content

Remover letras y caracteres y dejar numeros

gonzalo.valle

By gonzalo.valle,
in Temas resueltos

Recommended Posts

Cristian 1985

Cristian 1985 306

Posted
Posted

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:

Link to post
Share on other sites
Silvia

Silvia 1,914

Posted
Posted

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,

 

 

Link to post
Share on other sites
John Jairo V

John Jairo V 2,596

Posted
Posted

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

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!

 

Link to post
Share on other sites
John Jairo V

John Jairo V 2,596

Posted
Posted
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

Link to post
Share on other sites
Gerson Pineda

Gerson Pineda 3,916

Posted
Posted
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

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Guest
This topic is now closed to further replies.
Go to topic listing

  • Crear macros Excel

  • Posts

    • Antoni
      Macro para utilizar VLOOKUP entre dos hojas dentro del mismo libro

      By Antoni · Posted

      Private Sub CommandButton1_Click() Dim Celda As Range, x As Long Application.ScreenUpdating = False For x = 2 To Range("A" & Rows.Count).End(xlUp).Row Range("K" & x) = "" Set Celda = Sheets("Sheet2").Columns("A").Find(Range("A" & x), , , xlWhole) If Not Celda Is Nothing Then Range("K" & x) = Celda.Offset(0, 1) Next End Sub  
    • Sergio
      obtener texto de web con macro

      By Sergio · Posted

      Hola @Galactico, ¿has probado a extraer el texto de la etiqueta "a"? Y si ha sido así, ¿qué resultado has obtenido?
    • Galactico
      obtener texto de web con macro

      By Galactico · Posted

      buenas noches, estoy trabajando en una macro para obtener datos web, de a poco he ido aprendiendo como poder obtener objetos desde las diferentes paginas pero ahora he llegado a un punto donde no puedo seguir avanzando, precisamente con lo siguiente   tengo este código de pagina fuente <a href="www.google.com" data-original-title="" title=""> <span itemprop="category">Hola</span> "Adios" </a> con el siguiente código htmlDeRespuesta.getElementsByTagName("span")(0).innerText puedo obtener el texto Hola, pero no he logrado dar con la solución para obtener el dato Adios   por favor agradecería mucho su apoyo y gracias por el espacio   saludos!
    • ALBER2021
      convertir NÚMEROS a LETRAS

      By ALBER2021 · Posted

      hola Sergio. Gracias por tu respuesta. Pues algo así también he pensado. traducir las diferentes cantidades e ir cambiándolas en la macro saludos
    • Gerson Pineda
      Control de ventas.

      By Gerson Pineda · Posted

      Pues creo que si hay algo... aunque si ya lo hiciste, pues no estaría mal que lo compartas   Saludos!

  • Recently Browsing

    No registered users viewing this page.

×
×
  • Create New...

Important Information

Privacy Policy

  I accept