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í
