Cómo ordenar números mixtos y texto, números multinivel en Excel

Ordenar números como texto en Excel

Al ordenar una columna de números de menor a mayor en Excel, en lugar de tener esto:

1, 2, 3, 11, 12, 13, 110, 120, 130

A veces puede obtener algo como esto:

1, 11, 110, 12, 120, 13, 130, 2, 3

Eso sucede cuando los números son en realidad texto y se ordenan como texto; como palabras que se organizan según sus letras, los “números de texto” se ordenan según sus dígitos en lugar de sus valores.

Si la columna de destino contiene números formateados como texto, todo lo que se necesita para que los números se ordenen normalmente es convertir el texto en números. El resultado está en la columna C en la captura de pantalla siguiente.

Por el contrario, si desea ordenar números como textoprimero convierta los números a texto y luego haga clic en Hogar pestaña > Edición grupo > Filtro de clasificación > Ordenar A – Z.

El resultado será como en la columna A de la captura de pantalla anterior.

Cómo ordenar números mixtos y texto en Excel

Con la función Ordenar de Excel incorporada, las cadenas alfanuméricas (texto y números combinados) siempre se ordenan como texto, es decir, letra por letra, dígito por dígito. Para ordenar una columna de números que contienen un prefijo o sufijo de letra como números, lleve a cabo los pasos a continuación. Y aquí está el resultado que estamos tratando de lograr.

Para evitar que Excel maneje números en cadenas alfanuméricas como texto, extraeremos esos números en una columna auxiliar y los ordenaremos por esa columna. Esto le permitirá mantener los datos originales sin cambios pero reorganizarlos como desee.

1. En una columna junto a los valores originales, ingrese una fórmula que extraiga el número de la cadena.

   En este ejemplo, podemos simplemente extraer todos los caracteres después de un guión (“-“). En Excel 365, esto se puede hacer usando la función DESPUÉS DE TEXTO. Al igual que con cualquier otra función de Texto, su salida siempre es texto, independientemente de si está recuperando letras o dígitos, por lo que multiplicamos el resultado por 1 para convertir una cadena de texto en un número.

   =TEXTAFTER(A2, "-")*1

   En versiones anteriores de Excel, se puede lograr el mismo resultado utilizando una combinación de tres funciones diferentes que extraen texto después de un carácter específico. Además, se realiza la operación *1 para convertir la salida de texto a número:

   =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1
   

   Consejo. Para extraer rápidamente números de cualquier posición en una celda, puede utilizar la herramienta Extraer incluida con Ultimate Suite for Excel.

   
   
2. Ordene los datos originales por los números extraídos. Con los números seleccionados, vaya a la Hogar pestaña > Filtro de clasificación > Ordenar de menor a mayor. En el cuadro de diálogo que aparece, elija el Amplia la seleccion opción y haga clic Clasificar. Para obtener más detalles, consulte Cómo ordenar y mantener filas juntas.
   

   Ahora las cadenas que contienen letras y dígitos están ordenadas como números y puedes eliminar u ocultar la columna auxiliar si es necesario.
   

Ordenar cadenas por texto y números a la vez

En caso de que sus cadenas de origen tengan algunos elementos diferentes, puede extraer cada elemento en una columna separada y luego ordenar por varias columnas. Como resultado, las cadenas se organizarán como en la columna D de la siguiente captura de pantalla:

Como todas las cadenas de nuestro conjunto de datos tienen el mismo patrón, la forma más sencilla de dividir texto y números en diferentes columnas es mediante expresiones regulares. Esto se puede hacer con la ayuda de la función personalizada denominada RegExpExtract. Primero, agrega su código a su Excel como se explica en este tutorial. Después de eso, utilice las siguientes fórmulas.

Para extraer la primera aparición de texto de la celda A2, la fórmula en B2 es:

=RegExpExtract(A2, "[^\d]+", 1)

Para extraer el primer número, la fórmula en C2 es:

=RegExpExtract(A2, "\d+", 1)*1

Para extraer el segundo número, la fórmula en D2 es:

=RegExpExtract(A2, "\d+", 2)*1

en el 2^{Dakota del Norte} y 3^tercero fórmulas, multiplicamos la salida RegExpExtract por 1 para convertir una subcadena extraída en un número.

