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

Ordenar números como texto en Excel

Cuando ordena 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 puedes obtener algo como esto:

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

Esto sucede cuando los números son en realidad texto y se clasifican como texto; al igual que las palabras que se organizan en función de sus letras, los «números de texto» se clasifican por sus dígitos en lugar de por sus valores.

Si la columna de destino contiene números con formato de texto, todo lo que se necesita para que los números se ordenen normalmente es convertir el texto en número. El resultado está en la columna C en la captura de pantalla a continuación.

Por el contrario, si quieres ordenar números como textoprimero convierta números a texto y luego haga clic en El hogar pestaña > publicación grupo > Ordenar y filtrar > Ordenar A – Z.

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

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

Con la función de clasificación integrada de Excel, las cadenas alfanuméricas (texto y números combinados) siempre se clasifican como texto, es decir, letra por letra, dígito por dígito. Para ordenar una columna de números que contiene un prefijo o sufijo de letra como numeros, realice 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 de ayuda y 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 TEXTAFTER. Al igual que con cualquier otra función de texto, su resultado siempre es texto, ya sea que esté recuperando letras o números, 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 usando una combinación de tres funciones diferentes que extraen texto por un carácter específico. Además, se realiza la operación *1 para convertir texto a número:

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

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

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

   Ahora las cadenas que contienen letras y números se clasifican como números y puede eliminar u ocultar la columna de ayuda si es necesario.

Ordenar cadenas por texto y números a la vez

Si las cadenas de origen tienen varios elementos diferentes, puede extraer cada elemento en una columna separada y luego ordenar por varias columnas. Como resultado, las filas se organizarán como en la columna D en la siguiente captura de pantalla:

Dado que todas las cadenas en nuestro conjunto de datos tienen el mismo patrón, la forma más fácil de dividir el texto y los números en diferentes columnas es usar expresiones regulares. Esto se puede hacer usando la función personalizada llamada RegExpExtract. Primero, agregue su código a Excel como se explica en este tutorial. Después de eso, use las siguientes fórmulas.

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

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

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

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

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

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

En 2^cuando y 3^calle fórmulas, multiplicamos el resultado de RegExpExtract por 1 para convertir una subcadena extraída en un número.

Los usuarios de nuestra Ultimate Suite pueden usar las herramientas Regex para lograr el mismo resultado. Solo tendrán que 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 el conjunto de datos por varias columnas:

1. Selecciona todas las columnas (A2:D16 en nuestro caso).
2. En datos pestaña, en Ordenar y filtrar grupo, haga clic en forma botón.
3. Agregue tantos niveles de clasificación como necesite y elija el orden de clasificación que desee.
4. Cuando haya terminado, haga clic en Bien.

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

El resultado es exactamente lo que estaba buscando:

Cómo ordenar números en múltiples niveles/jerarquías en Excel

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

Para ordenar cadenas multinivel como números, puede usar una función personalizada definida por el usuario que tiene la siguiente sintaxis:

HierarchyNumber(rango, del, max_level)

Dónde:

• Rango es el rango a ordenar.
• Supr es el delimitador utilizado para separar los niveles, generalmente un punto (.).
• Máximo nivel es el número máximo de niveles en los números jerárquicos.

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

Lo que hace la función es generar números que permiten la clasificación de varios niveles de cadenas numéricas. Más específicamente, 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 jerárquicos usando la función personalizada:

1. Ingrese el código JerarquíaNúmero función en un módulo de código de libro de trabajo estándar y guárdelo como un libro de trabajo habilitado para macros (.xlsm). Las instrucciones detalladas están aquí.
2. En una celda vacía al lado de la primera fila de la jerarquía, ingrese un JerarquíaNúmero fórmula y presione la tecla ingresar llave.
3. Arrastre la fórmula hacia abajo tantas filas como sea necesario.
4. Ordene el conjunto de datos por la columna de fórmula.

Suponga 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, ordene todo el conjunto de datos (A2:B19) por la columna de fórmula de menor a mayor:

Y obtenga las cadenas numéricas multinivel ordenadas como números:

Ordenar números en varios niveles prefijados con texto

Este ejemplo analiza un caso ligeramente diferente en el que los números de varios niveles tienen un prefijo de texto, por ejemplo, «Nivel 1.1.1». A primera vista, la tarea suena más complicada, pero en realidad la solución es más sencilla 🙂

Primero, extraiga las subcadenas numéricas multinivel en una columna separada. Para ello, puede utilizar la herramienta de extracción incluida con Ultimate Suite:

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

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

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

Aparecerá la siguiente advertencia donde elija «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)