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

Как удалить символ в ячейке Excel?

Как удалить ячейки в Excel: пустые, лишние, убрать пробелы в конце или начале, удалить часть текста из содержимого | 📝Справочник по Excel

1. Щелкните вкладку Файл.
2. Щелкните кнопку Параметры.
3. Щелкните кнопку Advanced.
4. Перейдите в раздел Общие и установите флажок Подтверждать преобразование формата файла при открытии. …
5. Закройте и снова откройте файл.

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

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

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

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

Как удалить пробелы в Excel?

Как отобразить скрытые символы в Excel?

Функция ТЕКСТ в Excel

Преобразовывает числовые значения в текстовые и задает формат отображения посредством специальных строк форматирования. Удобна в тех случаях, когда нужно показать числа в определенном формате или объединить их с текстом, символами.

=ТЕКСТ(числовое_значение_или_формула_в_результате_вычисления_которой_получается_число;формат_ который_требуется_применить_к_указанному_значению)

Для определения формата следует предварительно клацнуть по значению правой кнопкой мышки – и в выпадающем меню выбрать одноименную опцию, либо нажать сочетание клавиш Ctrl+1. Перейти в раздел «Все…». Скопировать нужный формат из списка «Тип».

Чтобы понять, как работает функция, необходимо открыть книгу и создать таблицу с данными.

1. Кликнуть по любому свободному месту, например, G Ввести знак «=» и ссылку на адрес ячейки – B2. Активировать Мастер функций, нажав на кнопку fx (слева) во вкладке «Формулы», или с помощью комбинации клавиш Shift+F3.
2. На экране отобразится окно Мастера. В строке поиска ввести название функции и нажать «Найти».
3. В списке нужное название будет выделено синим цветом. Нажать «Ок».
4. Указать аргументы: ссылку на число и скопированное значение формата. В строке формулы после B2 вписать знак «&».
5. В результате появится сумма в денежном формате вместе с наименованием товара. Протянуть формулу вниз.

Еще один пример.

Необходимо объединить текстовые и числовые значения с помощью формулы =A14&» «&»составляет»&» «&B14&»,»&» «&A15&» «&ТЕКСТ(B15;»ДД.ММ.ГГ;@»).

Таким образом любые данные преобразовываются в удобный формат.

Как извлечь подстроку перед определенным символом.

Удалить пунктуацию с помощью регулярных выражений

Поскольку слова в русскоязычных текстах, как правило, состоят только из символов кириллицы, латиницы и цифр — наиболее простым способом быстро оставить их — применить синтаксис регулярных выражений.

Подробнее о них можно почитать в этой статье. Если кратко, их поддержки в Excel «из коробки» нет, но подключить библиотеку с помощью VBA можно.

Эту задачу я решил за вас, все что остается — подключить надстройку !SEMTools к вашему Excel, и функции начнут работать. Причем эти функции доступны в бесплатной версии !SEMTools.

Регулярное выражение для удаления всех символов, кроме букв, цифр и пробелов, будет таким:

Как работает формула:

1. Функция REGEXREPLACE заменяет все символы, кроме перечисленных диапазонами в квадратных скобках, на пробел.
2. СЖПРОБЕЛЫ удаляет лишние пробелы

Удаление повторяющихся и ненужных символов

ПЕЧСИМВ (функция ПЕЧСИМВ)

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке).

В этой статье описаны синтаксис формулы и использование функции ПЕЧСИМВ в Microsoft Excel.

Удаляет все непечатаемые знаки из текста. Функция ПЕЧСИМВ используется в том случае, когда текст, импортированный из другого приложения, содержит знаки, печать которых не возможна в данной операционной системе. Например, можно использовать функцию ПЕЧСИМВ, чтобы удалить низкоуровневые компьютерные коды, которые часто встречаются в начале или в конце файлов данных и не могут быть напечатаны.

Важно: Функция ПЕЧСИМВ предназначена для удаления из текста первых 32 непечатаемых знаков в 7-разрядном коде ASCII (значения 0—31). В кодировке Юникод имеются дополнительные непечатаемые знаки (со значениями 127, 129, 141, 143, 144 и 157)

Сама по себе функция ПЕЧСИМВ не позволяет удалить эти дополнительные непечатаемые знаки. Пример удаления этих дополнительных непечатаемых знаков из текста приведен в разделе десять способов очистки данных.

Аргументы функции ПЕЧСИМВ описаны ниже.

