Jump to content
Sign in to follow this  
pegones1

Separar texto alfanumérico en celdas con el separador personalizado

Recommended Posts

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:

294kvif.jpg

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.

Share this post


Link to post
Share on other sites

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. :mad:

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

Share this post


Link to post
Share on other sites
Guest 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.

gracias.gif

Saludos, Luis.

SepararTextoAlfanumerico.zip

Share this post


Link to post
Share on other sites

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.

ejm_1-480x369.jpg

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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! :mad:

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 :mad: (por cierto, tu solución es muy ingeniosa :o)

=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 :P

SepararTextoAlfanumerico2.zip

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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! :rolleyes:

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!!! :mad:

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

Responsable: Sergio Andrés Celemín

Finalidad: Moderar y responder comentarios de usuarios. Recuerda que la información que facilites es pública, y los datos que incluyas los leerá cualquier visitante de esta web, así como el avatar que poseas.

Legitimación: Consentimiento del interesado.

Destinatarios: Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso,
rectificación, supresión, oposición y demás derechos legalmente establecidos a
través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Sign in to follow this  



×
×
  • Create New...

Important Information

Privacy Policy