Extraer caracteres numéricos de celda

Hola.

Si tenemos la necesidad de extraer los números de una celda que está computas por números y letras como el siguiente ejemplo:

Lo que debemos hacer es crear la siguiente función:

Public Function ExtraeNumeros(celda)
   Dim n As Long, c As String, r As String
   For n = 1 To Len(celda)
      c = Mid(celda, n, 1)
      Select Case c
      Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
         r = r & c
      End Select
   Next
   ExtraeNumeros = r
End Function

Quedando de la siguiente manera:

Saludos

Juan A.

Listar Carpetas, Archivos, SubCarpetas y Archivos de las SubCarpetas

Alguna vez has necesitado hacer un listado de todos los archivos que tienes en una carpeta?

Si tienes esto:

Imagen 1

y pasarlo para ver esto:

Imagen 2

Comparto una macro creada por Hector Miguel.

En un módulo normal vas a pegar el siguiente código. Posteriormente en la celda A1 ingresas la ruta por ejemplo teniendo la carpeta como se observa en la imagen 1 escribimos: D:\Datos Juan A


Sub Lista_de_archivos()
  Application.ScreenUpdating = False
  Dim Carpeta As String: Carpeta = Range("A1"): Cells.Clear
  Range("a2:e2") = Array("Ruta", "Nombre", "Tamaño", "Modificado", "Tipo")
  Listar_archivos_en Carpeta, True
End Sub

Sub Listar_archivos_en(Carpeta As String, Completo As Boolean)
  Dim Archivo, SubCarpeta, Fila As Long
  Fila = Range("a65536").End(xlUp).Row + 1
  With CreateObject("scripting.filesystemobject")
    With .GetFolder(Carpeta)
      For Each Archivo In .Files
        With Archivo
          Range("a" & Fila & ":e" & Fila) = Array( _
            Application.Substitute(.Path, .Name, ""), .Name, .Size, .DateLastModified, .Type)
        End With
        Fila = Fila + 1
      Next
      If Completo Then
        For Each SubCarpeta In .SubFolders
          Listar_archivos_en SubCarpeta.Path, True
        Next
      End If
    End With
  End With
  Range("a1:e1").EntireColumn.AutoFit
  Range("a1") = Carpeta
  Debug.Print ActiveSheet.UsedRange.Address
End Sub

Ejecutamos la macro Lista_de_archivos y listo, el resultado será tal como se ve en la imagen 2.

Saludos

Juan Alejandro

Abrir archivo separado por comas

En alguna oportunidad les ha pasado que al exportar una tabla a Excel desde una herramienta, se abre Excel pero la tabla que visualizamos está separada por comas en una misma columna, es decir, el texto no está separado por columnas.

lo ideal sería que al realizar la exportación, se pudiera visualizar la información por columnas y no tener que acceder a la opción de Excel "Texto a Columnas" así:

Para lo anterior, solo debemos ingresar al panel de control, configuración regional y actualizar el símbolo decimal y el símbolo de grupo

Configuración Actual (No adecuada)

Configuración Nueva (Adecuada)

Esto es todo.

Saludos

Jalejop

Función ÍNDICE en Excel

La función índice en Excel, poco conocida pero de mucha ayuda en algunos casos, aunque por sí sola no luce muy útil, pero combinada con otras permite automatizar un importante número de tareas en Excel.

 

La función INDICE permite extraer un valor ubicado en una celda determinada de un rango de datos o base de datos.

 

Para ello debemos indicarle a Excel el número de la columna y el número de la fila, y Excel traerá el valor que se encuentra en la intersección de las dos.

 

Supongamos la siguiente base de datos

 

F/C	D	E
1	Nombre	Sueldo
2	Alfonso	1,500,000
3	Timoteo	1,200,000
4	Humberto	900,000
5	Ernesto	900,000
6	Carlos	600,000
7	Clara	700,000
8	Erika	550,000
9	Tomas	1,000,000
10	Marta	600,000

 

Tenemos una base de datos entre los rangos D2 y E10 (D2:E10).

 

Necesitamos ahora traer el valor que se encuentre entre la fila 7 y la columna 2 de la base de datos. La fórmula sería:

 

=INDICE(D2:E10;7;2)

 

El resultado debe ser 550.000 que corresponde a Erika, puesto que ese valor se encuentra en la intersección de la fila 7 de la base de datos con la columna 2 de la base de datos.

 