Текст — обязательный аргумент. Любые данные на листе, из которых нужно удалить непечатаемые знаки.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Как удалить невидимые особые знаки

В Google Sheets вы можете показывать формулы с помощью этих простых советов

Как получить слово после последнего пробела

Формула для удаления непечатных символов в Excel

​ a In ActiveSheet.UsedRange.SpecialCells(2).Areas​ = Application.Clean(ActiveSheet.UsedRange.SpecialCells(2).Value) End​ несколько дней назад​Guest​ проблемы.​ пустыми, но их​ непечатаемый символ абзаца​

Как удалить непечатные символы в Excel?

​ хочу я, а​vadi61​ все отображается нормально​ внешних источников, например,​(Word Options).​ пробела между словами​

​ПЕЧСИМВ(текст)​ уделить пару секунд​ писал вначале​: Какой формат? Вы​ a.Value = Application.Clean(a)​

​ запостил. в виде макроса​: НИКАК. ​nn​ считал COUNTA и​

​ ¶. . «​ в том где​, Ваше право опробовать​На компьютере коллеги​ из импортированных текстовых​Ещё можно включить отображение​ или сделали лишний​Аргументы функции ПЕЧСИМВ описаны​

Как удалить разрывы строк и непечатаемые символы

Убрать разрывы строк посредством формул Excel

Преимущества: есть возможность использовать цепочку формул для комплексной обработки данных. Например, можно убрать перенос строки и избавиться от лишних пробелов. 

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

Недостатки: необходимо создавать дополнительный столбец и выполнять вспомогательные действия.

1. Добавьте дополнительный столбец справа. Назовите его “1 линия”.
2. В первую ячейку данного столбца (C2) впишите формулу, которая уберёт разрыв строки. Ниже представлены разные сочетания, которые подойдут для всех случаев: 

Подходит для операционных систем Windows и Unix: 

=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””),CHAR(10),””)

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

=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””),CHAR(10),”, “)

Если нужно избавиться от всех непечатаемых символов, в том числе и разрывов строк, пригодится формула:

=CLEAN(B2)

3. Продублируйте формулу в других ячейках колонки. 
4. При необходимости данные из исходного столбца можно заменить конечным результатом:

• Выберите все ячейки в столбце C и нажмите сочетание Ctrl + C, чтобы скопировать данные.
• Теперь выделите ячейку B2 и нажмите сочетание Shift + F10, а затем V.
• Удалите дополнительный столбец.

Функция ПЕЧСИМВ

Как удалить пробелы в Excel

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

​ а то я​​ Выключаю «непечатаемые знаки»​ без принудительного разрыва​ Найти проделываете так​ как аргумент для​ параметр​В левой части диалогового​Формула​ функцию ПЕЧСИМВ, чтобы​:​RAN​ удобнее обозначать диапазоны?​: да, к сожалению​ — протестировать не​ в конце предложения!​ силен. Как его​ из 1С 7.7​ не вполне понимаю​ — уже нарисованная​ строки, а отметив​ — держите Alt​ функции СЖПРОБЕЛЫ. Благодаря​Показывать все знаки форматирования​ окна​Описание​

​ удалить низкоуровневые компьютерные​​Причину по которой​

​: А у меня​​ (если, к примеру,​ убивает и при​ возможно !!​myrecs​​ применить​Функция ПЕЧСИМВ() -​ о чем речь​ таблица пропадает​ Перенос текста в​ и на цифровой​ такой формуле мы​(Show all formatting​Параметры Word​Результат​ коды, которые часто​ XLTOOLS переводит в​ НЕ получается пройти​ у меня в​ фильтре не работает!​myrecs​: В PLEXе есть​Guest​ не помогла​ :)​Pelena​ формате, тогда нет​ клавиатуре набираете 0010​

​ одновременно очищаем текст​​ marks), который мы​(Word Options) нажмите​=ПЕЧСИМВ(A2)​ встречаются в начале​ числовой формат не​ XLTOOLSом. Я вообще​ 2 раза больше​

​ получается ерунда. PLEXовая​​: Сделал вот так​ функция удаления непечатаемых​​: Сам не силен,​Prog​kaa​