Los usuarios de nuestra Ultimate Suite pueden aprovechar las herramientas Regex para lograr el mismo resultado. Sólo necesitarán realizar una operación rápida más para convertir las cadenas numéricas extraídas (columnas C y D) en números:

Una vez que el texto y los números estén divididos, ordene su conjunto de datos en varias columnas:

1. Seleccione todas las columnas (A2:D16 en nuestro caso).
2. Sobre el Datos pestaña, en la Filtro de clasificación grupo, haga clic en el Clasificar botón.
3. Agregue tantos niveles de clasificación como necesite y elija el orden de clasificación deseado.
4. Cuando termine, haga clic en DE ACUERDO.

En nuestro caso, el rango seleccionado se ordena primero por Texto de la A a la Z, luego por Numero 1y luego por Número 2 de menor a mayor:

El resultado es exactamente lo que estábamos buscando:

Cómo ordenar números multinivel/jerarquía en Excel

En Microsoft Excel, los números multinivel como 1.1, 1.1.1, 1.1.2 son cadenas y se ordenan como texto, no como números:

Para ordenar cadenas de varios niveles como números, puede utilizar una función personalizada definida por el usuario que tiene la siguiente sintaxis:

Número de jerarquía (rango, del, nivel_máximo)

Dónde:

• Rango es el rango a ordenar.
• Del es el delimitador utilizado para separar los niveles, normalmente un punto (.).
• Máximo nivel es el número máximo de niveles en los números de jerarquía.

Y aquí está el código de la función:

La función lo que hace es generar números que permiten ordenar cadenas numéricas multinivel. Más precisamente, divide una cadena de jerarquía en números individuales (partes), procesa cada parte según el siguiente algoritmo y suma los resultados:

part(Index) * (10 ^ ((level - Index) * 2))

Por ejemplo, para 1.1, la función produce este resultado:

1*(10^((2-0)*2)) + 1*(10^((2-1)*2)) = 10100

Para 1.2, es:

1*(10^((2-0)*2)) + 2*(10^((2-1)*2)) = 10200

Cómo ordenar números de jerarquía usando la función personalizada:

1. Introduce el código del JerarquíaNúmero funcione en un módulo de código estándar de su libro de trabajo y guárdelo como un libro de trabajo habilitado para macros (.xlsm). Las instrucciones detalladas están aquí.
2. En una celda en blanco junto a la primera cadena de jerarquía, ingrese un JerarquíaNúmero fórmula y presione el botón Ingresar llave.
3. Arrastre la fórmula hacia abajo en tantas filas como sea necesario.
4. Ordene su conjunto de datos según la columna de fórmulas.

Supongamos que tiene cadenas numéricas de varios niveles con un máximo de 3 niveles en la columna A. La fórmula para B2 es:

=HierarchyNumber(A2, ".", 3)

Después de copiar la fórmula, obtendrá este resultado:

Después de eso, ordena todo el conjunto de datos (A2:B19) según la columna de fórmula, de menor a mayor:

Y ordene las cadenas numéricas de varios niveles como números:

Ordenar números de varios niveles con el prefijo de texto

Este ejemplo analiza un caso ligeramente diferente en el que los números multinivel tienen como prefijo algún texto, por ejemplo “Nivel 1.1.1”. A primera vista, la tarea parece más complicada, pero en realidad la solución es más sencilla 🙂

Primero, extrae subcadenas numéricas de varios niveles en una columna separada. Para ello, puede utilizar la herramienta Extraer incluida con nuestra Ultimate Suite:

O puedes escribir una fórmula para colocar todos los caracteres después de un espacio:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Y luego, realice una clasificación personalizada. En el cuadro de diálogo Ordenar de Excel, agregue dos niveles, primero por el número de jerarquía extraído y luego por la cadena original, y haga clic en Aceptar:

Aparecerá la siguiente advertencia, donde usted elige “Ordenar números y números almacenados como texto por separado“.

Como resultado, las cadenas que contienen números de varios niveles se ordenan por números:

descargas disponibles

Ordenar números mixtos y texto en Excel: ejemplos (archivo .xlsm)
Versión de prueba totalmente funcional de Ultimate Suite (archivo .exe)