miércoles, 15 de septiembre de 2010

La herramienta de Excel Texto en columnas.

Voy a dedicar algo de tiempo a explicar esta herramienta de Excel (Texto en columnas) tan útil cuando trabajamos con bases de datos en otros formatos. Aprovecharé el problema planteado por un usuario:

...como ordenar una lista que tiene en excel son al rededor de 20000 items pero no sabia usar el excel por ende escribia todo en la columna A y con la barra espaciadora le daba cierta distancia como para separarlo (OJALA HUBIERA SIDO TABULACION ASI ERA MAS FACIL PERO BUENO...).
La herramienta convertir texto en columnas me seria de gran utilidad si en vez de tomarme un espacio como un cambio de columna me tomara 3 espacios como cambio de columna pero solo me aparece la opcion de 1 espacio solo eso separa absolutamente todos los nombres, se puede modificar esta opcion?....


Resolveremos el caso empleando la utilidad del asunto.

Tenemos el siguiente listado... y es importante observar que en cada celda se han introducido lo que deberían ser distintos campos, empleando la barra espaciadora para separar éstos visualmente con tres espacios entre campos!!!:

La herramienta de Excel Texto en columnas.



Claro está que no podemos emplear directamente la herramienta Texto en columnas, ya que no existe un caracter de tabulación que separe los campos; por ello haremos un tratamiento previo. Empleando la herramienta de Reemplazar (Ctrl + l) en el Menú Inicio > Modificar > Buscar y seleccionar con el siguiente criterio:

La herramienta de Excel Texto en columnas.


reemplazamos 'tres espacios en blanco' por una 'barra vertical' (o cualquier otro caracter); con lo que logramos unificar nuestro listado para la aplicación de la herramienta Texto en columnas:

La herramienta de Excel Texto en columnas.



Ya podemos navegar por el menú Datos > Herramientas de datos > Texto en columnas hasta abrir un Asistente de tres pasos; con los datos seleccionados:
  1. Primer paso: definición de los datos y cómo están delimitados:

    La herramienta de Excel Texto en columnas.


  2. Segundo paso del asistente: estableceremos qué caracter se ha empleado como separador de campos en nuestro listado. Para nosotros ha sido la barra vertical '|'.

    La herramienta de Excel Texto en columnas.


  3. Tercer paso del asistente para convertir Texto en columnas: donde seleccionaremos y daremos si es necesario formato a cada columna resultante.

    La herramienta de Excel Texto en columnas.



El resultado final obtenido es el esperado, esto es, ahora tenemos en columnas diferentes cada uno de los campos:

La herramienta de Excel Texto en columnas.