​: Файл можете приложить​​ символа — нет​ (ничего не будет​ от непечатных символов​

​ рассматривали чуть выше.​​ кнопку​Удаляет непечатаемые знаки СИМВОЛ(9)​ или в конце​ знаю (возможно у​​ про такое впервые​ диапазонов) ?​

​ работает хорошо, но​​ Sub test() Dim​​ символов, часто ей​ поэтому лопухнулся. Данный​: Попробуйте мой вариант:​

​: скопировать этот символ,​​ с такой таблицей?​ отображаения. А по-другому​ отображаться), в Заменить​

​ и лишних пробелов​​ Включение или выключение​​Экран​ и СИМВОЛ(10) из​ файлов данных и​ меня в системе​ слышу. И ничего​KuklP​ она для выделенного​

​ c As Range​​ пользуюсь в виду​ макрос подходит для​1) выбираете диапазон​ выделить столбец, нажать​

​dudic​​ — отображается и​ — ставьте 1​

​ или отступов.​​ одного напрямую влияет​(Display).​ строки текста в​ не могут быть​ глюк, но перепроверял​ непечатного не нащел.​: Чем Вас не​ диапазона, а когда​ For Each c​ специфики подготовки большого​

​ вордовского варианта. А​​2) жмете ctrl-h​ crtl+H и заменить​: Прилагаю файл​ в сводных, и​ пробел​vadi61​​ на состояние другого.​В группе параметров​ ячейке А2.​ напечатаны.​ пару десятков раз)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Мяу()​ устраивает предыдущий пример​ куча файлов хочется​ In ActiveSheet.UsedRange.Cells: c.Value​ объема «правильных» CSV​ для экселевского (здесь​3) find what​ все к чертовой​Pelena​ в диаграммах.​vadi61​

​: Возникла такая проблема.​​Обратите внимание, что символы​Всегда показывать эти знаки​Помесячный отчет​Важно:​Возможно ли макрос​Selection.Replace «.», «.»,​ от Казанского?​ чтоб сразу для​ = WorksheetFunction.Clean(c.Value): Next​ файлов. Мне хотелось​ и правда код​ : оставляете пустой​ бабушке. ​: У Вас таблица​Может стоит поковыряться​:​ В одной книге​ форматирования, которые Вы​ форматирования на экране​Помимо базового содержимого, в​ Функция CLEAN разработана, чтобы​ замены с точки​ 2​Ves​ всего листа.​

​: У Вас таблица​Может стоит поковыряться​:​ В одной книге​ форматирования, которые Вы​ форматирования на экране​Помимо базового содержимого, в​ Функция CLEAN разработана, чтобы​ замены с точки​ 2​Ves​ всего листа.​

​ End Sub​​ бы как то​ символа 10) пока​4) replace with:​

​mazayZR​​ скрытым текстом отформатирована.​ в настройках, но​ув. Fairuza,​ есть несколько листов​ выберете на вкладке​(Always show these​ документе Word есть​ удалить первые 32​ на запятую, E72:F421​End Sub​: Устраивает)) просто хочу​

Формула ПОДСТАВИТЬ

Кроме первых двух способов есть еще один метод, в котором используется специальная формула «ПОДСТАВИТЬ», если используется англоязычная версия, то «SUBSTITUTE».

Чтобы формула заработала нужно в соседнем столбике или строчке от того, где находятся числа с пробелами, ввести следующую формулу: =ПОДСТАВИТЬ(A1;» «;»»). На место A1 вписывается нужный адрес. После этого, при необходимости, формула копируется на все остальные ячейки и заменяется таким же способом, как в предыдущем методе. Таким образом можно удалить пробелы во всем столбце.

Типы ссылок в Excel – как использовать абсолютную, относительную и смешанную адресацию

Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы

Отображение непечатных символов

​ и сообщить, помогла​​Udik​ о чем? Где​ Next End Sub​myrecs​ — как-то так. ​В ворде это​: пытаюсь прикрепить пример. ​ переставал считать если​его ВИДНО?​ хочет Excel. И​ предложенный вариант или​ (Excel 2010) в​ файлов или подключения​ непечатаемых символов, нажав​ конец абзаца. Но​​ ниже.​​ ли она вам,​​: Вот Вы упорный,​ в файле хоть​Ves​​: нет. после этого​Function Replace_symbols(ByVal sStr​ не возможно, ибо​​nn​ их выбрать и​или просто текст​ если окончание первой​ нет.​ сводной таблице, в​ ​ к данным через​ на кнопку, похожую​ чтобы увидеть документ​Текст​

​ с помощью кнопок​​ в приведённом примере​ одно число? У​: Добрый день! Для​

​ в других файлах​​ As String) As​ символ является непечатаемым.​: пример​ нажать DEL.​

