Etiqueta: Excel

Usar la misma celda en Excel al autocompletar una formula

Un truco simple, que muchos no saben en Excel para usar una celda fija a la hora de realizar muchas operaciones con un dato que varía y afecta a muchas celdas.

Leer más →

Export XML file from an Excel 2010 Worksheet

Is curious that we need to explain how to export an XML file from Excel, something that should be easy and painless… but reality again surprises us and im here explaining something that is very useful in development world and someone in Microsoft thinks that the best way to implement this function in excel is doing a lot of work, aparently, unneeded.

Sometimes, we will not use a Database to store information of our product, because sometimes the data is static, nothing enters and nothing leaves, so, why use conectors and queries if we just put everything in a XML file and later parse it to obtain the information we need?, the answer is obvious, but generate that XML file could be really painfull, even more, if all the static data is stored in a precious Excel Workbook.

If you search (like i do), you will notice that Microsoft and a lot of people suggest us to download some addin of Microsoft Excel 2003 and blablabla, but nothing about Office 2010, and, if you’re using a 64bit Windows, the super method of the addin will be useless, because the macro is not implemented for 64 bit operating systems.

Then, after all the introduction, let’s do the famous XML file from a Sheet (word-game here).
Leer más →

Convertir valor / texto a RUT (Chile) en Excel

Si se quisiera convertir el valor o texto: 154785219 a 15.478.521-9 se puede utilizar la siguiente función en Excel 2010 (o similar):

=CONCATENAR(TEXTO(MED(A1;1;LARGO(A1)-1);"#.#.#"); "-"; DERECHA(A1;1))

Todo esto, asumiendo que en A1 esta el numero plano y que la función la aplicamos en una celda distinta.

Ojo que funciona con RUTs que sean de mas o menos que 10 millones.

Actualizado 08 de junio, 2011

Para Excel 2007 o inferior, la función MED era conocida como EXTRAE, por lo que debe utilizarse:

=CONCATENAR(TEXTO(EXTRAE(A1;1;LARGO(A1)-1);"#.#.#"); "-"; DERECHA(A1;1))