Как убрать апостроф в ячейке excel

Сводные таблицы Excel

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel, далек от совершенства. Если он был введен другими пользователями или выгружен из какой-нибудь корпоративной БД или ERP-системы не совсем корректно, легко может содержать следующее.

лишние пробелы перед, после или между словами (для красоты!);
ненужные символы («г.» перед названием города);
невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция);
апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки).

Давайте рассмотрим способы избавления от такого «мусора».

Замена символов

Старый, но не устаревший трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная → Найти и выделить (Home → Find & Select → Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы в данном случае можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов.

Старый, но не устаревший трюк

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно.

СЖПРОБЕЛЫ удаляет из текста все пробелы, кроме одиночных пробелов между словами

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел, – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справиться не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:

Текст, в котором производим замену.
Старый текст – тот, который заменяем.
Новый текст – тот, на который заменяем.

С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом).

Функция ПОДСТАВИТЬ может легко избавиться от ошибок

Удаление апострофов в начале ячеек

Апостроф – это специальный символ

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. Апостроф виден только в строке формул, на листе его не видно.

По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная → Число → Текстовый), и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.

Но иногда апостроф оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда его текст.

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

Теперь если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик → кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Замена апострофа (‘) Excel

Прочее. Программное обеспечение

Сообщение от Малюк:
Пробывал через замену. Ставил найти ‘ заменить на ничего. Пишет, что апострофа не нашёл.
Вопрос такой: как удалить (не вручную, а через замену или как-то подругому за один раз и всё сразу) апострофы из ячеек документа?

Апостроф в Эксель является служебным символом, который указывает, что ячейка является текстовой.

Сообщение от Солидворкер:
Апостроф в Эксель является служебным символом, который указывает, что ячейка является текстовой.

Это я понимаю. Вот мне и нужно выражение типа ‘=450*40 переделать в =450*40. Если я могу сделать руками то наверно тоже самое может сделать и компъютер.

Сообщение от Pum-purum:
Вот эта штука работает несмотря на кажущуюся нелепость

У меня не хочет, смотри вложенный файл «Что получается»

И тоже сообщение с другим кодом «Что выходит»

Малюк, я перепробовал все известные мне хитрости касаемо MS Excel, множество редко используемых функций и т.п. Ничего не помогает. На мой взгляд, чтобы решение было сравнительно быстрым без лишнего загромождения листа, сделайте следующее:
1. Выделяете нужный диапазон ячеек.
2. Выполняете команду Главная=>Редактирование=>Очистить=>Очистить форматы
3. В строке формул апострофы исчезнут, но в ячейках по-прежнему останется запись наподобие =0,07*0,250
4. Не снимая выделение ячеек жмите на клавиатуре последовательно F2 и Enter

Читать еще: Как начертить диаграмму в excel

P.S. Такой способ не вершина инженерной мысли, но все же. Это на примере офиса 2007

Способ который нашёл я:
1. Указываем пустую ячейку и из контекстного меню нажать копировать.
2. Ctrl+A затем контекстное меню-специальная втавка поставить галку на форматы нажать ОК
3. Ctrl+H в нём
найти:1 заменить на:1 нажать «Заменить всё»
найти:2 заменить на:2 нажать «Заменить всё»
.
найти:9 заменить на:9 нажать «Заменить всё»
Всё теперь всё без апострофов

Сам тоже рассматривал возможность использования специальной вставки, правда пробовал с домножением на единицу. Видимо, в этом вопросе нет решения с 2-3 простыми действиями. С макросами, к сожалению, я не силен. Помочь в этом деле не смогу.

P.S. Наверно остается только радоваться что в качестве основной пользуемся системой счисления из 10 цифр

Сообщение от :
Вот как-бы макрос на это дело написать

Так включите запись макроса перед началом и остановите после. Он сам и напишется, хотя грубовато, но работать будет.

Сообщение от Малюк:
опрос такой: как удалить (не вручную, а через замену или как-то подругому за один раз и всё сразу) апострофы из ячеек документа?

Если правильно понял — это результат импорта откуда-то.
У меня получилось победить так:
1. Выделяем только «гадкие» ячейки
2. Наблюдаем рядом желтый ромб с потягушкой
3. Тянем-потянем, выбираем преобразовать в число

Сообщение от :
Наблюдаем рядом желтый ромб с потягушкой

Сообщение от :
выбираем преобразовать в число

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

Сообщение от Малюк:
Способ который нашёл я:
1. Указываем пустую ячейку и из контекстного меню нажать копировать.
2. Ctrl+A затем контекстное меню-специальная втавка поставить галку на форматы нажать ОК
3. Ctrl+H в нём
найти:1 заменить на:1 нажать «Заменить всё»
найти:2 заменить на:2 нажать «Заменить всё»
.
найти:9 заменить на:9 нажать «Заменить всё»
Всё теперь всё без апострофов

Не надо ничего писать. Вы поменяли формат, но его отображение меняется только в момент ввода в ячейку (нажатие Энтера). Сэмулировать его можно точно так же, найти — заменить, но не цифры, а знак равенства, «=». Он есть во всех «нужных» ячейках, и после одного единственного поиска/замены все станет правильно.
Ну или воспользуйтесь советом Солидворкера: выделить ячейки, скопировать, вставить в ворд, выделить получившуюся в ворде табличку, скопировать, вставить в эксель. Профит.

Сообщение от kp+:
Это, конечно, гробокопательство, но если кому интересно: в Excel 2010, а может, и в более ранних версиях, на вкладке «Данные» есть инструмент «Текст по столбцам», который и позволяет выполнить необходимое действие. В качестве разделителя указать тот самый апостроф

Как заставить Excel игнорировать Апостроф в начале ячейки

