Separar texto alfanumérico en celdas con el separador personalizado

[pegones1]

En VBA y en otros lenguajes de programación es fácil separar texto alfanumérico, pero no lo es tanto usando las fórmulas de Excel.

Para preparar unos diagramas de Gantt en Excel necesitaba separar la lista de predecesores de una tarea representados como "6,10,24" y obtener los números separados por comas en otras celdas.

Después de buscar una solución, he intentado mejorarla para que sea más general y he obtenido el siguiente resultado:

En la columna B se define el tipo de separador: un carácter alfanumérico, por ejemplo la coma ","

En la columna C se define el texto a incluir cuando no hay texto, por defecto es un espacio en blanco " "

En el próximo mensaje enviaré el fichero Excel con la solución propuesta.

[pegones1]

Aquí está el fichero con la solución propuesta sin macros para separar texto alfanumérico en varias celdas:

SepararTextoAlfanumerico.xls

Lo he subido a mi SkyDrive porque sigo sin poder subir ficheros al foro.

Espero que algún "Super-Merodeador" me lo pueda subir

[Invitado luigi]

Hola:

Atendiendo a tú petición “Un Súper-Merodeador” te sube el archivo para que quede en el registro del Foro.

Aunque de momento no lo he probado, gracias por tu aporte, que seguro será importante para muchos de nosotros.

Saludos, Luis.

SepararTextoAlfanumerico.zip

[pegones1]

Concatenar texto alfanumérico es fácil pues existe la función CONCATENAR(texto1; [texto2]; ...) o también se puede utilizar el operador de cálculo símbolo (&) para unir elementos de texto.

Lo que no existe es una función para DESCONCATENAR texto en varias celdas, aunque Excel lo resuelve como cuando importa datos externos.

Si en la celda A1 se escribe el texto alfanumérico a separar, por ejemplo:

1,ab,33,cde,4444

Se borran las celdas B1:F1 y se selecciona la celda A1

En el menú Datos, hacer click en Texto en columnas

Se abre un Asistente para convertir texto en columnas, como se explica en esta página de Microsoft.

En el paso 1, elegir Delimitados

En el paso 2, marcar solo la Coma como separador

En el paso 3, cambiar el Destino a la celda B1 (normalmente machaca A1) y pulsar Finalizar.

En el rango B1:F1 estará el texto alfanumérico separado.

1 ab 33 cde 4444

Para entenderlo mejor lee el artículo Separacion de texto en Excellentias.com

[sailepaty]

Veamos si podemos incrementar la atención en este post y tenemos otras propuestas. Aquí van un par de opciones extra, una con una fila auxiliar y otra sin la auxiliar.

Saludos

SepararTextoAlfanumerico.zip

[Antoni]

Hola:

Siguiendo mi misión de evangelización por territorios "formulianos", os dejo una mini macro que sustituye a vuestras mas de 200 fórmulas.

Sub SepararTexto()

On Error Resume Next


Application.ScreenUpdating = False


If Selection.Columns.Count <> 1 Then Exit Sub

If Selection.Column <> 1 Then Exit Sub


For x = 1 To Selection.Rows.Count

   celdas = Split(Cells(x, 1), Cells(x, 2))

   y = 4

   For c = 0 To 14

      If celdas(c) = "" Then

         Cells(x, y) = Cells(x, 3)

      Else

         Cells(x, y) = celdas(c)

      End If

      y = y + 1

   Next c

Next x


End Sub

[/CODE]

¿ Porqué matar moscas a cañonazos cuando existe el insecticida ?.

Saludos. Antoni.

[sailepaty]

En VBA y en otros lenguajes de programación es fácil separar texto alfanumérico, pero no lo es tanto usando las fórmulas de Excel.

Antoni, me parece que el tema estaba claramente expuesto.

En adición no son mas de 200 formulas es una sola, y te apuesto que me tarde menos en escribirla que lo que tú te tardaste en crear y probar la macro. Ademas que al cambiar el usuario el separador y el auxiliar en caso de que la cadena sea de longitud cero, los resultados se modificaran automáticamente. Se que le podrías poner la macro de evento, que no funcionaria si el usuario no activa las macros.

En fin no se trata de cual arma es mejor si no cual es la adecuada.

Saludos

[Gerson Pineda]

De acuerdo con sailepaty

¿ Porqué matar moscas a cañonazos cuando existe el insecticida ?.

Antoni es que a algunos les gusta los cañonazos y a otros como tu el insecticida jejeje;), por cierto la macro quedaria como aporte

Saludos a todos

[Ajedrez]

Hola, y yo me quedo con las excelentes propuestas y soluciones de todos ustedes, son diversas formas de abordar/solucionar un problema que de entrada es facil de resolver con la opcion texto a columnas de Excel.

Buenas soluciones y buenas formulas.

Saludos.

