Демонстрация: удалите начальный апостроф из чисел с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!
Возможно, вы сталкивались с документами Excel, созданными кем-то другим, которые содержат символ апострофа перед текстом, числами или датами.
Вы могли также заметить, что это происходит, когда вы копируете таблицы или данные из MS Word или импортируете данные с веб-страницы на лист Excel.
Часто вы даже не понимаете, что они там есть, потому что апострофы остаются скрытыми и видны только тогда, когда вы видите содержимое ячейки в строке формул.
Если вам интересно узнать, для чего нужны эти апострофы и как они могут повлиять на результаты любых последующих действий, примененных к ним, читайте дальше.
В этом руководстве по Excel я не только расскажу, что означают скрытые апострофы и зачем они здесь, но также продемонстрирую три различных и надежных способа удаления апострофов в Excel.
Итак, приступим!
Оглавление
Зачем удалять скрытые апострофы в Excel — проблема
Скрытые апострофы часто могут быть источником путаницы и разочарования, поскольку из-за них содержимое ячейки ведет себя не так, как вы ожидаете.
Иногда они не реагируют на форматирование, а иногда вы обнаружите, что примененные к ним формулы не дают ожидаемого результата.
Например, рассмотрим следующую таблицу. Перед вами набор чисел, первые четыре из которых имеют ведущие апострофы.
При применении вычисления к этому столбцу, например функции СУММ, обратите внимание, что это не дает желаемого результата.
Вы ожидаете получить сумму 1670, но из-за апострофов функция СУММ игнорирует первые 4 числа и учитывает только последнее число, 50.
Почему появляется апостроф?
Ведущие апострофы заставляют excel обрабатывать содержимое ячейки как текстовое значение. Таким образом, даже если ячейка содержит число или дату, Excel будет рассматривать их как текст.
Но почему кто-то хочет, чтобы Excel обрабатывал числа или даты как текст?
Для этого может быть множество причин.
Если вы укажете что-то вроде января-01, Excel автоматически преобразует его в дату и отформатирует ее в соответствии с вашим глобальным форматом даты (например, 01/01/2020).
Давай, попробуй… Это очень расстраивает!
Если вы не хотите, чтобы это произошло, и вы просто хотите отобразить текст как «01 января», вам нужно каким-то образом сообщить Excel, что вы хотите, чтобы это рассматривалось как текстовое значение, а не как дата. .
Это можно сделать, просто добавив начальный апостроф.
Иногда может потребоваться, чтобы ведущие нули в ваших числовых значениях оставались. Например, у вас может быть идентификационный номер:
000023452
Если вы не ставите перед числом апостроф, Excel автоматически отформатирует число, удалив все ведущие нули.
3 способа удалить ведущие апострофы в Excel
Если это всего лишь одна или две ячейки, вы можете выделить ячейку и удалить апостроф, чтобы вернуть ее в нормальное состояние.
Однако, если вам нужно работать с целым столбцом ячеек, есть три способа обойти эту проблему:
Примечание. Если вы думаете, что можете использовать функцию "Найти и заменить", чтобы найти символ апострофа и заменить его пробелом, это не сработает.
Использование функции преобразования текста в столбцы для удаления апострофа
Это наиболее часто используемый метод удаления ведущих апострофов в Excel.
Функция "Текст в столбцы" в основном предназначена для разделения текстового содержимого в ячейках на несколько столбцов.
Итак, если у вас есть ячейка, содержащая полное имя человека, разделенное пробелом, вы можете использовать функцию "Текст в столбцы", чтобы разделить ячейку на две ячейки, одна из которых содержит имя, а другая – фамилия.
Во время этого процесса разделения ячеек функция преобразования текста в столбцы также преобразует числовые значения в числа, значения дат в даты и все остальные значения в текст.
Таким образом, эта функция обеспечивает действительно умный и быстрый способ преобразовать все ваши ячейки, разделенные апострофами, обратно в их числовой формат или формат даты.
- Выберите диапазон ячеек, которые вы хотите преобразовать (удалите апострофы).
- На ленте меню "Данные" нажмите кнопку "Текст в столбцы".
- Появится мастер «Преобразовать текст в столбцы». На этом этапе больше ничего делать не нужно. Просто нажмите кнопку "Готово".
Вы увидите, что все ведущие апострофы из выбранных ячеек были удалены!
Умножение ячеек на 1
Вот еще один надежный метод и весьма изящный трюк, который можно использовать для удаления ведущих апострофов из ячеек, содержащих числа.
- Введите цифру 1 в любую пустую ячейку листа.
- Нажмите Ctrl+C, чтобы скопировать значение.
- Теперь выберите диапазон ячеек, которые вы хотите преобразовать (удалите из них апострофы).
- Нажмите правой кнопкой мыши и выберите "Специальная вставка" в появившемся всплывающем меню. Откроется диалоговое окно «Специальная вставка».
- В разделе «Операция» вы найдете параметр «Умножение». Выберите его и нажмите ОК. Это умножит каждую ячейку из выбранного диапазона на число 1. Результатом будет набор тех же числовых значений, но с удаленными апострофами.
- Вы можете удалить номер 1 сейчас, потому что его работа завершена.
Помните, однако, что этот метод работает только с числовыми значениями, которые содержат ведущие апострофы. Это не работает с датами или текстом.
Использование кода VBA
Третий способ удалить апострофы из любого столбца на листе — использовать код VBA. Я бы посоветовал вам использовать этот метод, только если вы опытный пользователь Excel и не возражаете против небольшого сценария, чтобы выполнить работу.
Вот шаги, которые необходимо выполнить:
- На ленте меню разработчика выберите Visual Basic.
- Когда откроется окно VBA, выберите в меню пункт «Вставить», а затем нажмите «Модуль». Это добавит новый модуль в панель проводника проекта.
- Дважды щелкните модуль, который вы только что вставили в Project Explorer. Откроется окно кода модуля.
- Введите или скопируйте и вставьте следующие 6 строк кода макроса VBA в окно модуля:
Теперь, в зависимости от ваших требований, вам нужно внести два изменения в строку 2 этого кода.
- Вы можете заменить «Лист1» именем листа, содержащего ячейки, которые вы хотите преобразовать.
- Кроме того, замените число «1» внутри функций Columns() на номер столбца, который вы хотите изменить. Таким образом, столбец A означает 1, столбец B – 2 и т. д.
После того как вы выполнили описанные выше шаги, все готово для запуска макроса. И когда вы запустите этот макрос, он мгновенно перейдет к столбцу 1 и удалит все апострофы из всех ячеек.
Ниже приведены шаги для запуска макроса:
- Нажмите в любом месте кода.
- Нажмите зеленую кнопку воспроизведения/запуска на панели инструментов или нажмите клавишу F5 на клавиатуре.
- Закройте окно VBA.
Если вы сейчас проверите свой лист Excel, вы обнаружите, что все апострофы удалены, а все ячейки преобразованы в числовой формат.
Обратите внимание, что если в вашем столбце есть значения дат с апострофами и некоторые без них, этот метод может иметь неприятные последствия, поскольку он преобразует даты без апострофов в число, которое, в конечном счете, превосходит цель.
Итак, это три метода, которые вы можете использовать для удаления скрытых апострофов из ячеек в Excel.
Есть и другие способы сделать это, но я считаю эти 3 наиболее эффективными.
Этот вопрос задает один из участников нашего сообщества в Facebook. Он застрял в общей проблеме. Ведущие апострофы. Это часто бывает, если данные извлекаются из известного бизнес-приложения Lotus Notes. Если данные копируются или извлекаются в Excel, эти нежелательные символы мешают нормальной работе Excel.Так что давайте избавимся от них, и это тоже довольно просто.
Загрузите этот пример рабочей книги Excel, чтобы попрактиковаться в методах, приемах и концепциях, которые мы изучаем в этом руководстве.
Используйте функцию «Найти и заменить» — осечка!
Уважаемый участник попытался использовать параметр поиска и замены, который я обсуждал ранее, с помощью которого мы смогли заменить определенную строку из данных Excel на желаемый результат. Здесь он попытался заменить апостроф (') пустым и поэтому оставил поле «Заменить на» пустым, тогда как в поле «Найти что» он упомянул апостроф. Но при нажатии кнопки «Найти или заменить» Excel вернул ошибку, что не может найти необходимые данные. Это, безусловно, вызывает недоумение, что наши глаза буквально видят это, как Excel стал слишком глупым, чтобы найти его? Что ж, есть причина, которая фактически спасла Excel от полнейшего позора в ваших глазах 🙂
Почему Excel не может найти апостроф?
Excel спроектирован так, чтобы быть совместимым с IBM Lotus Notes, и с самого начала, насколько я читал, он основан на платформе Lotus Notes, поэтому он также имеет многие из его функций. В Lotus Notes начальный апостроф означает данные, выровненные по левому краю. Если вы перейдете в «Параметры Excel»> «Дополнительно»> «Совместимость с Lotus»> «Проверить выбор клавиш навигации перехода»> «ОК» и вернуться к своим данным, вы увидите, что Excel покажет выбранную ячейку с выравниванием по левому краю. Если вы измените положение по центру, знак изменится на ^ (карат), а если выровнять по правому краю, он изменится на ” (цитата).
По сути, эти символы являются индикаторами и на самом деле не являются частью текста. Другими словами, их на самом деле не существует, поэтому Excel тоже их воспринимает, но каким-то образом искажает данные. И даже после включения параметра совместимости Excel по-прежнему не может его найти.
Пригласите специалиста! – Использовать специальную вставку
Шаг 1. Выберите данные с помощью мыши или, удерживая нажатыми клавиши Ctrl+Shift и нажимая клавиши со стрелками.
Шаг 2. Скопируйте данные, щелкнув правой кнопкой мыши или используя сочетание клавиш Ctrl+C
Шаг 3. Перейдите к другому столбцу на том же или другом листе. Нажмите Alt+Ctrl+V (это вызывает специальное диалоговое окно вставки), выберите значения и нажмите OK. Теперь у вас есть данные без начальных апострофов.
Шаг 4. Апостроф остался? Выберите «Файл» > «Параметры» > вкладка «Дополнительно» > прокрутите вниз до пункта «Совместимость с Lotus» > снимите флажок «Переходные клавиши навигации» > нажмите кнопку «ОК».
В другом столбце? Серьезно.
Ну… да! По какой-то причине, если вы попытаетесь вставить значения в тот же столбец, где на самом деле находятся данные, это не сработает (в большинстве случаев). Мы можем очень легко преобразовывать формулы с их результирующими значениями в пределах одного столбца, но почему-то это не работает. Причина? Я этого еще не знаю. Если кто-то из вас знает, почему это так, пожалуйста, поделитесь со мной в поле для комментариев ниже.
Личное избранное — использование текста в столбец
Во многих случаях вы не можете использовать вспомогательный или дополнительный столбец для работы. В таких случаях:
- молитесь, чтобы ваши данные были только числовыми, такими как цифры, числа, время или дата и т. д.
- использовать функцию преобразования текста в столбец в Excel
Шаг за шагом
Шаг 1. Выберите данные с помощью мыши или клавиатуры
Шаг 2. Перейдите на вкладку "Данные" > группу "Инструменты данных" > кнопку "Текст в столбец"
Шаг 3. Когда появится диалоговое окно. Ничего не делайте и нажмите кнопку Готово.
Очень быстро! Ваши данные будут преобразованы в числа или даты в правильном формате. Однако обратите внимание, что это не работает с текстом, т. е. если текст следует за начальным апострофом, то этот метод вообще не работает.
Использовать формулу – DATEVALUE / VALUE и т. д.
Исправление дат
Мы также можем использовать формулы для преобразования чисел, хранящихся в виде текста, в числа для фактического выполнения вычислений. Например, если это дата, вы можете использовать функцию DATEVALUE, чтобы преобразовать ее в рабочую дату.
Например, чтобы преобразовать даты в столбце B в правильный формат, выполните следующие действия:
Шаг 1. В ячейку E2 введите следующую формулу: =DATEVALUE(B2) и нажмите Enter.
Шаг 2. Теперь у вас будет какое-то число. Чтобы отформатировать его как правильную дату, перейдите на вкладку «Главная» > «Группа чисел» > в раскрывающемся списке выберите «Короткая» или «Длинная дата».
То же самое можно сделать, нажав Ctrl+1 и в диалоговом окне слева выберите Дата. Из вариантов справа выберите наиболее подходящий формат и нажмите OK.
Шаг 3. Перетащите маркер заполнения в конец диапазона, чтобы получить правильные даты в остальной части столбца.
Исправление чисел (числовых значений)
Точно так же, если у вас есть числа, дата или время, вы можете использовать функцию ЗНАЧЕНИЕ, чтобы преобразовать их в фактическое состояние и выполнить вычисления.
Чтобы преобразовать время в правильный формат, указанный в столбце C, выполните следующие действия:
Шаг 1. В ячейку F2 введите следующую формулу: =ЗНАЧ(C2) и нажмите клавишу Enter
Шаг 2. Вы должны отформатировать ячейку в формате времени так же, как вы сделали это для даты выше.Для этого используйте вкладку «Главная» или вызовите диалоговое окно «Формат числа», нажав Ctrl+1.
Помните, что в этом случае вам понадобится дополнительный столбец, например, если вы хотите применить формулу. После преобразования текста в числа вы можете заменить исходные данные правильными или использовать новый столбец для расчетов.
Должны ли мы действительно беспокоиться об апострофе в тексте?
Во всех приведенных выше ситуациях вы, должно быть, заметили, что все примеры связаны с числами. Но что, если у нас есть текст после начального апострофа?
Ну, я бы задал себе вопрос: действительно ли мне нужно беспокоиться? Я знаю, что это ленивый подход, но давайте спросим. Ну ответ НЕТ. По крайней мере, для меня это не вызвало никаких проблем. Чтобы доказать это себе, я провел логический тест, и, что удивительно, если сравнить один и тот же текст в двух ячейках, в одной с апострофом, а в другой без апострофа, тест окажется ИСТИННЫМ.
Это означает, что это действительно не проблема, по крайней мере, в случае использования формул и функций. Я еще мало знаю о его последствиях для VBA, но в той мере, в какой я его представил, это не было для меня проблемой. Возможно, именно поэтому Microsoft оставила без внимания апостроф для текста.
Если вы по-прежнему настаиваете на том, что не хотите его удалять, мы можем, по крайней мере, его скрыть. Перейдя к параметрам Excel, снимите флажок «Клавиши перехода».
Но иногда, прежде чем это сработает с текстом, вам, возможно, придется использовать специальный подход вставки, описанный выше, и вы избавитесь от упрямого апострофа или, по крайней мере, он не покажет свое лицо.
Читайте также:
|