Nótese que no hablamos de la columna A, B, C, etc., sino del número de la columna que conforman la base de datos. Es nuestro ejemplo nuestra base de datos está conformada por 2 columnas, y no importa en qué columnas de Excel se encuentre la base de datos. Igual sucede con las filas, pues no se cuentan las filas de la hoja de Excel, sino las filas que componen la base de datos.

 

Obsérvese que aunque Erika se encuentra en la fila 8 de Excel, está realmente en la fila 7 de la base de datos como tal. Y aunque está en la columna E, dentro de la base de datos está en la columna 2.

 

La primera parte de la fórmula indica el rango donde está la base de datos. El primer criterio corresponde al número de la fila y el segundo criterio corresponde al número de la columna. El escribir la fórmula Excel le irá indicando los valores a introducir.

Función INDIRECTO y COINCIDIR

Quiero dejar un ejemplo del uso de estas dos funciones en Excel que son faciles de manejar.

Primero la definición:

INDIRECTO

La función INDIRECTO (INDIRECT en inglés), es bastante rara.  Es muy difícil identificar para qué la usarías … hasta que la necesitas.
Su función, según la ayuda de Excel es: “Devuelve la referencia especificada por una cadena de texto.”

veamos los parámetros

INDIRECTO(Referencia; [opcional: Tipo de Referencia])

Donde:

Referencia: Texto de una referencia del tipo A1 o R1C1 (es decir, con la “dirección” de la celda.
Tipo: Valor lógico que indica el tipo de referencia:

VERDADERO u omitido: Referencia del tipo A1
FALSO: Referencia del tipo R1C1
También se pueden usar “nombres” de rangos.  Es en estos casos donde yo he obtenido mayor provecho de esta función.

-----------------------------------------------------------------------------------------------------------------------

COINCIDIR

"Esta función nos devuelve la posición que ocupa un valor determinado dentro de una matriz de datos".

COINCIDIR(Valor buscado; Matriz datos;Tipo de coincidencia)

En Valor buscado, escribiremos el valor o la celda que contenga el valor que deseamos buscar dentro de un grupo de celdas que especificaremos en Matriz datos.

En tipo de coincidencia tenemos tres posibilidades.

- Pondremos 1, para encontrar el valor que sea inferior o igual al que indicamos en Valor buscado. El listado deberá estar ordenado de menor a mayor.

- Pondremos 0, para encontrar el primer valor de la matriz que sea igual al valor que estamos buscando. En esta ocasión el listado puede estar desordenado.

- Pondremos -1, para encontrar el valor que sea mayor o igual al que indicamos en Valor buscado. El listado, deberá estar ordenado de mayor a menor.
-----------------------------------------------------------------------------------------------------------------------

Ahora veamos un ejemplo:

Vamos a utilizar la función COINCIDIR para buscar un valor en una matriz.

Buscamos en la tabla, donde esta “Ana”.  La función COINCIDIR/MATCH, devuelve la fila en que encuentra el datos buscado.

Si a la tabla anterior, le adiciono otra columna con el salario, yo sabiendo que en la columna L esta el dato que necesito, lo puedo concatenar con la fila para obtener dicho dato. Vemos:

Utilizamos la función INDIRECTO para buscar una referencia.  En este caso vamos a buscar el valor de la celda L5.

 

 

EXCEL - Función ESPACIOS o TRIM no funciona

Alguna vez, les ha pasado que en Excel, cuando aplicamos la formula TRIM (ESPACIOS en español) no funciona? ¿Revisamos la barra de formula e identificamos que hay varios espacios los cuales debería quitar nuestra formula?

Si observas la gráfica anterior, se aplicó la formula TRIM a la columna A y los espacios continúan apareciendo. 

Resulta que el caracter que tú crees que es un espacio, no lo es es.  Es otro caracter, para que lo compruebes.  Teniendo la imagen anterior,  en la celda C1, digita: =MID(A1,1,1) ahora ve a la celda D1 y digitas : =CODE(C1).

Notaras que en D1 aparece un 160, esto es parte de la tabla extendida de códigos ASCII, el cual es un caracter no imprimible, por lo cual parece que es un espacio, pero no lo es; por ello la función TRIM no te funciona.

Ahora bien, en B1 pon esta fórmula:

=SUBSTITUTE(A1;$C$1;"")

Con esto le estamos indicando que sustituya de nuestra cadena [A1] el caracter [$C$1], el cual ya habíamos extraído previamente, por nada [""].

Nota:

¿Por qué en la formula en el parámetro “Old:text” debemos poner “$C$1” y no un espacio (“ “)? Porque va a sustituir el carácter que encontró y no un espacio. El código ASCII del espacio es el 32, no el 160, que es en este caso el que está acompañando los nombre de la columna A.

Saludos

jalejop

Formulas en Excel: Inglés - Español

Cuando trabajamos con Microsoft Excel, nos aprendemos un montón de formulas, pero cuando te toca crear una formula y la versión que tienes instalada no es igual a la que ya te acostumbraste vienen los problemas.  Para los que se les presente este problema, quiero compartir esta tabla donde pueden buscar sus formulas y así tener su equivalente.

Ver Tabla

Novedades de Excel 2010

Aunque Excel 2007 introdujo algunos cambios radicales vs. su versión anterior. Parece ser que Excel 2010 ofrecerá en su mayoría mejoras y tan sólo unos pocos cambios redicales. Así los usuarios de Excel 2007 tendrán un a fácil transición a Excel 2010.

Microsoft está liberando a cuenta gotas la información sobre Excel 2010, y estas son algunas de imágenes e información que se ha filtrado del nuevo software, mietras se realiza su lanzamiento oficial:

Figura 1: Presentación de Excel 2010.

En esta primera imagen podemos ver que el enorme botón de Office ha sido reemplazado por un rectángulo situado a la izquierda de la Pestaña “Home(Inicio)“ botón verde, como se muestra en la Figura 1.

Según vemos la disposición de las barras de herramientas permanecen idénticas a las del 2007, a excepción de la de “Add-Ins(Agregar)”. Esto significa que usted puede insertar pestañas personalizadas o desactivar cualquiera de las pestañas que hace parte de la barra de herramientas, esto permite personalizar Excel 2010 para satisfacer sus necesidades propias. No se preocupe: es fácil restablecer la barra a su estado por defecto cuando sea necesario

Información del libro se muestra en la Figura 2 en donde se presenta una gran cantidad de información acerca de las características del documento.

Figura 2: La nueva ficha Información proporciona características del documento

En la Figura 3, se muestra algo bien interesante, gráficos en las celdas llamados Sparklines ó minigráficas. Excel 2010 soportará de forma predeterminada este tipo de gráfica. que proporcionan una nueva forma de visualizar los datos. En las versiones anteriores Excel 2003 y 2007, visualizar gráficos de los datos de las celdas pero creando un objeto separado no como elementos de la hoja de calculo.

Figura 3: Excel 2010 le permite crear gráficos en las celda conocidos como Sparklines

Otra característica es que sproporciona una interfaz visual para el filtrado de la tabla pivote y otros datos de la base de datos. En lugar de utilizar las tradicionales listas desplegables para limitar los datos que se muestran en una tabla dinámica, un nuevo mecanismo gráfico para filtrar los datos.

Excel 2010 tiene un utilidad para realizar “Captura/ Recorte de pantalla” de modo directo característica que ayudará salvar todo o parte de la hoja como una imagen.

Y como se muestra en la Figura 4, el comando Imprimir ha sido modificado para fusionar la vista previa de impresión y opciones de configuración de página en el proceso de impresión.

Figura 4: Imprimir, Vista Previa, Configuración de Página se han fusionado en una sola pantalla.

Si el ordenador se reinicia mientras tiene abierto un documento de Excel, Excel 2010 se inicia automáticamente en el arranque, y los intentos de recuperar automáticamente los documentos que estaban trabajando.

Una nueva característica aparece en el menú desplegable del botón derecho para celdas, la funcion de “Pegar” contiene nuevas opciones, como se muestra en la Figura 5.

Figura 5: Nuevos iconos para Pegado especial en menú desplegable de cada celda.

Una nueva característica es que guarda de modo automático una copia de los documentos no guardados por un máximo de 4 días. Esto para el proceso de Autorrecuperación. Si inadvertidamente se cierra sin guardar un documento en el que has estado trabajando durante 10 minutos o más, puedes facilmente recuperar tu trabajo.

Fuente: Actualicese.com - Vía: accountingweb.com