2 formas de ordenar los valores de una celda según un carácter delimitador

Una de las tareas más cotidianas y una de las que se aprenden nada más comenzar a usar Excel es la de ordenar alfabéticamente (o numéricamente) los valores de un rango de celdas. Puedes usar la herramienta Ordenar y filtrar o, si dispones de Microsoft 365, puedes usar directamente las funciones ORDENAR y ORDENARPOR.

Una tarea menos frecuente es la de ordenar los valores de una celda cuando estos se encuentran delimitados por un carácter (por ejemplo una coma).

Un Exceler preguntó hace unos días en el foro de Ayuda Excel sobre cómo podía realizar esta tarea. Él se dedica al control de calidad de una empresa textil y recibe la siguiente información de una máquina productora:

La consulta que hizo en el foro fue sobre si había alguna forma de poder ordenar alfabéticamente los valores de cada celda para tener un mayor control de las piezas de tela que cortaba la máquina.

Ya sabes que mi máxima es “En Excel siempre hay varias formas de hacer una misma tarea”. Por mi parte propuse una solución con una fórmula y otro usuario del foro aportó una solución mediante una macro.

¿Las vemos?

Ordenar valores de una celda con una fórmula

Aprovechando que los valores estaban separados por una coma (y que nuestro Exceler usaba Microsoft 365) le propuse la siguiente fórmula:

=TRANSPONER(ORDENAR(XMLFILTRO("<b><a>"&SUSTITUIR(A2;",";"</a><a>")&"</a></b>";"//a")))

La explico paso por paso:

Se sustituyen las comas por etiquetas XML

En la ficha de la función SUSTITUIR explico que esta función sirve para sustituir (obviamente ????) una cadena de caracteres por otra. Es decir,

SUSTITUIR(A2;",";"</a><a>")

Se convierte en:

=SUSTITUIR("A,I,G,C,F,G,A,G,F,I,K,H,G,H,F";",";"</a><a>")

Obteniendo como resultado:

A</a><a>I</a><a>G</a><a>C</a><a>F</a><a>G</a><a>A</a><a>G</a><a>F</a><a>I</a><a>K</a><a>H</a><a>G</a><a>H</a><a>F

Se agregan etiquetas XML al principio y al final de la cadena de texto

Si te fijas, el primer valor no tiene etiqueta XML de apertura y el último valor no tiene etiqueta de cierre, se las agrego:

"<b><a>"&SUSTITUIR(A2;",";"</a><a>")&"</a></b>"

Esta parte de la fórmula devuelve:

<b><a>A</a><a>I</a><a>G</a><a>C</a><a>F</a><a>G</a><a>A</a><a>G</a><a>F</a><a>I</a><a>K</a><a>H</a><a>G</a><a>H</a><a>F</a></b>

Importante: la etiqueta <b> abre y cierra el conjunto de caracteres y la etiqueta <a> cada uno de los elementos de forma individual.

Extraer datos XML

A continuación se extraen los valores de la cadena en formato XML. La fórmula

XMLFILTRO("<b><a>"&SUSTITUIR(A2;",";"</a><a>")&"</a></b>";"//a")

Se convierte en una matriz:

{"A";"I";"G";"C";"F";"G";"A";"G";"F";"I";"K";"H";"G";"H";"F"}

Y muestra los valores de forma vertical como se ve en la siguiente imagen:

Ordenar los valores de la matriz

A continuación se ordenan los valores de la matriz anterior. La fórmula

ORDENAR(XMLFILTRO("<b><a>"&SUSTITUIR(A2;",";"</a><a>")&"</a></b>";"//a"))

Devuelve la matriz de valores ordenada en una sola columna pero todavía no es suficiente.

Transponer la matriz

Para obtener el resultado de todas las celdas es necesario que la función devuelva la matriz de forma horizontal para que el resultado de una fórmula no invada el espacio de otra. Para esto se usa la función TRANSPONER:

=TRANSPONER(ORDENAR(XMLFILTRO("<b><a>"&SUSTITUIR(A2;",";"</a><a>")&"</a></b>";"//a")))

El resultado final de esta fórmula es el siguiente:

Si fuese necesario cambiar el formato del resultado para que coincida con el de los datos originales podrías usar la función UNIRCADENAS de esta forma:

=UNIRCADENAS(",";;B2#)

Si no sabes qué significa la almohadilla (#) detrás de la referencia de la celda, te recomiendo que eches un vistazo a la publicación El operador de rango derramado.

Ordenar valores de una celda con una macro

En el mismo tema del foro otro usuario aportó una macro magistral que devolvía el mismo resultado que la fórmula. Pero esta vez lo hacía en las mismas celdas donde se encontraban los valores originales.

La macro solicitaba el rango de celdas donde se encontraban los valores a ordenar. A continuación pedía el carácter delimitador y automáticamente hacía la ordenación.

Atención: Antes de ejecutar la macro asegúrate de que el rango de celdas introducido es realmente el que quieres ordenar. No selecciones nunca una columna completa o una fila completa. La macro te bloqueará el archivo (avisado quedas ????).

Sub OrdenarValores()
 

Dim rng As Range
Dim cell As Range
Dim del As String
Dim Arr As Variant
 

On Error Resume Next
 

Set rng = Application.InputBox(Prompt:="Selecciona el rango de celdas:", _
Title:="Ordenar valores de una celda", _
Default:=Selection.Address, Type:=8)
 

del = InputBox(Prompt:="Introduce el carácter delimitador:", _
Title:="Ordenar valores de una celda", _
Default:="")
 

On Error GoTo 0
 

For Each cell In rng
 
   
    Arr = Split(cell, del)
 
   
    OrdenarSeleccion Arr
 
  
    cell = Join(Arr, del)
 

Next cell
 
End Sub

Function OrdenarSeleccion(TempArray As Variant)
 
  
    Dim MaxVal As Variant
    Dim MaxIndex As Integer
    Dim i As Integer, j As Integer
 

    For i = UBound(TempArray) To 0 Step -1
 

        MaxVal = TempArray(i)
        MaxIndex = i
 

        For j = 0 To i
            If TempArray(j) > MaxVal Then
                MaxVal = TempArray(j)
                MaxIndex = j
            End If
        Next j
 

        If MaxIndex < i Then
            TempArray(MaxIndex) = TempArray(i)
            TempArray(i) = MaxVal
        End If
    Next i
 
End Function

Si no sabes dónde pegar el código anterior, échale un vistazo a esta publicación:

Dónde colocar tu código VBA Excel

Resumen

Como comenté al principio, ordenar la información de una hoja es una de las tareas que se realizan con mas frecuencia. Si lo que quieres ordenar es un conjunto de celdas, puedes recurrir a las funciones ORDENAR u ORDENARPOR o a la herramienta Ordenar y filtrar. Para ordenar los valores de una celda tienes a tu disposición dos métodos: con una fórmula o mediante una macro. En ambas opciones puedes personalizar el carácter delimitador en caso de que utilices por ejemplo, el punto y coma (;) o la barra vertical (|).