Excel: Невидимый апостроф
Часто при нумерации счетов или счетов-фактур требуется ввести число с несколькими нулями впереди, например: 0005. Но сделать это не удается, так как программа EXCEL автоматически преобразовывает его в 5.
Выйти из подобной ситуации можно, преобразовав формат ячейки из «общего» в «текстовый». Но есть способ быстрее и удобнее.
Необходимо перед числом поставить значок апострофа ‘ , который находится на одной клавише с буквой «Э», в английской раскладке клавиатуры.

Символ будет виден только в строке формул. В самой рабочей области и при печати его видно не будет.


Также можно использовать апостроф для предотвращения превращения названия сайта в гиперссылку, если поставить его перед адресом сайта.
База Знаний: Calc. Символ «’» перед числом
Если перед числами в строке ввода стоит символ «’», то в этом случае цифры не распознаются как числа.
Символ «’» (апостроф) является символом принудительного форматирования данных «как текст», поэтому он не отображается в ячейках; увидеть его можно только в строке ввода перед данными.
«Язык формул» электронных таблиц разработан задолго до появления MS Windows и Microsoft Office. Символ «апостроф» в начале содержимого ячейки всегда был символом принудительного форматирования содержимого «как текст». Данный способ форматирования является преимущественным перед форматом ячейки.
Так работали Quatro Pro, Super Calc, таблицы Lotus 1-2-3.
Так работают и все современные электронные таблицы.
«’» перед цифровыми значениями следует использовать, когда вводятся константы, не являющиеся числами. Например — банковские и почтовые реквизиты: номера расчётных и корреспондентских счетов, индексы; различные идентификаторы.
Отмена принудительного текстового форматирования
Для единичной отмены принудительного текстового форматирования в ячейке следует в строке ввода удалить ведущий символ «’» (апостроф).
Для массовой отмены принудительного текстового форматирования следует воспользоваться регулярными выражениями. Для этого нужно:
- вызвать диалог поиска и замены Ctrl + F ;
- ввести в строке Найти: .+
- ввести в строке Заменить: &
- щёлкнуть кнопку Больше параметров и установить флаг Регулярные выражения
- щёлкнуть кнопку Заменить все
При необходимости можно обработать либо заданный диапазон, либо все листы.
| «’» перед цифровыми значениями не обнаруживается средствами поиска. |
Зачистка текста
Давайте рассмотрим способы избавления от такого «мусора».
Замена
«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (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 воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:
Sub Replace_Latin_to_Russian() Rus = "асекорхуАСЕНКМОРТХ" Eng = "acekopxyACEHKMOPTX" For Each cell In Selection For i = 1 To Len(cell) c1 = Mid(cell, i, 1) If c1 Like "[" & Eng & "]" Then c2 = Mid(Rus, InStr(1, Eng, c1), 1) cell.Value = Replace(cell, c1, c2) End If Next i Next cell End Sub
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂
Ссылки по теме
- Поиск символов латиницы в русском тексте
- Проверка текста на соответствие заданному шаблону (маске)
- Деление «слипшегося» текста из одного столбца на несколько
Как убрать апостроф в ячейке excel
апостроф это признак того что в поле записан текст.
вы когда ставите курсор на ячейку, видите справа от ячейки малельний квадратик с восклиц знаком, жмите на него и выберите пункт «преобразовать в число».
выделить все данные и по аналогии преобразовать в числа.
вариант два в соседнюю ячейку напишите =ЗНАЧЕН(A1), протяните, скопируйте столбец и вставьте значения.
06.09.2010 17:53:19
подскажите пожалуйста как убрать апостроф справа, так чтобы число не преобразовывалось в формулу, а оставалось искомым числом.
помогите оч оч надо
Прикрепленные файлы
- post_152537.xls (14 КБ)
Пользователь
Сообщений: 11312 Регистрация: 01.01.1970
06.09.2010 17:56:59
Никак. Только текст.
помогите оч оч надо
Пользователь
Сообщений: 1220 Регистрация: 13.05.2010
06.09.2010 18:02:35
Не уверен, но попробуйте в «Настройка региональных параметров» посмотреть.
06.09.2010 21:40:48
Переместить апостроф влево.
Можно формулой убрать апостроф — числа сами в текст преобразуются:
=ПСТР(A1;1;ДЛСТР(A1)-1)
Таким образом Вы не потеряете данные и сможете работать с этими цифрами(но как с текстом!).
Кстати, там не апостроф, а косая запятая.
да эта формула преобразует,но только первый столбец т.к там невидимый апостроф слева.
подскажите как убрать косую запятую. этот эксель ни как не хочет его удалять в формулу преобразует и всё там. через правка—>заменить, не выходит(
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.09.2010 21:56:31
А это, случайно, не номера счетов?
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
06.09.2010 22:06:00
Вчера только выкладывал:
Public Sub ExtractNumber()
Dim i As Integer, str As String, s As String, c As Range
For Each c In Selection.Cells
s = c
For i = 1 To Len(s)
If InStr(1, «1234567890,.», Mid(s, i, 1)) <> 0 Then str = str & Mid(s, i, 1)
Next
c = CDbl(str)
str = «»
Next
End Sub
Выделяем диапазон и выполняем макрос.
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 3139 Регистрация: 23.12.2012
06.09.2010 22:06:04
В ячейку B1 введите =ПОДСТАВИТЬ(A1;»`»;»»)
и протяните вниз. После этого можно скопировать полученный диапазон и специальной вставкой (только значения) вставить в нужный диапазон.
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
06.09.2010 22:13:22
И если хочется формулой, то ЮДФ:
Public Function ExtractNumber(s As String)
Dim i As Integer, str As String
For i = 1 To Len(s)
If InStr(1, «1234567890,.», Mid(s, i, 1)) <> 0 Then str = str & Mid(s, i, 1)
Next
ExtractNumber = CDbl(str)
End Function
Так вроде не должно зависеть от региональных настроек.
Hugo любезно выложил, спасибо ему(я уж и забыл):
http://www.planetaexcel.ru/forum.php?thread_id=18847&page_forum=1&allnum_forum=33
Я сам — дурнее всякого примера! .
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.09.2010 22:23:29
сентябрь, если это номера счетов (на это указывает количество символов), то преобразование в число не требуется — ведь Вы не собираетесь с ними никаких математических действий производить?
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
06.09.2010 22:42:25
Черт, я вообще-то на первый пример отвечал. Ща только увидел второй:-(
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
06.09.2010 22:45:54
И всего-то строку:
ExtractNumber = CStr(str)
так записать:-)
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
06.09.2010 22:48:19
Даже:
ExtractNumber = str
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 2867 Регистрация: 17.01.2013
07.09.2010 07:48:45
1. =A1&»»
2. Протягиваете на все ячейки.
3. Копируете и вставляете туда же как значения. Злополучный ‘ исчезнет.
Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/
Пользователь
Сообщений: 2867 Регистрация: 17.01.2013
07.09.2010 13:50:42
Во-первых: посмотрите дату первого сообщения.
Во-вторых: почитайте мое сообщение от 02.12.2009, 11:43 — Вы явно не посмотрели пример(post_81472.xls) прежде чем советовать.
В-третьих: То, что Вы предлагаете делается без формул и доп.столбцов так, как писал Марчук в посте от 02.12.2009, 11:10. простым преобразованием в число.
Это я к тому, что прежде чем писать неплохо бы почитать, что уже предлагали.
Во-первых: прошу нижайше простить меня. Как я вообще смел что-либо написать. Я ведь всего лишь гость на этом сайте, и мог бы догадаться, что получу разнос за неумелую попытку помочь, не посмотрев на дату первого сообщения. Как, прочем, и KuklP (сообщение от 06.09.2010).
Во-вторых: я явно посмотрел пример(post_81472.xls) прежде чем советовать.
В-третьих: То, что Я предложил, конечно же, только один из способов, и, конечно же, может делаться без формул и доп.столбцов так, как писал Марчук в посте от 02.12.2009, 11:10. простым преобразованием в число.
В-четвертых: Какая собака Вас укусила, что Вы на меня так набросились? Если бы данная тема не содержала двух тем (вторая — от Сентябрь за 06.09.2010), то . этого словесного фехтования не было бы.
В-пятых: прошу (на всякий случай) прощения у KuklP и Сентябрь за упоминание вскольз в данном сообщении.
В-шестых: Постараюсь впредь не лезть со своими советами на данный форум. Прошу простить за неловкое вторжение.
В-седьмых: То, что написано во в-шестых — вру, не дождётесь 😀