Como ordenar números com prefixo ou sufixo de texto/letra no Excel?

Este método introduzirá uma função definida pelo usuário (VBA) para extrair apenas números dos números com prefixo ou sufixo, e então você poderá ordenar facilmente.

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

2. Clique em Inserir > Módulo, e depois cole o código VBA abaixo na nova janela Módulo que se abre.

Função Definida pelo Usuário: Extrair apenas números de strings 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. Salve o código e feche a janela do Microsoft Visual Basic for Applications.

4Selecione uma célula em branco ao lado dos números originais com prefixo ou sufixo, digamos a Célula B2, digite a fórmula =num(A2) (A2 é o número com prefixo ou sufixo) nela, e depois arraste a Alça de AutoPreenchimento desta célula para o intervalo que você precisa.

5Devido aos números extraídos estarem armazenados como texto, precisamos convertê-los para números. Copie todos os números extraídos, clique com o botão direito no intervalo copiado e selecione Colar Valores no menu que aparece ao clicar com o botão direito. Veja a captura de tela:

Observação: No Excel 2007, você pode copiar os números extraídos armazenados como texto, e depois clicar Início > Colar > Colar Valores.

6Continue selecionando esses números, e depois clique no  botão ao lado dos números selecionados, e selecione o Converter para Número na lista suspensa. Veja a captura de tela:

7. Mantenha os novos números selecionados, e clique em Dados > Ordenar do Menor para o Maior ou Ordenar do Maior para o Menor; e depois marque a opção Expandir a seleção na caixa de diálogo Aviso de Classificação que aparece, e clique no botão Ordenar. Veja as capturas de tela:

8Agora os números com prefixo ou sufixo de letra foram ordenados como uma ordenação normal de números (Veja a captura de tela abaixo). Exclua os números extraídos, se necessário.

Na verdade, o Kutools para Excel também oferece uma função EXTRACTNUMBERS semelhante para extrair números de strings de texto. Portanto, podemos ordenar números com prefixo ou sufixo de letra extraindo os números primeiro.

1Selecione uma célula em branco ao lado dos números com prefixo ou sufixo de letra, digamos a Célula B2, digite a fórmula =EXTRACTNUMBERS(A2,VERDADEIRO) (A2 é a célula do número com prefixo ou sufixo de letra) nela, e depois arraste a Alça de AutoPreenchimento desta célula para o intervalo que você precisa. Veja a captura de tela:

2. Mantenha os números extraídos selecionados, clique em Dados > Ordenar do Menor para o Maior ou Ordenar do Maior para o Menor; marque a opção Expandir a seleção na caixa de diálogo Aviso de Classificação que aparece, e depois clique no botão Ordenar. Veja as capturas de tela abaixo:

3. Agora os números com prefixo ou sufixo de letra foram ordenados como uma ordenação normal de números. E você pode excluir todos os números extraídos conforme necessário.

O Kutools para Excel também suporta dividir texto e números das células usando seu utilitário Dividir Células. Portanto, podemos dividir o texto e os números e colocá-los em colunas diferentes para facilitar a ordenação. Por favor, faça o seguinte:

1Selecione os números com prefixo ou sufixo de letra, e clique Kutools > Texto > Dividir CélulasVeja a captura de tela abaixo:

2. Na caixa de diálogo Dividir Células que se abre, por favor marque a opção Dividir em Colunas na seção Tipo, marque Texto e número na seção Dividir por , e clique no botão Ok. Veja a captura de tela acima:

3Agora a segunda caixa de diálogo Dividir Células aparece. Por favor, selecione a célula ao lado dos números originais com prefixo ou sufixo de letra, digamos a Célula B2, e clique no OK botão.
 

Agora os textos e números são divididos e colocados ao lado dos números originais com prefixo ou sufixo. Veja a captura de tela abaixo.

4. Selecione os números divididos, e clique em Dados > Ordenar do Menor para o Maior ou Ordenar do Maior para o Menor; e depois marque a opção Expandir a seleção na caixa de diálogo Aviso de Classificação que aparece, e clique no botão Ordenar. Veja as capturas de tela abaixo:

5. Agora os números com prefixo ou sufixo de letra foram ordenados como uma ordenação normal de números. Você pode excluir o texto e os números divididos conforme necessário.

Este método introduzirá o utilitário Remover Caracteres do Kutools para Excel para remover todos os caracteres não numéricos (incluindo letras) e deixar apenas números nas células. E então você pode ordenar números com prefixo/sufixo de texto/letra facilmente.

1. Copie todos os números com prefixo ou sufixo de letra, e depois cole ao lado dos números originais com prefixo ou sufixo de letra.

2Continue selecionando os números colados, clique Kutools > Texto > Remover Caracteres, e depois marque apenas o Não-numérico opção na caixa de diálogo Remover Caracteres que aparece, e clique no Ok botão. Veja a captura de tela abaixo:

3. Agora só restam números na nova coluna. Continue selecionando esses números, e clique em Dados > Ordenar do Menor para o Maior ou Ordenar do Maior para o Menor; e depois marque a opção Expandir a seleção na caixa de diálogo Aviso de Classificação que aparece, e clique no botão Ordenar. Veja as capturas de tela abaixo:

4. Exclua os números conforme necessário.

Ordenar nomes completos pelo sobrenome no Excel

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

Ordenar células pelo último caractere ou número no Excel

Ordenar dados pelo valor mais frequente no Excel