El misterioso caso de los números cambiantes (Excel y las definiciones regionales)

En algún post del pasado he mencionado que, en ciertas circunstancias, Excel tiende a ser más "amable" de lo deseado. En su afán de ayudarnos Excel toma decisiones y transforma datos a medida que los vamos ingresando en una celda. Por ejemplo, si ingresamos el valor 15/9 Excel supondrá que se trata de la fecha 15 de setiembre y agregará el año corriente resultando 15/09/2017.

Esta característica de Excel conlleva problemas potencialmente graves. El resultado de la transformación está ligada a las definiciones regionales del sistema (Windows). Por ejemplo, si las definiciones regionales de nuestro sistema son las de España e ingresamos 10/9 Excel lo transformará en 10/09/2017 de acuerdo al patrón de la definición (día/mes/año)

Pero si nuestras definiciones regionales son las de Estados Unidos

si bien veremos en la celda 10/9/2017, de acuerdo al patrón mes/día/año, la fecha ingresada es el 9 de octubre.

El problema no está limitado solamente a las fechas. Supongamos que nuestro sistema tiene definiciones regionales donde la coma es el separador de miles y el punto de los decimales. Ahora copiamos este valor de la tasa GBP/USD (libra esterlina / dólar americano) y lo pegamos en una celda de Excel

El resultado será el siguiente

El valor fue copiado de un sitio español donde el separador de decimales es la coma; pero en mi sistema la coma es el separador de miles. Si nos fijamos veremos que en la barra de las fórmulas aparece 13845 (trece mil ochocientos cuarenta y cinco) y en la celda Excel ha aplicado el formato de número.
¿Cómo solucionamos este problema? Si se trata de un valor podemos hacer un clic en el icono de las opciones de pegado y usar el asistente para importar texto

Vamos directamente al paso 3

y cambiamos en Avanzadas las definiciones de separador de decimal y separador de miles. Ésto solucionará el problema.

¿Y si se trata de una tabla con muchos valores? Lo solucionamos con la opción Texto en Columnas (Datos-Herramientas de Datos) o con Power Query, por supuesto, como mostraré en una próxima nota.

Excel y el problema de los separadores (definiciones regionales)

En más de una oportunidad a lo largo de la historia de este blog me he topado con el problema de los separadores y las definiciones regionales. En ciertos países se usa la coma para separar los miles  y el punto para los decimales mientras que en otros el uso se invierte.
Lo mismo sucede con los separadores de filas y columnas que usamos en las constantes matriciales. Si nos atenemos a la ayuda de Excel usaremos la coma para crear una constante matricial orientada horizontalmente

y el punto y coma para una orientada verticalmente

Sin embargo, en ciertas definiciones regionales, Excel usa el caracter "\" como separador.

Para saber qué caracteres usa Excel para los distintos separadores podemos usar una macro para exponer los valores de la propiedad Application.International. Esta tabla muestra los índices de los distitntos valores

El código para mostrar los valores en un MessageBox es el siguiente

 Sub mostrar_separadores()  
 Dim strSep As String  
 With Application  
   strSep = "Separador de elementos de matriz alternativo =" & .International(xlAlternateArraySeparator) & vbCrLf  
   strSep = strSep & "Separador de Columna =" & .International(xlColumnSeparator) & vbCrLf  
   strSep = strSep & "Separador Decimal =" & .International(xlDecimalSeparator) & vbCrLf  
   strSep = strSep & "Separador de Lista =" & .International(xlListSeparator) & vbCrLf  
   strSep = strSep & "Separador de Fila =" & .International(xlRowSeparator) & vbCrLf  
   strSep = strSep & "Separador de Miles =" & .International(xlThousandsSeparator) & vbCrLf  
   MsgBox (strSep)  
 End With  
 End Sub  

Al correr el código veremos este mensaje