sábado, marzo 17, 2007

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

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:

8 comentarios:

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

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

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

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

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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))

    ResponderBorrar
  6. graciassssssssssss desde ampuero diciembre 2009

    ResponderBorrar
  7. Buenas,

    Muchas gracias.

    ¿Y cómo se haría lo contrario? Es decir pasar de un número por ejemplo -45 a 45-

    Gracias.

    ResponderBorrar
  8. En tu caso tendrás que usar formato numérico personalizado (fijate en esta nota.

    El código a usar sería

    #,##0;#,##0-;0

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.