Как отсортировать числа с текстовым или буквенным префиксом/суффиксом в 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