texto a numero con excel, cambiando el separador decimal

Este feedback realmente me gusta, alentándome en la tarea de continuar con mi blog. Hace unos minutos le avisé a mi lectora Graciela (link a la entrada sobre su primer duda) y enseguida me contestó x mail y realizando una entrada en el blog, con otra consulta. Y es sobre algo muy muy común el el mundo de la migración de archivos de texto a Excel: el formato de los números.

Cada PC, y mas si estamos hablando de usuarios de distintas partes del mundo, tiene su propia configuración regional (a la cual accedemos desde el Panel de Control). Allí indicamos zonas, fechas.... y el formato que llevarán los números: que caracteres se encargarán de separar los miles y los decimales. Aquí, para Excel, el punto fundamental es detectar correctamente el separador de decimales, ya que el de miles es una cuestión de formatos y no afecta al número en sí. Y salvo que indiquemos lo contrario, hay dos alternativas: el punto o la coma. En mi PC tengo la coma. Vean la siguiente imagen:

[+/-] Ver el resto / Ocultar

Esto nos trae acarreado un problema elemental: con el valor ingresado en A1 será imposible efectuar operaciones matemáticas, ya que ese punto ( . ) separador de miles convierte al valor en texto.

Graciela está migrando los datos de un txt, cuyo origen probable sean los registros obtenidos de una base de datos (access, mysql, etc, etc), y este tipo de aplicaciones por lo general nos traen estos inconvenientes, mas aún si fueron diseñadas en países con una configuración distinta a la que utilizamos.

Existen muchas formas de arreglar esto. Mi lectora ya me truncó una: no podemos hacer uso de Macros. Y entiendo esta petición, dado que en muchos casos en nuestras oficinas no se ejecutan (x controles de seguridad) o bien los usuarios finales conocen poco acerca de Macros y las desactivan cuando abren el libro, logrando con esto inutilizar todo nuestro código.

Se me ocurren dos formas, para contestarte rápido y que puedas aplicar los ejemplos:

1) si son miles y miles de registros las funciones seguro ralentizarán el desempeño de Excel. Por cuanto hacés lo siguiente: a) seleccionás toda la columna con los datos mal configurados. b) presiónas Ctrol + L (buscar y reemplazar)

Lo principal aquí es tener bien en claro que vamos a reemplazar, y en que orden. Primero: quitaremos las comas ( , ) que separan los miles. Ingresás la coma en el cuadro "Buscar" a "Reemplazar con" lo dejás en blanco. Aquí le estás ordenando que busque todas las comas y las reemplace con "" (nada), o sea que en realidad las estamos eliminando. Ahora presionamos el botón "Reemplazar todos", que nos arrojará el siguiente resultado:

las comas fueron eliminadas.

Pero... ese punto separador de decimales está mal. Vamos a repetir el proceso anterior, pero ahora reemplazando los puntos ( . ) por comas ( , ):

Y el resultado:

tan tan. a simple vista, por la alineación que Excel le dió a los valores, nos damos cuenta que los valores de texto del comienzo ahora son números.

Creo que es la mejor y mas rápida solución a tu planteo. Igualmente ahora brindaré otro método, que viene de la mano de la función:

=SUSTITUIR(texto, texto original, texto nuevo, [numero de ocurrencia])

Explicacion:

texto: cadena de caracteres o referencia a la celda en donde está el texto que deseamos modificar.

texto original: el texto que deseamos cambiar

texto nuevo: el texto que vamos a incorporar al primer argumento

numero de ocurrencia: [opcional] especifica la aparicion del texto original que se desea reemplazar. si se omite, texto original será reemplazado en todos los lugares donde aparece.

Paso a paso, y dejando atrás esas explicaciones que siempre son necesarias y se entienden poco, vamos a los ejemplos. En la columna B reemplazo a la coma ( , ) por "" (nada):

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

En la columna C cambiaremos los puntos por las comas:

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

el trabajo listo... pero algo no funciona bien. en C13 ingresé la funcion SUMA() y ahí detecto que Excel me sigue tomando a los registros como texto, a pesar del correcto separador de miles.

Y para solucionar lo visto en la imagen anterior, nada mejor que la función VALOR(), que nos convierte un dato de texto (siempre y cuando tenga el formato correcto) en numero. Así que solo nos resta:

=VALOR(C2)

ahora sí está todo OK. otra forma sería multiplicar la columna C por 1, obteniendo el mismo resultado.

Una vez que comprendiste el paso a paso (sinceramente espero haber sido claro), podemos dejarnos de "molestar" con tantas columnas auxiliares y resumir todo en una:

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

Que hice: anidé dos funciones SUSTITUIR(). En Excel, te comento x si no estás al tanto, las funciones se leen "de adentro hacia afuera". Esto quiere decir que primero se ejecutarán las funciones que se encuentren en el centro del anidado. Y así "saiendo" hasta llegar a la que englobe todas las fórmulas.

El primer SUSTITUIR() me cambia los decimales por nada y a ese resultado (sin comas) lo paso como primer argumento de la segunda SUSTITUIR(), para que cambie los puntos por comas. Y termino cerrando el negocio conviertiendo con VALOR() el dato obtenido.

Sin macros, con funciones o bien con el comando "buscar y reemplazar" creo estar con una respuesta acorde a tu requerimiento. Espero que te sea de utilidad. Ya te mando un mail así tu celular te avisa y ves el post. Saliendo un poco de Excel.... que celular tenés?