Крис работает на аэрокосмическом заводе, где они производят детали газотурбинных двигателей для многих компаний. У каждой компании есть свои уникальные номера деталей. Некоторые из них числовые, но большинство представляют собой комбинацию цифр и букв разной длины. При сортировке по номеру детали Крис хотел бы, чтобы столбец рассматривался как текст, чтобы сортировка выполнялась так, как ему нужно. Он попытался отформатировать ячейки как текст, но это не всегда помогало. Кроме того, ему не нравится идея вводить апостроф в каждой ячейке. Крис задается вопросом, есть ли простой способ заставить Excel отсортировать все номера деталей как текст.

Возможно, наиболее показательной частью проблемы Криса является утверждение, что он пытался отформатировать ячейки как текст, но это не всегда срабатывает. Есть уловка для форматирования ячеек как текста — вы должны сделать это перед вводом информации в ячейки. Если вы попытаетесь отформатировать как текст ячейку, содержащую число, это не изменит число на текст. Однако, если ячейка отформатирована как текст перед вводом числа, то при вводе число обрабатывается как текст.

Решение состоит в том, чтобы повторно ввести все числа после форматирования ячеек как текста. Это, конечно, может стать серьезной рутиной, когда вы имеете дело с сотнями или тысячами номеров деталей. Вместо этого вы можете попробовать следующие общие шаги, в которых, как я предполагаю, номера деталей указаны в столбце A:

  1. Вставьте столбец справа от номеров деталей. (Этот новый столбец теперь является столбцом B.)

  2. Отформатируйте столбец B как текст.

  3. Выберите столбец A и нажмите Ctrl + C. Это скопирует номера деталей в буфер обмена.

  4. Выберите ячейку B1.

  5. Используйте Специальную вставку, чтобы вставить только значения в новый столбец.

Когда вы выполните эти шаги, все в столбце B следует рассматривать как текст, и вы можете удалить столбец A. Теперь вы сможете сортировать по номерам деталей и получать желаемые результаты.

Конечно, с этим есть проблема. Если вы будете часто копировать и вставлять номера деталей в будущем, вполне возможно, что все номера деталей больше не будут обрабатываться как текст. Это происходит потому, что если вы используете традиционные Ctrl + C и Ctrl + V для копирования и вставки, Excel также вставляет форматирование в ячейки. Таким образом, если вставляемый номер детали был ранее отформатирован как число, то после вставки он будет рассматриваться как число.

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

Sub MakeText()

Dim c As Range

Selection.NumberFormat = "@"

For Each c In Selection         c.Value = c.Value     Next c End Sub

Идея макроса заключается в том, что вы должны выбрать все номера деталей, а затем запустить его. Форматирование выделения устанавливается на текст, а затем каждая ячейка в выделенном фрагменте «повторно вводится», так что числовые значения впоследствии обрабатываются как текст.

Если вы не против использования вспомогательных столбцов и не хотите использовать макрос, вы можете попробовать создать тот, который содержит текстовый эквивалент номеров деталей. Это можно сделать с помощью любой из следующих формул:

=A1 & ""

=CONCATENATE(A1)

=TEXT(A1, "@")

Скопируйте формулу на необходимое количество ячеек. Эти формулы заставляют содержимое вспомогательного столбца обрабатываться как текст. Затем вы можете отсортировать номера деталей на основе содержимого вспомогательного столбца.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (4627) применим к Microsoft Excel 2007, 2010, 2013 и 2016.