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

Excel es una herramienta poderosa que nos permite realizar todo tipo de cálculos y organizaciones de datos de manera eficiente. Sin embargo, a veces podemos encontrarnos en situaciones en las que nuestros datos contienen números mixtos y texto, o números multinivel que requieren ser ordenados de forma coherente. ¿Cómo podemos manejar esta tarea aparentemente complicada en Excel? En este artículo, aprenderemos los pasos y técnicas necesarias para ordenar números mixtos y texto, así como números multinivel en Excel de manera efectiva y sin esfuerzo. ¡Sigue leyendo y descubre cómo puedes llevar tus habilidades de organización de datos al siguiente nivel!

El tutorial explica problemas comunes al ordenar cadenas alfanuméricas en Excel y proporciona soluciones prácticas para ordenar números como texto y texto como números.

Ordenar texto y números por separado en Excel es tan fácil como ABC o 123 🙂 Pero reorganizar los ID de productos, SKU y otros valores que contienen letras y dígitos puede causar problemas. La clasificación alfabética de Excel no puede distinguir una parte numérica de una cadena para manejarla por separado. Si desea tener más control sobre cómo se clasifican las picaduras alfanuméricas, consulte los ejemplos siguientes.

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. Cómo ordenar números mixtos y texto, números multinivel en Excel

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. Cómo ordenar números mixtos y texto, números multinivel en Excel

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. Cómo ordenar números mixtos y texto, números multinivel en Excel

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 Cómo ordenar números mixtos y texto, números multinivel en Excel

    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.

    Cómo ordenar números mixtos y texto, números multinivel en 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. Cómo ordenar números mixtos y texto, números multinivel en Excel

    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. Cómo ordenar números mixtos y texto, números multinivel en Excel

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

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 2Dakota del Norte y 3tercero fórmulas, multiplicamos la salida RegExpExtract por 1 para convertir una subcadena extraída en un número. Cómo ordenar números mixtos y texto, números multinivel en Excel

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

El resultado es exactamente lo que estábamos buscando: Cómo ordenar números mixtos y texto, números multinivel en Excel

Consejo. Para reorganizar las cuerdas de manera diferente, cambie el orden de los niveles en el Clasificar caja de diálogo.

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

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:

Función personalizada para ordenar números de jerarquía

Función Número de jerarquía (rango Como rango, del Como CadenaMáximo nivel Como Entero) Como Doble

Oscuro parte Como Variante

parte = Dividir(rango.Valor, del)

Para Índice = 0 A UBound(parte) Número de jerarquía = Número de jerarquía + parte (Índice) * (10 ^ ((nivel_máximo – Índice) * 2))

Próximo Índice

Fin 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: Cómo ordenar números mixtos y texto, números multinivel en Excel

Después de eso, ordena todo el conjunto de datos (A2:B19) según la columna de fórmula, de menor a mayor: Cómo ordenar números mixtos y texto, números multinivel en Excel

Y ordene las cadenas numéricas de varios niveles como números: Cómo ordenar números mixtos y texto, números multinivel en Excel

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

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

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2)) Cómo ordenar números mixtos y texto, números multinivel en Excel

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: Cómo ordenar números mixtos y texto, números multinivel en Excel

Aparecerá la siguiente advertencia, donde usted elige «Ordenar números y números almacenados como texto por separado«. Cómo ordenar números mixtos y texto, números multinivel en Excel

Como resultado, las cadenas que contienen números de varios niveles se ordenan por números: Cómo ordenar números mixtos y texto, números multinivel en Excel

Así es como ordenar cadenas numéricas y números de varios niveles en Excel. ¡Gracias por leer y nos vemos en nuestro blog la próxima semana!

descargas disponibles

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

Usted también podría estar interesado en

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded

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

Excel es una potente herramienta que nos permite realizar cálculos, análisis y organización de datos de manera eficiente. Sin embargo, a veces nos encontramos con la necesidad de ordenar una mezcla de números y texto en una columna en Excel, o incluso ordenar números multinivel en una hoja de cálculo.

Ordenar números mixtos y texto en Excel

Cuando tenemos una columna que contiene números mixtos y texto en Excel, por defecto la función de ordenar no nos dará el resultado esperado. Esto se debe a que Excel considera los números y el texto como entidades distintas al realizar la ordenación.

Para solucionar este problema, podemos seguir los siguientes pasos:

  1. Selecciona la columna que deseas ordenar.
  2. Haz clic en la pestaña «Datos» en la barra de herramientas de Excel.
  3. Dentro de la pestaña «Ordenar y filtrar», haz clic en el botón «Ordenar».
  4. En la ventana emergente de «Ordenar», asegúrate de que la opción «Ordenar por» esté seleccionada correctamente, es decir, elija la columna que contiene los números y el texto que deseas ordenar.
  5. En la sección «Ordenar por», selecciona la opción «Valores» y luego elige «Ordenar menor a mayor» o «Ordenar mayor a menor», según tus necesidades.
  6. Haz clic en «Aceptar» y Excel ordenará la columna teniendo en cuenta tanto los números como el texto.

De esta manera, podrás ordenar correctamente una columna que contiene una mezcla de números y texto en Excel.

Ordenar números multinivel en Excel

A veces es necesario ordenar números de forma multinivel en Excel, es decir, realizar una ordenación en función de múltiples criterios. Por ejemplo, podrías necesitar ordenar una tabla de ventas primero por región y luego por mes.

Para ello, sigue estos pasos:

  1. Selecciona las columnas que deseas ordenar multinivelmente.
  2. Haz clic en la pestaña «Datos» en la barra de herramientas de Excel.
  3. Dentro de la pestaña «Ordenar y filtrar», haz clic en el botón «Ordenar».
  4. En la ventana emergente de «Ordenar», asegúrate de que las opciones «Ordenar por» estén seleccionadas correctamente según tus criterios de ordenación multinivel.
  5. Si es necesario, añade criterios adicionales de ordenación haciendo clic en el botón «Agregar nivel».
  6. Haz clic en «Aceptar» y Excel ordenará los datos multinivelmente según tus criterios seleccionados.

Ahora podrás ordenar tus datos en Excel de manera multinivel y obtener los resultados deseados.

Recuerda que estos son solo algunos ejemplos de cómo ordenar números mixtos y texto, así como números multinivel en Excel. Si deseas obtener más información sobre este tema, puedes consultar los siguientes recursos:

Esperamos que esta guía te haya sido útil y puedas aplicar estos conocimientos para organizar tus datos de manera eficiente en Excel.

Deja un comentario