​ в две строчки​​ строки еще можно​ув. Fairuza,​ том месте, где​ канал интернета, могут​ на прописную латинскую​ таким, каким он​ — обязательный аргумент. Любые​ внизу страницы. Для​ непечатных знаков не​ чисел разделитель запятая,​

​ очистки текста от​​ беда получается​​ String Dim i​​ Но его можно​nn​Решили реплейсом в​ в одной ячейке​ отрегулировать шириной ячейки,​,​ находится перенос строки​ появляться непечатные символы.​ букву​ выйдет на печать,​ данные на листе,​​ удобства также приводим​ обнаружилось, по крайней​ а не точка.​ всего лишнего я​KuklP​ As Byte Dim​ скрыть (поищите кнопочку​: Нужно заменить невидимый​

​ диапазоне сначала пустую​​ расположен?​ то второй -​сработало. Еще раз​​ стоит квадтат.​​Вместо того чтобы удалять​​P​ нужно скрыть эти​​ из которых нужно​ ссылку на оригинал​ мере мешающих перевести​HoBU4OK​ ищу в каждой​: Тогда так:​ St As String​ «непечатаемые знаки» на​ символ на пробел!​ строку на случайный​возможно две причины:​ уже никак.​ спасибо.​Какую настройку в Excel​​ их вручную можно​(только зеркально повернутую).​ символы. Мы научим​ удалить непечатаемые знаки.​ (на английском языке).​ текст в числа.​: У меня получается​ ячейке коды символов​Sub NPC_del() Dim​ St = «*/\:?|»»»»

«​​ панели инструментов Стандартная).​Guest​ набор букв (типа​1- текст просто​Нет не стоит.​Но если у​ надо исправить, что​ воспользоваться специальной функцией​ Данный символ –​ Вас, как легко​Скопируйте образец данных из​В этой статье описаны​ Всё что надо​ пройти XLTOOLSом, и​ If Asc(s)

2 способа удалить пробелы между словами или числами в ячейках Excel — Microsoft Excel для начинающих

Как в Word отобразить непечатаемые символы

​ командой сайта office-guru.ru​ не зависимо от​ отображать их в​ функция CLEAN не​ Эта страница переведена​: О, начали вылазить​Помогите справится с​ себя Transpose. По​ в другом -​ слабости в программировании​nn​

​ документе? Потому что,​ удаляет эти дополнительные​ автоматически, поэтому ее​ забытые «мелочи».​ проблемой формата, а​ кр. мере до​ Type mismatch​ не смог. Если​: КЛАСС! Но остается​: выфайуауцйайа было лучше​Prog​ символа? И как​ какой-то из моих​

​ не отображается, а​​ул. Кривомазовская. 13​Перевела: Ольга Гелих​Нажмите​

​ когда видно эти​ непечатаемые символы. Пример​​ текст может содержать​​RAN​

​ точнее с непечатными​ 2007 включительно. Итого:​​прилагаю файл с​​ поможете буду благодарен.​

​ символ в конце​ :)​​: Был подобный глюк,​​ отобразить видимость знаков​ настроек, но какой. )​​ у коллеги -​​а в сводной​

​Правила перепечатки​​ОК​ символы, легче понять​​ удаления этих дополнительных​ неточности и грамматические​, будет несомненно рад​ символами в книге.​ Sub NPC_del() Dim​ ошибкой​Dima S​​ предложения после копирования​​Guest​ ячейки выглядели пустыми,​ форматирования, как Word?​Да так можно,​ отображается. Из этого​ таблице в одну​

​Еще больше уроков​​, чтобы сохранить изменения​​ расстановку интервалов и​ непечатаемых знаков из​ ошибки. Для нас​​ — переписывать код​​ Определяет число как​

​ a As Range​KuklP​:​ в WORD! Как​: А ведь он​​ если в них​​mazayZR​ но тогда разрыв​ я делаю вывод,​​ строку.​​ по Microsoft Word​ и закрыть диалоговое​​ компоновку документа.​​ текста см

в​​ важно, чтобы эта​​ несколько раз​

