Кьяран работает в библиотеке, и ему часто приходится работать с длинными списками номеров ISBN в Excel. Числа должны содержать 10 или 13 цифр, могут содержать дефисы и могут иметь начальные нули. Он использует текстовый формат для ячеек, содержащих номера ISBN, чтобы сохранить их неизменными как текстовые строки. Для некоторых целей дефисы в ISBN должны быть удалены (для этого он использует «Найти и заменить»), и здесь начинается проблема.

0-241-95011-2 становится 241950112 (теперь количество цифр уменьшено до 9), и, что еще хуже, 978-0-00-200784-9 становится 9.78E + 12 (экспоненциальное представление). Кьяран не может найти способ обойти эти две проблемы, независимо от того, что он делал с форматированием до или после использования функции «Найти и заменить», чтобы избавиться от тире.

Что происходит, когда вы редактируете ячейки, Excel анализирует содержимое ячейки как числа, а не как текст. В этом случае лучшее решение — убедиться, что перед содержимым вашей ячейки стоит апостроф, прежде чем выполнять поиск и замену, чтобы избавиться от тире.

Если у вас есть рабочий лист, содержащий много номеров ISBN в столбце A, вы можете добавить апострофы с помощью следующей формулы:

= "'" & A1

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

Причина, по которой это работает, заключается в том, что апостроф является индикатором для Excel, что содержимое ячейки следует рассматривать как текст. Апостроф не отображается на листе, но он является частью содержимого ячейки, как вы можете убедиться, посмотрев на панель формул.

Другой подход — обойти использование функции «Найти и заменить», чтобы избавиться от дефисов. Вместо этого используйте функцию ЗАМЕНА, чтобы удалить их следующим образом:

=SUBSTITUTE(A1,"-","")

Функция листа SUBSTITUTE возвращает текстовое значение, поэтому любые ведущие нули сохраняются, и Excel не пытается преобразовать числа в числовой формат.

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

Sub RemoveDashes()

Dim c As Variant, sISBN As String

For Each c In Selection         sISBN = Application.Substitute(c, "-", "")

c.NumberFormat = "@"

c.Value = "'" & sISBN     Next End Sub

В основном макрос выполняет три функции: удаляет тире, форматирует ячейку как текст и помещает вырезанный ISBN обратно в ячейку с апострофом перед ним.

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (9927) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Removing_Dashes_from_ISBN_Numbers [Удаление тире из номеров ISBN].