Как отсортировать числа с текстовым/буквенным префиксом или суффиксом в Excel?

Этот метод представляет пользовательскую функцию (VBA) для извлечения только чисел из чисел с префиксом или суффиксом, после чего вы сможете легко выполнить сортировку.

1. Нажмите клавиши Alt + F11 одновременно, чтобы открыть окно Microsoft Visual Basic для приложений.

2. Щелкните Вставка > Модуль, затем вставьте приведенный ниже код VBA в открывшееся окно Модуля.

Пользовательская функция: Извлечение только чисел из текстовых строк

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. Сохраните код и закройте окно Microsoft Visual Basic для приложений.

4Выберите пустую ячейку рядом с исходными числами с префиксом или суффиксом, например, ячейку B2, и введите формулу =num(A2) (A2 — это число с префиксом или суффиксом), затем перетащите маркер автозаполнения этой ячейки на нужный диапазон.

5Так как извлеченные числа хранятся как текст, нам нужно преобразовать их в числа. Скопируйте все извлеченные числа, щелкните правой кнопкой мыши скопированный диапазон и выберите Значения вставить из контекстного меню. См. скриншот:

ПримечаниеВ Excel 2007 вы можете скопировать извлеченные числа, сохраненные как текст, а затем нажать Главная > Вставить > Значения вставить.

6Продолжайте выделять эти числа, затем нажмите кнопку рядом с выбранными числами и выберите Преобразовать в число из выпадающего списка. См. скриншот:

7. Продолжая выделять новые числа, нажмите Данные > Сортировать по возрастанию или Сортировать по убыванию; отметьте опцию Расширить выделение в появившемся диалоговом окне Предупреждение о сортировке и нажмите кнопку Сортировать. См. скриншоты:

8Теперь числа с буквенным префиксом или суффиксом отсортированы как обычные числа (см. скриншот ниже). При необходимости удалите извлеченные числа.

На самом деле, Kutools для Excel также предоставляет аналогичную функцию EXTRACTNUMBERS для извлечения чисел из текстовых строк. Таким образом, мы можем сортировать числа с буквенным префиксом или суффиксом, сначала извлекая числа.

1Выберите пустую ячейку рядом с числами с буквенным префиксом или суффиксом, например, ячейку B2, и введите формулу =EXTRACTNUMBERS(A2,TRUE) (A2 — это ячейка с числом с буквенным префиксом или суффиксом), затем перетащите маркер автозаполнения этой ячейки на нужный диапазон. См. скриншот:

2. Выберите извлеченные числа, нажмите Данные > Сортировать по возрастанию или Сортировать по убыванию; отметьте опцию Расширить выделение в появившемся диалоговом окне Предупреждение о сортировке, затем нажмите кнопку Сортировать. См. скриншоты ниже:

3. Теперь числа с буквенным префиксом или суффиксом отсортированы как обычные числа. При необходимости вы можете удалить все извлеченные числа.

Kutools для Excel также поддерживает разделение текста и чисел из ячеек с помощью своей утилиты Разделить ячейки. Таким образом, мы можем разделить текст и числа и поместить их в разные столбцы для удобной сортировки. Пожалуйста, действуйте следующим образом:

1Выберите числа с буквенным префиксом или суффиксом и нажмите Kutools > Текст > Разделить ячейкиСм. скриншот ниже:

2. В открывшемся диалоговом окне Разделить ячейки отметьте опцию Разделить на столбцы в разделе Тип, отметьте Текст и число в разделе Разделить по , затем нажмите кнопку ОК. См. скриншот выше:

3Теперь появится второе диалоговое окно Разделить ячейки. Выберите ячейку рядом с исходными числами с буквенным префиксом или суффиксом, например, ячейку B2, и нажмите ОК кнопку.
 

Теперь текст и числа разделены и помещены рядом с исходными числами с префиксом или суффиксом. См. скриншот ниже.

4. Выберите разделенные числа и нажмите Данные > Сортировать по возрастанию или Сортировать по убыванию; отметьте опцию Расширить выделение в появившемся диалоговом окне Предупреждение о сортировке, затем нажмите кнопку Сортировать. См. скриншоты ниже:

5. Теперь числа с буквенным префиксом или суффиксом отсортированы как обычные числа. Вы можете удалить разделенный текст и числа по мере необходимости.

Этот метод представит утилиту Удалить символы из Kutools для Excel для удаления всех нечисловых символов (включая буквы) и оставления только чисел в ячейках. После этого вы сможете легко отсортировать числа с текстовым/буквенным префиксом или суффиксом.

1. Скопируйте все числа с буквенным префиксом или суффиксом, затем вставьте рядом с исходными числами с буквенным префиксом или суффиксом.

2Продолжая выделять вставленные числа, нажмите Kutools > Текст > Удалить символызатем отметьте только Нечисловые символы опцию в появившемся диалоговом окне Удалить символы и нажмите ОК кнопку. См. скриншот ниже:

3. Теперь в новом столбце остались только числа. Продолжая выделять эти числа, нажмите Данные > Сортировать по возрастанию или Сортировать по убыванию; отметьте опцию Расширить выделение в появившемся диалоговом окне Предупреждение о сортировке, затем нажмите кнопку Сортировать. См. скриншоты ниже:

4. Удалите числа по мере необходимости.

Сортировка полных имен по фамилии в Excel

Быстрая сортировка дней рождения (дат) по месяцам в Excel

Сортировка ячеек по последнему символу или числу в Excel

Сортировка данных по наиболее часто встречающемуся значению в Excel