[Antoni]

Jolín, que sentidos sois los "formulianos", os ofendeis enseguida, pero si en el fondo todos sabemos que os gustaría usar macros, ¡¡ Venga hombre !! animaros a usarlas, si es mucho mas sencillo de lo que parece, luego me lo agradecereis.

¡¡¡ Que nunca se pierda el sentido del humor !!! Saludoooooooooos a todoooooooooooos.

Macro Antonio.

[sailepaty]

Ningún sentimiento de ofensa por este lado Macro, si mi respuesta ha sido por el lado gentil y amable que tengo:confused:.

Te cuento que desde hace algunos años mi trabajo lo desarrollo esencialmente con macros. Sin embargo me di cuenta que no podía seguir así, tenía que hacer algo, o algo serio me podía suceder; tenía que ejercitar el cerebro! Es entonces que decidí que mis respuestas se deberían de basar en formulas.

Saludos

[Antoni]

Parece mentira el deterioro cerebral que se sufre con el paso de los años.

¿ No será un exceso de tequila ?, hazme caso, pásate a la cerveza y lo verás todo mas claro.....jajaja:rolleyes:

Un saludo muy cordial desde la tierra de las meigas.

Macro Antonio.

[pegones1]

Hola, y yo me quedo con las excelentes propuestas y soluciones de todos ustedes, son diversas formas de abordar/solucionar un problema que de entrada es facil de resolver con la opcion texto a columnas de Excel.

Buenas soluciones y buenas formulas.

Saludos.

fornelasa, la opción Texto en columnas de Excel, que expuse en el Post #4, no es interactiva y cada vez que se cambia el texto obliga a pasar por el Asistente para convertirlo...

[Ajedrez]

Hola pegones1, gracias por la aclaracion, entonces corrijo mi comentario:

yo me quedo con las excelentes propuestas y soluciones de todos ustedes, son diversas formas de abordar/solucionar un problema.

Buenas soluciones y buenas formulas y tambien en mi opinion un muy buen tema.

Saludos.

[pegones1]

Veamos si podemos incrementar la atención en este post y tenemos otras propuestas. Aquí van un par de opciones extra, una con una fila auxiliar y otra sin la auxiliar.

Saludos

sailepaty, veo que has captado la esencia de este tema, que no es otra que olvidarse de los algoritmos y usar las fórmulas de Excel para separar textos, como si existiese la función DESCONCATENAR en Excel ¡que no existe!

Como todo (o casi todo) se puede hacer de diversas maneras y gracias a tus ideas, propongo otra solución con una única fórmula y sin tener que repetir 500 veces el espacio en blanco (por cierto, tu solución es muy ingeniosa )

=SIERROR(MED($A1;ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";COLUMNAS($D1:D1)));1)&MED($A1;ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";COLUMNAS($D1:D1)))+1;ENCONTRAR("

";SUSTITUIR($A1&$B1;$B1;"

";COLUMNAS($D1:D1)))-ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";COLUMNAS($D1:D1)))-1);$C1)[/CODE]

NOTA: Se ha incluido a propósito, entre cada 2 dobles comillas, un salto de linea generado con la combinación de teclas [Alt]+[intro] porque no está incluido entre los caracteres del texto a separar.

Lo cierto es que se ha complicado bastante, para tener en cuenta la personalización del o los caracteres auxiliares, en caso de que la cadena resultante sea de longitud cero y ha obligado a llamar a la función SIERROR

SepararTextoAlfanumerico2.zip

[pegones1]

Para que la fórmula anterior del Post #15 sea independiente del número de columnas (entre la celda en la que se está separando el texto y la primera de ellas) hace falta una fila auxiliar con números enteros consecutivos (1, 2, 3, ..)

Si la fila auxiliar es la 18, la fórmula será:

=SIERROR(MED($A1;ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";D$18));1)&MED($A1;ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";D$18))+1;ENCONTRAR("

";SUSTITUIR($A1&$B1;$B1;"

";D$18))-ENCONTRAR("

";SUSTITUIR($B1&$A1;$B1;"

";D$18))-1);$C1)[/CODE]

La fila auxiliar hace las veces de argumento a la función:

[b]DESCONCATENAR(Texto; Número de celda a separar)[/b]

¡OJO función virtual no implementada en Excel 2007 ni Excel 2010!

Aunque la función completa sería algo así:

[b]DESCONCATENAR(Texto; Número de celda a separar; [separador]; [Auxiliar de relleno])[/b]

Siendo los valores opcionales por defecto:

Separador = , (coma)

Auxiliar de relleno = (espacio en blanco)

SepararTextoAlfanumerico2007.zip

[Ajedrez]

Hola, Para Antoni

Multiupload.com - upload your files to multiple file hosting sites!

[pegones1]

Hola:

Siguiendo mi misión de evangelización por territorios "formulianos", os dejo una mini macro que sustituye a vuestras mas de 200 fórmulas.

Sub SepararTexto()

On Error Resume Next


Application.ScreenUpdating = False


If Selection.Columns.Count <> 1 Then Exit Sub

If Selection.Column <> 1 Then Exit Sub


For x = 1 To Selection.Rows.Count

   celdas = Split(Cells(x, 1), Cells(x, 2))

   y = 4

   For c = 0 To 14

      If celdas(c) = "" Then

         Cells(x, y) = Cells(x, 3)

      Else

         Cells(x, y) = celdas(c)

      End If

      y = y + 1

   Next c

Next x


End Sub

[/CODE]

¿ Porqué matar moscas a cañonazos cuando existe el insecticida ?.

Saludos. Antoni.

Macro Antonio, las moscas son parte de la cadena alimenticia y pierden su valor al usar el insecticida ¡pobres de mis arañas!

Si me hubieras dado una macro mas general, no anclada a la fila 1, sería una solución pero así no me vale.

Un método más ecolológico, sin matar a nada ni a nadie, es usar una tela aislante que impida el paso de las moscas. Me explico, ¿por qué no crear una función?

[CODE]Function Desconcatenar(cadena, num, separador, relleno)

' Devuelve el elemento indicado por num desde una cadena de texto.
' Usa el caracter separador para separar elementos de la cadena de texto.
' Si la cadena es de longitud cero devuelve el relleno.
Dim x As Variant
x = Split(cadena, separador)
If num > 0 And num - 1 <= UBound(x) Then
Desconcatenar = x(num - 1)
Else
Desconcatenar = relleno
End If

End Function[/CODE]

Si los valores de las celdas son:

A1 = 4,66,4,667,3,66,9,456,2,98

B1 = ,

C1 =

La llamada a la función:

=Desconcatenar(A1;4;B1;C1)

daría: 667

Se la voy a proponer a Microsoft para que en la versión Excel 2011 esté implementada!!!

[Ajedrez]

Hola, parezco disco rayado pero la verdad este es un post fantastico por los aportes proporcionados.

Saludos.

[Antoni]

Hola:

Si me hubieras dado una macro mas general, no anclada a la fila 1, sería una solución pero así no me vale.

Me temo que va a ser que no. La macro corre correctamente para cualquier fila.

De hecho está adaptada a la primera imagen que proporcionaste, es decir, Texto en la columna 1, Separador en la columna 2, Auxiliar de relleno en la columna 3 y Resultados a partir de la columna 4.

La macro funciona con una selección de celdas de la columna 1.

fijate en la instrucción:

For x = 1 To Selection.Rows.Count[/CODE]

Hoy voy a ser bueno, y voy a colaborar con el "enemigo":

He hecho esta UDF con los parámetros que querías.

[CODE]Function DESCONCATENAR(Texto As Range, _
Posicion As Integer, _
Optional Separador As Variant = ",", _
Optional Auxiliar As Variant = "") As Variant
On Error GoTo Herror

If Texto.Columns.Count <> 1 Then Exit Function
Celdas = Split(Texto, Separador)
DESCONCATENAR = Celdas(Posicion - 1)
Exit Function

Herror:
DESCONCATENAR = Auxiliar
Resume Next
End Function
[/CODE]

[b]DESCONCATENAR(Texto; Número de celda a separar; [separador]; [Auxiliar de relleno])[/b]

Salu2. Macro Antonio.

PD: Me acabo de dar cuenta que ya habías desarrollado la UDF, pues ale, dos por el precio de una.

[pegones1]

Hola, parezco disco rayado pero la verdad este es un post fantastico por los aportes proporcionados.

Saludos.

Gracias fornelasa, la verdad es que con macros ya había un tema:

Macro para separar texto y valores en Distintas columnas

que Macro Antonio (hasta ayer conocido como Antoni Ayala) se encargó de resolver magistralmente, como siempre.

Lo que intento con este tema es ir por el camino de las fórmulas para quienes no tengan o no gusten de habilitar las macros...

[Ajedrez]

Hola, y bueno.....para mi aun mas genial porque yo no estoy peleado ni con las formulas ni con las macros, es decir, si es con formulas ¡fantastico!, y si es con macros pues..... igual de ¡fantastico!.

Saludos.

[Antoni]

No confundas fornelasa, aquí nadie se pelea con nadie, es simplemente para darle un poco de "vidilla" y humor a las cosas.

Una saludo para todos. Macro Antonio, anteriormente conocido por Antoni.

[Antoni]

Os podeis creer que no recuerdo este tema:

Macro para separar texto y valores en Distintas columnas

Y parece que participé y todo.

Cada día estoy peor.

[Gerson Pineda]

De acuerdo con fornelasa... y sigan rallando el disco jeje, que muchos seremos los beneficiados

Saludos