​ статье десять основных​ статья была вам​HoBU4OK​​пример в файле​​ Next For Each​​Sub NPC_del() ActiveSheet.UsedRange.SpecialCells(2).Value​​: функцию Hugo буквально​ пожалуйста!​ искать причину невидимой​ все равно были​». В тексте содержится​ том месте где​ в настройках Excel.​

​ компьютере (Excel 2007)​В импортированных данных с​​Параметры Word​​ где поставили два​​ способов очистки данных.​​ полезна. Просим вас​: я поэтому обобщенно​

Функция ЧИСТКА в Excel

Microsoft Excel ЧИСТЫЙ функция используется для удаления всех непечатаемых символов из данного текста.

Функция Примечание

Функция CLEAN удаляет первые 32 непечатаемых символа в 7-битном коде ASCII (значения от 0 до 31) из заданного текста. В наборе символов Unicode есть дополнительные непечатаемые символы (значения 127, 129, 141, 143, 144 и 157). Для этих дополнительных непечатаемых символов функция CLEAN не удаляет сама по себе.

Примеры

В этом разделе объясняется, как использовать функцию CLEAN с подробными примерами.

Пример 1. Удалите непечатаемые символы и разрывы строк из заданного текста.

Чтобы удалить непечатаемые символы вместе с разрывами строк из заданного текста, сделайте следующее.

1. Выберите пустую ячейку для вывода текста с удаленными непечатаемыми символами.

2. Скопируйте в нее приведенную ниже формулу и нажмите Enter . =CLEAN(B3)

3. Затем перетащите Ручка заполнения вниз, чтобы применить формулу к другим ячейкам, чтобы получить весь текст без непечатаемых символов.

Чтобы одновременно удалить непечатаемые символы, разрывы строк и лишние пробелы, вы можете сделать следующее.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter . =TRIM( CLEAN( C3 ))

Затем перетащите маркер заполнения вниз, чтобы получить другие тексты без непечатаемых символов и лишних пробелов, как показано ниже.

Пример 3. Проверьте, содержит ли ячейка непечатаемые символы

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

1. Выберите пустую ячейку рядом с ячейкой, которую вы будете проверять на наличие непечатаемых символов, скопируйте в нее формулу ниже и нажмите Enter . = ЕСЛИ (ОЧИСТКА (B3) = B3; «Нет»; «Требуется очистка»)

2. Перетащите маркер заполнения вниз, чтобы получить все результаты.

Внимание: Если в определенной ячейке есть непечатаемые символы, возвращается результат «Требуется очистка», в противном случае — «Нет». Удалить не буквенно-цифровые символы в Excel Предположим, что существует диапазон данных, таких как JAMES0898 # 4% ^ {}, который содержит не буквенно-цифровые символы на листе, и теперь вы просто хотите удалить не буквенно-цифровые символы и сохранить буквенно-цифровые символы

Например, JAMES0898 # 4% ^ {} > JAMES0898. В этой статье есть метод для вас.Нажмите, чтобы узнать больше ..

Удалить не буквенно-цифровые символы в Excel Предположим, что существует диапазон данных, таких как JAMES0898 # 4% ^ {}, который содержит не буквенно-цифровые символы на листе, и теперь вы просто хотите удалить не буквенно-цифровые символы и сохранить буквенно-цифровые символы. Например, JAMES0898 # 4% ^ {} > JAMES0898. В этой статье есть метод для вас.Нажмите, чтобы узнать больше …

Удалить буквенные символы из ячеек в Excel Предположим, вам нужно удалить все буквенные символы из диапазона в Excel, например, одними данными в ячейке могут быть «Лукас 13654698745″, И вы хотите, чтобы это было»13654698745”, Как можно быстро удалить все буквенные символы из ячеек? Есть ли какие-нибудь хорошие идеи для решения этой проблемы? Попробуйте методы, описанные в этой статье.Нажмите, чтобы узнать больше …

Удалить нечисловые символы из ячеек в Excel Предположим, у вас есть рабочий лист с большим количеством таких данных. ABCD4578124YUIOH, и вы хотите удалить только нечисловые символы ABCDYUIOH но оставьте числовые символы в ячейках. Попробуйте метод, описанный в этой статье.Нажмите, чтобы узнать больше …

Удалить не-буквенные символы из ячеек в Excel Например, у вас есть данные диапазона, такие как Джейкоб «001» @ # $%, и вы хотите удалить из строки все не-альфа-символы, чтобы сохранить ее как Иаков. Как в Excel быстро удалить все символы, отличные от альфа, из ячеек? В этой статье показаны два метода, которые помогут вам сделать это легко. Нажмите, чтобы узнать больше …

