Como ordenar números que incluem prefixos ou sufixos de texto/letras no Excel?

Este método apresenta uma função definida pelo utilizador (VBA) que extrai apenas os números de valores com prefixo ou sufixo, permitindo-lhe ordená-los facilmente.

1. Prima simultaneamente as teclas Alt+F11 para abrir a janela do Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo e cole o código VBA abaixo na nova janela do Módulo aberta.

Função Definida pelo Utilizador: Extrair apenas números de cadeias de texto

Function num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End Function

3. Guarde o código e feche a janela do Microsoft Visual Basic for Applications.

4. Selecione uma célula vazia junto aos números originais com prefixo ou sufixo — por exemplo, a célula B2 —, introduza a fórmula =num(A2) (em que A2 é o número com prefixo ou sufixo) e arraste a alça de autorreenchimento dessa célula até à gama pretendida.

5. Como os números extraídos são armazenados como texto, é necessário convertê-los em números. Copie todos os números extraídos, clique com o botão direito do rato na gama copiada e selecione Colar Valores no menu de contexto. Veja a imagem:

Nota: No Excel 2007, pode copiar os números extraídos armazenados como texto e, em seguida, clicar em Base > Colar > Colar Valores.

6. Mantenha os números selecionados e clique no botão junto aos números selecionados; em seguida, selecione Converter em Númerona lista pendente. Veja a imagem:

7. Mantenha os novos números selecionados e clique em Dados > Ordenar do menor para o maior ou Ordenar do maior para o menor; na caixa de diálogo Aviso de Ordenação que surge, marque a opção Expandir a seleção e clique no botão Ordenar. Veja as imagens:

8. Os números com prefixo ou sufixo alfabético já foram ordenados como se fossem números normais (veja a imagem abaixo). Elimine os números extraídos, se necessário.

Na verdade, o Kutools para Excel também disponibiliza uma função semelhante denominada EXTRACTNUMBERS para extrair números de cadeias de texto. Assim, podemos ordenar números com prefixo ou sufixo alfabético ao extrair primeiro os números.

1. Selecione uma célula vazia junto aos números com prefixo ou sufixo de letra — por exemplo, a célula B2 —, introduza a fórmula =EXTRACTNUMBERS(A2;VERDADEIRO) (em que A2 é a célula que contém o número com prefixo ou sufixo de letra) e arraste a alça de autorreenchimento até à gama pretendida. Veja a imagem:

2. Mantenha os números extraídos selecionados e clique em Dados > Ordenar do menor para o maior ou Ordenar do maior para o menor; na caixa de diálogo Aviso de Ordenação que surge, marque a opção Expandir a seleção e clique no botão Ordenar. Veja as imagens abaixo:

3. Os números com prefixo ou sufixo alfabético já foram ordenados como se fossem números normais. Pode eliminar todos os números extraídos, conforme necessário.

O Kutools para Excel também permite dividir células por texto e número através da sua funcionalidade Dividir Células. Assim, pode separar facilmente o texto e os números, colocando-os em colunas distintas para facilitar a ordenação. Proceda da seguinte forma:

1. Selecione os números com prefixo ou sufixo de letra e clique em Kutools > Texto > Dividir Células. Veja a imagem abaixo:

2. Na caixa de diálogo Dividir Células aberta, selecione a opção Dividir em Colunas na secção Tipo, escolha Dividir por Texto e Número na secção Dividir por e clique no botão OK. Veja a imagem acima:

3. Surge agora a segunda caixa de diálogo Dividir Células. Selecione a célula adjacente aos números originais com prefixo ou sufixo alfabético — por exemplo, a célula B2 — e clique no botão OK.
 

Os Dividir por Texto e Número foram agora divididos e colocados junto aos números originais com prefixo ou sufixo. Veja a imagem abaixo.

4. Selecione os números separados e clique em Dados > Ordenar do menor para o maior ou Ordenar do maior para o menor; na caixa de diálogo Aviso de Ordenação que surge, marque a opção Expandir a seleção e clique no botão Ordenar. Veja as imagens abaixo:

5. Os números com prefixo ou sufixo alfabético já foram ordenados como se fossem números normais. Pode eliminar as colunas «Dividir por Texto» e «Dividir por Número», conforme necessário.

Este método apresenta a utilidade Excluir Caracteres Específicos do Kutools para Excel para remover todos os caracteres não numéricos (incluindo letras) e deixar apenas os números nas células. Depois, poderá ordenar facilmente os números com prefixo ou sufixo de texto/letra.

1. Copie todos os números com prefixo ou sufixo alfabético e cole-os junto aos números originais que também incluam prefixo ou sufixo alfabético.

2. Mantenha os números colados selecionados e clique em Kutools > Texto > Excluir Caracteres Específicos. Na caixa de diálogo Excluir Caracteres Específicos que surge, marque apenas a opção Não numérico e, em seguida, clique no botão OK. Veja a imagem abaixo:

3. Restam agora apenas números na nova coluna. Mantenha esses números selecionados e clique em Dados > Ordenar do menor para o maior ou Ordenar do maior para o menor; na caixa de diálogo Aviso de Ordenação que surge, marque a opção Expandir a seleção e clique no botão Ordenar. Veja as imagens abaixo:

4. Elimine os números, conforme necessário.

Ordenar Nome Completo por Sobrenome no Excel

Ordenar rapidamente aniversários (datas) por mês no Excel

Ordenar células pelo último carácter ou número no Excel

Ordenar dados pelo valor mais frequente no Excel