Я пишу инструмент, который синхронизирует базы данных с листов Excel. Каждый элемент таблицы в базе данных соответствует одной строке на листе. Я прочитал лист Excel в инструмент с помощью C# и интерфейса Excel interop com, а затем сравнил значения элементов (т. е. один из Столбцов на листе excel) после синхронизации, чтобы убедиться, что они равны.

вчера я нашел случай, когда сравнение было неверным:

второй — Я прочитал из Excel, и, как вы можете видеть, он пропустил первый Апостроф. Есть ли способ сказать Excel, чтобы рассматривать ячейку как просто текст (нет, просто установка форматирования ячейки не помогает)?

Я даже попытался скопировать значение (‘привет’) ячейки в VBA, как это:

результатом было то, что значение целевой ячейки всегда привет’

Если нет никакого способа, мне придется сделать что-то уродливое, как

4 ответов

Я боюсь Апостроф ‘ является специальным символом для Excel, когда он появляется как первый символ в ячейке, как вы нашли. Он говорит Excel рассматривать остальную часть строки как текст, так что вы можете ввести что-то вроде ‘34.2 в ячейке, и он будет рассматривать его как строку вместо числа (для форматирования и так далее).

Я предлагаю сделать что-то похожее на то, что вы предложили, за исключением того, что там, где вы помещаете его в Excel, проверьте первый символ и добавьте дополнительный ‘ Если там уже есть один.

кроме того, вы можете добавить Апостроф ко всем значениям — если вы хотите, чтобы все они были текстом. Таким образом, вам не понадобится дополнительная проверка первого символа.

посмотреть PrefixCharacter свойства Range объект, который соответствует этой ячейке

если TransitionNavigKeys свойства False , этот символ префикса будет ‘ для текстовой метки, или пустой. Если TransitionNavigKeys недвижимость True , этот персонаж будет для left-justified label, » for a правая метка, ^ для a центрированная метка, для повторения этикетка или пустая.

на TransitionNavigKeys часть относится к совместимости Lotus 1-2-3, поэтому она, скорее всего, будет False

ответ на основе статьи на сайте:

(предупреждение: может появиться немного раздражающее всплывающее окно)

редактировать: на самом деле это, вероятно, не будет никакой пользы, потому что PrefixCharacter доступно только для чтения 🙁

edit2: я был прав первый раз. PrefixCharacter заполняется только в том случае, если значение, добавленное в ячейку, начинается с » so just read back PrefixCharacter плюс Value и сцепления. Пока TransitionNavigKeys и False , что составляет

попробовать targetcell.Value вместо. .Formula — это формула, видимая в строке формул, в то время как .Value — вычисляемое значение ячейки.

Читать еще: Неразрывный пробел в excel

Итак, я предполагаю, что вы бы использовать .Formula в исходном коде, а также. Изменение этого должно сработать.

EDIT: хорошо, это не сработало (смущенно).

Excel специально обрабатывает начальную одинарную кавычку.. так специально, что даже неясные свойства ячейки / диапазона не имеют доступа. Единственное решение, которое я смог найти, это по сути, то же самое, что вы думали изначально. Вот:

вы можете попробовать предварительно ожидать одну цитату в текстовых полях («»+dbField ) в вашем запросе, чтобы для полей со встроенными одинарными кавычками ваш запрос возвращал:

который при размещении в ячейке Excel преобразуется в:

для символов, которые не были в кавычках, вы получите:

который при размещении в ячейке Excel преобразуется в:

Удаление апострофа

Вот такой апостроф выгружается из 1С вместе с текстом. Как его убрать сразу во всём документе.

Сведения о вопросе

Ответы (13) 

* Попробуйте выбрать меньший номер страницы.

* Введите только числа.

* Попробуйте выбрать меньший номер страницы.

* Введите только числа.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Здравствуйте!
Нажмите ctrl+f, выберите «заменить».
Введите в найти этот апостраф, а в заменить ничего не вводите.

Далее нажмите «заменить всё».
Он будет удален со всего документа.
Удачи вам!

__
Если мой ответ вам помог, пожалуйста, отметьте его как ответ.

Disclaimer: В ответе могут быть ссылки на сайты не на сайты Microsoft, Все советы с таких сайтов Вы выполняете на свой страх и риск.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

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

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

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Добрый день.
Покажите пожалуйста проблему картинками.

В ячейке апостроф отсутствует а в строке формул он есть.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Выделите нужные ячейки. Используйте Найти и Заменить примерно так.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Если вы спрашиваете о значке перед текстом:

то этот значок для Формата «Общий» устанавливаемый по умолчанию для новых файлов Excel , говорит Excel, что введённое значение в ячейку, считать текстом и выводить на экран монитора так, как введено. Попробуйте ввести без апострофа значение 0605180, первый 0 у вас будет потерян.

И чем вас так обидел апостроф перед введённым текстом?

Это не баг Excel .

P.S. И да, в вашем примере как и на представленном вами скриншоте, перед апострофом есть лишний пробел:

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Если вы спрашиваете о значке перед текстом:

И чем вас так обидел апостроф перед введённым текстом?

Это не баг Excel .

P.S. И да, в вашем примере как и на представленном вами скриншоте, перед апострофом есть лишний пробел:

Вы пишете про лишний пробел. Там нет пробела — это апостроф который виден только в строке формул. А в ячейке этот апостроф EXCEL НЕ ВИДИТ и соответственно найти и заменить его НЕВОЗМОЖНО.

Читать еще: Какое приложение замедляет видео

Этот ответ помог 1 пользователю

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Зачистка текста

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

лишние пробелы перед, после или между словами (для красоты!)
ненужные символы («г.» перед названием города)
невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция)
апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

Давайте рассмотрим способы избавления от такого «мусора».

Замена

«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов:

Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем

Удаление апострофов в начале ячеек

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