Быстрое удаление разрывов строк в Excel Иногда, когда вы копируете некоторые данные с веб-сайта или разделяете свои значения с помощью клавиш Alt + Enter, вы получаете несколько разрывов строк или возврата каретки, если вы хотите быстро удалить несколько разрывов строк в Excel, вы можете быстро это сделать с помощью методы в этой статье. Нажмите, чтобы узнать больше …

Четыре способа, как быстро удалить пробелы в Excel

Excel. Обнаружение лишних пробелов

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

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

• один ведущий пробел и более;
• один завершающий пробел и более;
• два и более последовательных пробела в самом тексте.

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

1. Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
2. Выполните команду Главная –>Условноеформатирование –>Создатьправило, чтобы открыть диалоговое окно Созданиеправилаформатирования (рис. 3).
3. В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
4. В области Измените описание правила введите формулу: =D2СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
5. Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
6. Нажмите Ok два раза.

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

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.

По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 183–185.

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

4 ответов

Если не работает …

Как убирать большие пробелы в ворде при выравнивании по ширине?

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

Для того, чтобы устранить большие пробелы, необходимо просто его удалить и поместить на его место нормальный с помощью синхронного нажатия трех кнопок.

Убираем большие пробелы во всём документе

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

Для их устранения требуется сделать следующее:

• Выделить текст полностью любым удобным способом, например, Ctrl+A;
• Перейти во вкладку разметки страницы;
• Сделать расстановку переносов автоматической.

Большие пробелы из-за межзнаковых интервалов

В случае, если пробелов стало больше из-за наличия строк с разрывом, то требуется сделать следующее:

• Перейти к меню Файла;
• Выбрать Параметры;
• Перейти к дополнительным параметрам.

После чего требуется пролистать параметры до самого низа и убрать галочку на строчке «Расширять межзнаковые интервалы в строке с разрывом». Данный параметр может отличаться в зависимости от версии программы, возможно в вашем случаи нужно на оборот, установить галочку.

Замена больших пробелов на обычные

Достаточно просто подменить большие пробелы на нормальные при помощи автозамены. Чтобы это совершить необходимо вызвать окно замены сочетанием клавиш Ctrl+H. В первую строку требуется вставить большой пробел (выделяем в тексте большой пробел, нажимаем Ctrl+С), а в строку на замену необходимо вставить созданное клавишами Shift+Ctrl+Пробел. Потом кликнуть по кнопке «Найти и заменить». Данный способ автоматически заменит все большие пробелы, которые присутствуют в документе.

Замена больших пробелов на обычные через программу Блокнот

Еще один способ, как убирать большие пробелы в ворде — это использовать программу Блокнот, которая есть на любой версии Windows. Для этого достаточно скопировать текст с Ворда в Блокнот, а потом обратно, вот и все.

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

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

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

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

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

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

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

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

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

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, а может, и в более ранних версиях, на вкладке «Данные» есть инструмент «Текст по столбцам», который и позволяет выполнить необходимое действие. В качестве разделителя указать тот самый апостроф

Как открыть редактор формул в Numbers на Mac

Удаление пробелов только слева или справа

Удаление с применением ПОДСТАВИТЬ

Количество символов в ячейке в Excel

При работе с данными иногда требуется посчитать количество символов в определенной ячейке. Вручную делать это неудобно, особенно когда значений много или содержимое меняется в зависимости от заданного условия. Для таких случаев используется функция ДЛСТР.

=ДЛСТР(ячейка_1)

Функция работает только с одним значением.

1. Выделить ту ячейку, где будет показан подсчет.
2. Вписать формулу, указывая ссылку на адрес определенной ячейки.
3. Нажать «Enter».
4. Растянуть результат на другие строки или столбцы.

Можно посчитать общее количество символов:

1. Выделить все значения, во вкладке «Главная» на панели справа найти инструмент «Сумма».
2. Кликнуть по одноименной опции. Рядом (под или с боковой стороны от выделенного диапазона) отобразится результат.

В разбросанных ячейках

В этом случае необходимо воспользоваться ДЛСТР и суммированием:

1. Установить курсор в желаемом месте.
2. Ввести формулу =ДЛСТР(значение1)+ДЛСТР(значение2)+ДЛСТР(значение3) и т.д.
3. Нажать «Enter».