27 comentarios:

  1. Salu2, gracias por la nota

    ResponderEliminar
  2. Excelente y simple solución. Muy útil.
    Gracias!!

    ResponderEliminar
  3. A veces, las cuestiones más dificiles se resuelven con las cosas más fáciles, solo es cuestión de saber utilizar la herramienta adecuada en cada momento.
    Muy bueno

    ResponderEliminar
  4. TENGO UNA RANGO DE CELDAS Y LA QUIERO COMPARAR CON OTRO RANGO ( TODO ES TEXTO, PAGADO CANCELADO) QUIERO QUE AL COMPARARSE, SI NO ES IGUAAL SE PONGA LA PALABRA CANCELADO Y SI ES IGUAL LA PLABRA PAGADO COMO LE HAGO?
    GRACIAS POR SU AYUDA

    ResponderEliminar
  5. Hola Gareth,
    supongo que te refieres a comparar celda a celda de cada rango ???
    Si es así, para cada registro a comparar tendrías que introducir un condicional SI
    =SI(celda_rango1=celda_rango2;"PAGADO";"CANCELADO")

    Si te refieres a comparar ambos rangos en conjunto (con todos sus elementos uno a uno), habría que saber si pueden o no estar dispuestos en el mismo orden, etc... pero probablemente se requiera una macro.

    Slds

    ResponderEliminar
  6. Como hago para volver a dejar todo junto cuando ya utilice esa funcion con anterioridad

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      una vez aplicada esta herramienta de Texto en columanas sólo sería posible volver a 'juntarlo' todo mediante una fórmula de CONCATENAR en una columna auxiliar.

      Un cordial saludo

      Eliminar
  7. la celda que requiero separar en columnas tiene enter en cada linea, como lo puedo separar en columnas, ya intente eliminar espacios no tuve exito

    ResponderEliminar
  8. la celda que requiero separar en columnas tiene enter en cada linea, como lo puedo separar en columnas, ya intente eliminar espacios no tuve exito, y son varias celdas para hacerlo manual

    Ejemplo de texto de la celda,
    1. Pronóstico de fectivo para atm's y sucursales
    2. Sistema nacional de dictámenes
    3. Aplicación de ráfagas automáticas de depositos de clientes

    ResponderEliminar
    Respuestas
    1. Hola, que tal!?
      Entiendo por lo que dices que sólo existe un Salto de línea en cada celda de esa columna.
      Con la herramienta Texto en columnas no podrás hacer mucho en este caso...
      Una solución rápida sería con la función EXTRAE y ENCONTRAR.
      Supongamos que las celdas están en el rango A1:A3, entonces en C1 escribes:
      =ENCONTRAR(CARACTER(10);A1)
      en D1:
      =EXTRAE(A1;1;C1-1)
      y en E1:
      =EXTRAE(A1;C1+1;LARGO(A1)-C1)

      De esta manera en D obtienes la primera parte del Salto y en E la segunda.
      Espero te sirva.
      Slds

      Eliminar
  9. muchas gracias me ha servido de masiado, Adicionalmente agradezco el doble el apoyo. Es la primera vez que me contestan en un blog, por lo general envio dudas pero no obtengo respuesta. Saludos desde Monterrey. Atte Irasema Ibarra

    ResponderEliminar
    Respuestas
    1. Gracias a ti,
      me alegro haberte podido ser útil.
      Slds cordiales

      Eliminar
  10. Hola Buenas Tardes.
    Como puedo hacer para optimizar la captura de datos en excel.
    Actualmente se tiene una base con clientes con sus datos generales, y adicionalmente se tienen que capturar importes y observaciones por 6 personas dependiendo el cliente que este a su carfo.

    La problematica que existe actualmente es: el archivo se vuelve lento o no puede ser usado al mismo tiempo por 2 o mas personas.

    Estoy pensando en un formulario pero no se como empezar.
    Mi idea es: que les muestre una pantalla para que agreguen los datos, sin necesidad de estar directamente en la BD

    Atte: xiia

    ResponderEliminar
    Respuestas
    1. Hola Xiia,
      según interpreto, y como tu has indicado, estás trabajando con una base de datos, por lo que te recomendaría abandonaras Excel y cambiaras a Access, que en ningún caso te generará estos problemas de los que hablas...
      No olviddemos nunca que Excel es 'sólo' una hoja de cálculo.

      Con Access no tendrás problemas al trabajar en multiusuario, y desde luego, desaparecerán esos problemas de lentitud. Me imagino que esos problemas se te generan en Excel por el alto volumen de datos relacionados entre sí mediante fórmulas tipo BUSCARV o similares...

      Slds cordiales

      Eliminar
  11. Gracias Ismael
    el acces es la opcion mas viable, pero resulta que en el trabajo el uso e acces es por lincencia. La prouesta que tengo es la siguiente:
    Tener un boton de consulta en Excel hacia la BD de Acces y que muestre la informacion.
    y tener otro boton que permita guardar la informacion en Acces

    Tengo parte del codigo, pero desconozco cual es la intruccion para conexion a acces ya que esta hecho para consulta en sql
    Sub Botón1_Haga_clic_en()

    Dim cnnConexion As ADODB.Connection
    Dim rstOrigen As ADODB.Recordset
    Dim CmdComando As ADODB.Command
    Dim Contador As Integer
    Dim strSQL As String

    Set cnnConexion = New ADODB.Connection

    cnnConexion.ConnectionString = strSQL = "C:\Documents and Settings\BD_clientes.accdb"
    cnnConexion.Open

    Set CmdComando = New ADODB.Command
    CmdComando.ActiveConnection = cnnConexion
    CmdComando.CommandType = adCmdText
    CmdComando.CommandTimeout = 180

    Contador = 3
    Do While Worksheets("hoja1").Cells(Contador, 1) <> ""
    strSQL = Worksheets("hoja1").Cells(Contador, 1) & "'"
    CmdComando.CommandText = "SELECT tipo_aclaracion, Trimestre, NOlinea, Quién_asigna, Folio, Resolucion, Causa_Resolución, Seguimiento FROM QN_xiia WHERE Folio = " & strSQL
    Set rstOrigen = CmdComando.Execute()
    If Not rstOrigen.EOF Then
    Worksheets("hoja1").Cells(Contador, 2) = rstOrigen.Fields("tipo_aclaracion")
    Worksheets("hoja1").Cells(Contador, 3) = rstOrigen.Fields("Trimestre")
    Worksheets("hoja1").Cells(Contador, 4) = rstOrigen.Fields("NOlinea")
    Worksheets("hoja1").Cells(Contador, 5) = rstOrigen.Fields("Quién_asigna")
    Worksheets("hoja1").Cells(Contador, 6) = rstOrigen.Fields("Folio")
    Worksheets("hoja1").Cells(Contador, 54) = rstOrigen.Fields("Resolucion")
    Worksheets("hoja1").Cells(Contador, 55) = rstOrigen.Fields("Causa_Resolución")
    Worksheets("hoja1").Cells(Contador, 56) = rstOrigen.Fields("Seguimiento")

    End If
    Contador = Contador + 1
    Loop

    cnnConexion.Close


    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Xiia,
      el código que adjuntas parece correcto a vista de pájaro...
      lo que no tengo seguro es que el acceso a través de VBA a la base de datos no 'consuma' una licencia de Access (que entiendo es el problema), ya que en el fondo estás accediendo a la tabla 'QN_xiia' de tu BD Access 'BD_clientes.accdb'
      ¿Lo has probado y funciona??

      Slds

      Eliminar
  12. Aun no lo valido, porque la conexion a la BD de acces, me marca error al ejecutar el codigo.
    Aun no encuentro como es la instruccion.
    El script que te envie marca error. Sigo investigando

    ResponderEliminar
    Respuestas
    1. Hola Xiia,
      ¿has activado la referencia de 'Micorosoft Access 14.0 Object Library' y Microsoft 'ActiveX Data Objects x.x Library' en el Editor de VB en Herramientas > Referencias.
      Echa un vistazo a
      http://support.microsoft.com/kb/308047/es

      Quizá sea esto.
      Slds

      Eliminar
  13. Respuestas
    1. Hola Sebastián,
      puedes plantearla a través de los comentarios o envíandome un mail a:
      excelforo@gmail.com

      Slds

      Eliminar
  14. Hola yo debo imporatar varios datos desde otras tablas y los pego en una sola columna, luego proceso a dividir la comuna en tres, mi problema es que cuando vuelvo a pegar (importar) otra serie de datos, excel los pega en tres colmunas separadas y no en una como lo necestio para la mi primera columna. Si cierro excel, esto desaparece, y puedo pegarlos en una sola comuna y luego divirlos en tres.
    alguien me puede ayudar con esto?

    Victoria

    ResponderEliminar
    Respuestas
    1. Hola Victoria,
      quizá la solución sea importar los datos ya divididos.
      pero, claro, dependerá del tipo de información y el origen...

      Si especificas algo más al respecto, quizá te podría dar más señas.

      Saludos

      Eliminar
  15. Ismael muchas gracias por tu pronta respuesta.
    Los datos desde la tabla que los importo estan ya agrupados en una sola columna y pense en luego dividirlos. La planilla que estoy generando es compleja pues estos datos son variables en cantidad algunos tienen mas datos otros menos (ejemplo es como tener nombres de personas, algunos de ellos tendran 4 datos otros dos, y otros 6, como no simpre son 4, la planilla que estoy generando los divide en 4 columnas de lo contrario en agunos caso tendira mucha columnas, y esta planilla es muy grande con otros campos), por ello pense en dividirlos en tres columnas que son la mayoria de los casos y el resto se ingresaria de modo manual, es decir sin dividirlos en columnas, pero ahora no se si es el medio mas óptimo.

    ResponderEliminar
    Respuestas
    1. Probablemente la herramienta Texto en columnas, en ese caso, no se la mejor alternativa...
      Intenta aplicar en la importación la función Split
      (http://excelforo.blogspot.com.es/2011/11/vba-la-funcion-split-en-una-macro-de.html)
      para dividir los datos...

      Espero te resulte mejor.
      Saludos

      Eliminar
  16. Hola Ismael, espero te campoAncuentres bien.

    Tengo un archivo .txt con tres lineas que contienen datos separados por una barra (|) el cual estoy abriendo con Excel para separarlos en columnas. Ejemplo:

    Primera línea: 1 | 2 | 3 | 4 |
    Segunda línea: Nombre | Apellido | Dirección | Teléfono
    Tercera línea: Pedro | Perez | Av este con calle norte apt 46 Distrito metropolitano | 5554332

    Pero al abrirlo con Excel y utilizar la función de texto a columna, utilizando como delimitador la barra (|) todo queda bien menos la dirección. Que coloca sólo las primeras dos palabras correctamente y el resto de la dirección la coloca en las líneas siguientes. He intentado colocar la dirección entre comillas pero no logro que toda la dirección quede junta en la misma columna.

    Agradezco sí puedes darme alguna idea de cual es el problema.

    De antemano gracias por tu amable atención.

    Saludos
    Yoberly

    ResponderEliminar
    Respuestas
    1. Hola Yoberly,
      diría que el problema está en el .txt, que se haya incluido en lugar de espacio un caracter de salto de linea... tendría que ver el fichero de texto...
      yo abriría el .txt (con el bloc de notas, por ejemplo) y vería como me aparecen las líneas dispuestas...
      Siento no poder decirte mucho más
      Un cordial saludo

      Eliminar