Corrigiendo la posición del signo menos en números importados

sábado, marzo 17, 2007

Una tarea bastante corriente en Excel es importar datos de fuentes externas, como sistemas ERP (SAP, Oracle,etc) o bases de datos. Suele suceder que al importar los datos a Excel los números negativos aparezcan con el signo menos a la derecha del número; por ejemplo 550-, en lugar de -550.
El problema en estos casos no es tanto la estética como el hecho que Excel no interpreta estos datos como números, sino como texto.
Mostraremos como corregir esta situación con una fórmula.

Supongamos que hemos importado esta corregir signo menoslista de un sistema externo



Si agregamos en la columna B la fórmula =ESNUMERO() vemos que aquellos con el signo menos a la derecha son considerados por Excel como texto.



En la celda B2 ponemos esta fórmula

=VALOR(SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2))

el resultado es el valor corregido



Pasemos a explicar como funciona esta fórmula, lo que hacemos de "adentro" hacia "afuera".

ESPACIOS(A2) todo lo que hace es asegurarnos que no hay espacios en blanco después del signo menos.

DERECHA(ESPACIOS(A2);1)&A2 extrae el signo menos a la derecha del número y "lo pone" por delante de él. El resultado de esta fórmula es que el signo menos aparece por delante y por detrás del número



SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2) sustituye el segundo signo menos en -5119- por un blanco, con lo que obtenemos el resultado deseado.

Finalmente usamos la función VALOR para que el resultado de SUSTITUIR sea numérico.

Esta fórmula tiene un serio problema. Si la copiamos a lo largo del rango de números a corregir, los números positivos serán deformados



Esto se soluciona agregando una función SI a nuestra fórmula

=SI(DERECHA(ESPACIOS(A3);1)="-";VALOR(SUSTITUIR(DERECHA(ESPACIOS(A3);1)&A3;"-";"";2));A3)

Lo que hace la fórmula es evaluar si el valor en la celda a corregir tiene un signo "-" a la derecha; si lo tiene usa la fórmula; si no, copia la celda como tal.



Si no queremos usar columnas auxiliares, podemos usar esta Macro Corregir signo menosmacro

Sub sustituir_menos()
Dim Celda As Range

For Each Celda In Selection
If Trim(Right(Celda, 1)) = "-" Then
Celda.Value = Val("-" & Left(Celda, Len(Celda) - 1))
End If
Next

End Sub

Para que esta macro esté siempre disponible hay que copiarla a un módulo del Personal.xls usando el editor Vba.



Technorati Tags:

6 comments:

Anónimo,  17 marzo, 2007 22:32  

Muchas gracias por la entrada. Me es especialmente util cunado importo movimientos bancarios desde Internet.

Jaizki 17 marzo, 2007 23:30  

Otra fórmula para hacer lo mismo, quizás un poco menos complicada, podría ser:

=-1*IZQUIERDA(A1;LARGO(ESPACIOS(A1))-1)

Jaizki 17 marzo, 2007 23:31  

El -1* se puede dejar en un simple -.

Jorge L. Dunkelman 18 marzo, 2007 08:47  

Hola jaizki

gracias por los comentarios.

"podría ser:

=-1*IZQUIERDA(A1;LARGO(ESPACIOS(A1))-1)"


de hecho es la que uso en la macro.

Jaizki 18 marzo, 2007 13:05  

Cierto, no me había dado cuenta.

Por cierto, me parece que en la macro falta un Trim. Creo que debería ser:

celda.value = left(celda(len(trim(celda))-1))

Anónimo,  20 diciembre, 2009 23:11  

graciassssssssssss desde ampuero diciembre 2009

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP