Transformar números en forma de texto a valores numéricos en Excel

lunes, julio 05, 2010

Esta nota ha sido corregida a partir de las observaciones de mis lectores

Podría empezar esta nota con el conocido adagio "basado en hechos reales". Sucede que no existe una convención internacional sobre separadores de decimales y de miles. En Argentina y España por ejemplo, se usa punto para separar los miles y la coma para separar los decimales. Pero en otros países, como México, Puerto Rico y Estados Unidos, se usa la coma para separar los miles y el punto para los decimales.
Cuando introducimos un valor en una celda, Excel interpreta si es número o texto de acuerdo a las definiciones regionales




Como se puede apreciar, en Argentina se usa el punto para separar los miles y en México la coma.

Si un usuario en la Argentina recibe un cuaderno Excel hecho en México, Excel "traduce" el número de un sistema al otro. Los problemas empiezan cuando los datos son enviados de México a la Argentina en formato .txt o, peor aún, .csv.

Por ejemplo, sin un usuario mexicano envía un archivo .csv donde aparece el valor "123,456.78", que para él es un número, al abrir el archivo con las definiciones regionales de la Argentina, Excel lo transformará en texto.

En esta nota veremos cómo solucionar el problema con funciones Excel y, por supuesto, con UDF (funciones definidas por el usuario, macros).

En esta tabla,



los valores en la columna A son texto; en la columna B san sido transformados a números con esta fórmula

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

Vamos a explicar cómo funciona esta fórmula pasa por paso.


El primer paso consiste en reemplazar todas las comas que pueda haber en el texto por puntos. Esto lo hacemos en la columna B (Auxiliar 1) con la fórmula

=SUSTITUIR(A2;",";".")

Luego creamos una cadena sin separadores, en la columna C, con

=SUSTITUIR(B2;".";"")

Comparando el largo de las cadenas en B y C podemos saber cuántos separadores (puntos y comas) hay en la cadena con esta fórmula, lo que hacemos en la columna D

=LARGO(B2)-LARGO(C2)+ESERROR(HALLAR(".";A2))

Usamos la expresión =ESERROR(HALLAR(".";A2)) para agregar un 1 en caso que el "texto" no contenga una parte decimal

Este dato nos será útil en la columna E, para reemplazar el último punto en la cadena por el separador de decimales, la coma

=VALOR(SUSTITUIR(B2;".";",";D2))

Como se puede apreciar, también combinamos la función VALOR, para convertir la cadena de texto resultante de usar SUSTITUIR en un valor numérico.

En la columna F reemplazamos todas las fórmulas intermedias por referencias a la celda que contiene la cadena de texto (la columna A), resultando nuestra "mega-fórmula"

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

En definitiva, podemos usar esta fórmula sin todos los pasos intermedios.

También podemos incluir la fórmula en un nombre, lo que la hará más legible





Function TextNum_to_Num(strNumText As String, sepDec)
   
    Dim numInt As String 'la parte entera del numero
    Dim numDec As String  'la parte decimal del numero
    Dim arrNumTemp() As String
    Dim sepMiles
   
    If sepDec = "." Then
        sepMiles = ","
    Else
        sepMiles = "."
    End If
   
   
    arrNumTemp = Split(strNumText, sepDec, -1)
    numInt = WorksheetFunction.Substitute(arrNumTemp(0), sepMiles, "")
   
    Select Case WorksheetFunction.CountA(arrNumTemp)
        Case Is = 1
            TextNum_to_Num = Val(numInt)
        Case Else
            numDec = arrNumTemp(1)
            TextNum_to_Num = Val(numInt & sepDec & numDec)
    End Select
   
End Function


Los argumentos de esta función son



  • strNumtoText: la celda que contiene que la cadena de texto que queremos convertir en número
  • sepDec: el separador de decimales usado en la cadena de texto (distinto del usado en las definiciones regionales).

El archivo con los ejemplos y el código de la función se puede descargar aquí

16 comments:

Die 06 julio, 2010 04:39  

Muy interesante, el método.
Aunque, al menos en Excel 2007, se puede hacer esto utilizando el asistente para importaciones de datos. Si uno selecciona Datos-->Obtener datos externos-->texto, aparece el asistente para importar. Seleccionamos el delimitador de texto (espacio o el símbolo que sea), ponemos siguiente y hacemos click en la columna que contenga los números. Seleccionamos el botón Avanzado... y podemos indicar a Excel que el delimitador de decimales o el separador de miles es diferente al usado localmente.
Luego, cuando damos a siguiente, los números aparecen como en la configuración local.

Jorge L. Dunkelman 06 julio, 2010 12:22  

Debido a un inconveniente en Blogger no puedo por ahora publicar los comentarios a esta nota.
Espero que la gente de Google solucione el problema en las próximas horas.

Jim McLean 06 julio, 2010 15:47  

Jorge,
Una alternativa interesante sería utilizar la función suma:
'123 aplicamos =SUMA(a1+0)
'123.4 aplicamos =SUMA((SUSTITUIR(a1;".";",")+0))
.......
Saludos

carolsurz 06 julio, 2010 19:22  

Una pregunta que me surge (seguramente no me he fijado bien, pero la planteo):
Si por ejemplo tienes el guarismo en Mexicano:
123,456,789,915
Si haces las operaciones de las columnas auxiliares, al hacer la resta de largos te va a dar 3 que son las comas que hay; sin embargo en la columna E, se pondría una coma de decimal y, en este caso no valdría ya que son todos indicadores de miles; es decir según como lo propones quedaría en la columna E 123456789,915
No sé si me he explicado muy bien....

Jorge L. Dunkelman 06 julio, 2010 20:58  

Carolsurz,
efectivamente, el error es mío. Se me pasó por alto que todos los números en forma de texto tienen que tener también la parte decimal. Es decir, tu ejemplo funciona si el "texto" es 123,456,789,915.00
La función UDF funciona correctamente.

Jorge L. Dunkelman 06 julio, 2010 21:00  

Die,
también en Excel Clásico existe esa posibilidad. Durante el proceso de importación es la mejor solución.

Jorge L. Dunkelman 06 julio, 2010 21:20  

Siguiendo con la observación de carolsurz, he corregido la fórmula.
La fórmula auxiliar 3 (en la columna D de la hoja "columnas auxiliares") debe ser

=LARGO(B2)-LARGO(C2)+ESERROR(HALLAR(".";A2))

La fórmula definitiva es

=VALOR(SI((LARGO(SUSTITUIR(A6;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A6;",";".");".";"")))=0;SUSTITUIR(A6;",";".");(SUSTITUIR(SUSTITUIR(A6;",";".");".";",";LARGO(SUSTITUIR(A6;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A6;",";".");".";""))+ESERROR(HALLAR(".";A6))))))

Una explicación ampliada irá en el blog.

Jorge L. Dunkelman 07 julio, 2010 00:25  

Finalmente he reescrito la entrada. La fórmula final (espero) es más compacta que la del comentario anterior.
Gracias por las observaciones.

carolsurz 07 julio, 2010 17:31  

Muchas gracias Jorge por corregirlo, aunque desde luego se entiende mejor la fórmula por pasos, que la Mega-Fórmula.
Otra pregunta, lo de sumar (creía que sólo servía en las multiplicaciones) un dígito a "Verdadero" o "Falso", ¿sólo funciona con estas dos palabras, aunque las escribas tu "manualmente" y no sean el resultado de una fórmula?

Jorge L. Dunkelman 07 julio, 2010 20:23  

Así es, las expresiones VERDADERO y FALSO son interpretadas (o "traducidas") por Excel a 1 y 0 respectivamente y se pueden usar en todas las operaciones.
Al introducir la palabra "verdadero", así en minúsculas, en una celda, al apretar Enter Excel la transforma a VERDADERO (en mayúsculas) indicando que la intepreta como valor boleano.

Anónimo,  24 junio, 2011 03:08  

Creo que otra manera más fácil es buscar y reemplazar, primero la coma por un caracter comodin y después el punto por la coma y el caracter comodin por el punto.
Se le aplica el formato de número deseado y el resultado es el mismo

Hada Heavy sin cuento 25 noviembre, 2011 23:51  

como siempre un genio mi kerido jorge.. gracias por tus conocimientos.. este post.. me ayudo muchisimo.. jo.. oseaa ya se un pokin mas. jejeje

Mianamay 12 enero, 2012 15:41  

Hola Jorge...tengo un pequeño problema que espero me ayudes a solucionar. Tengo unas celdas en las cuales los usuarios deben introducir valores unicamente en saltos de 0,5, es decir 1,0 o 1,5 o 2,0 pero nunca 1,3 o 1,8....hay alguna manera a traves de la validación de datos para limitar el ingreso de los datos de esta manera?
Mil gracias

Jorge L. Dunkelman 13 enero, 2012 11:01  

En validación de datos, en la opción Personalizada, puedes usar

=O((A1-ENTERO(A1))=0;(A1-ENTERO(A1))=0,5)

o mejor aún (mas breve)

=RESIDUO(A1;0,5)=0

Kunnington 17 setiembre, 2012 16:17  

Hola Jorge, es muy interesante su blog. En verdad se aprende mucho.

Respecto de este problema, ¿no sería más sencillo primero sustituir las comas por espacios en blanco y después el punto por coma?. Algo así

=Valor(Sustituir(Sustituir(A2;",";"");".";","))

Se llega al mismo resultado. No sé qué inconvenientes tendría usar esta fórmula.


File 02 diciembre, 2012 11:23  

Muchas gracias por el Post. Llevo varios días peleando con este problema, ya que no podía graficar unos datos. Saludos

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP