Сжпробелы excel как пользоваться

Обновлено: 06.07.2024

С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.

Примечание: Функция ТЕКСТ преобразует числа в текст, что может затруднить их использование в дальнейших вычислениях. Рекомендуем сохранить исходное значение в одной ячейке, а функцию ТЕКСТ использовать в другой. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.

ТЕКСТ(значение; формат)

Аргументы функции ТЕКСТ описаны ниже.

Имя аргумента

Числовое значение, которое нужно преобразовать в текст.

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

Общие сведения

Самая простая функция ТЕКСТ означает следующее:

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

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

Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.

=ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ")

Сегодняшняя дата в формате ДД/ММ/ГГ, например: 14.03.12

=ТЕКСТ(СЕГОДНЯ();"ДДДД")

Сегодняшний день недели, например: понедельник

=ТЕКСТ(ТДАТА();"ЧЧ:ММ")

Текущее время, например: 13:29

=ТЕКСТ(0,285;"0,0 %")

Процентный формат, например: 28,5 %

Дробный формат, например: 4 1/3

Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью.

=ТЕКСТ(12200000;"0,00E+00")

Экспоненциальное представление, например: 1,22E+07

Дополнительный формат (номер телефона), например: (123) 456-7898

=ТЕКСТ(1234;"0000000")

Добавление нулей в начале, например: 0001234

Пользовательский формат (широта или долгота), например: 12° 34' 56''

Примечание: Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Чтобы изменить форматирование без формулы, нажмите клавиши CTRL+1 (на компьютере Mac — +1), а затем в диалоговом окне Формат ячеек на вкладке Число выберите нужный формат.

Скачивание образцов

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

Другие доступные коды форматов

Просмотреть другие доступные коды форматов можно в диалоговом окне Формат ячеек.

Нажмите клавиши CTRL+1 (на компьютере Mac — +1), чтобы открыть диалоговое окно Формат ячеек.

На вкладке Число выберите нужный формат.

Выберите пункт (все форматы).

Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.

Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — кнопку Отмена, чтобы закрыть диалоговое окно Формат ячеек.

Коды форматов по категориям

В примерах ниже показано, как применить различные числовые форматы к значениям следующим способом: открыть диалоговое окно Формат ячеек, выбрать пункт (все форматы) и скопировать нужный код формата в формулу с функцией ТЕКСТ.

Почему программа Excel удаляет нули в начале?

Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.

Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;"00000"), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.

Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (--), например: =--D4.

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

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

Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).

Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.

После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.

Способ отображения дат можно изменять, используя сочетания символов "Д" (для дня), "М" (для месяца) и "Г" (для года).

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "М" и "м", "Д" и "д", "Г" и "г".

Минда советует.

Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.

Способ отображения времени можно изменить с помощью сочетаний символов "Ч" (для часов), "М" (для минут) и "С" (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы "AM/PM".

Если не указывать символы "AM/PM", время будет отображаться в 24-часовом формате.

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "Ч" и "ч", "М" и "м", "С" и "с", "AM/PM" и "am/pm".

Для отображения десятичных значений можно использовать процентные (%) форматы.

Десятичные числа можно отображать в виде дробей, используя коды форматов вида "?/?".

Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.

В Excel доступны четыре дополнительных формата:

"Почтовый индекс" ("00000");

"Индекс + 4" ("00000-0000");

"Табельный номер" ("000-00-0000").

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

Типичный сценарий

Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку "Отчет напечатан 14.03.12" или "Еженедельный доход: 66 348,72 ₽". Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например "ДД.ММ.ГГГГ" для дат.

В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (" ") и значения: =A2&" "&B2.

Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.

Вот обновленная формула:

ячейка C2:=A2&" "&ТЕКСТ(B2;"дд.мм.гггг") — формат даты.

Вопросы и ответы

Как преобразовать числа в текст, например 123 в "сто двадцать три"?

К сожалению, это невозможно сделать с помощью функции ТЕКСТ. Для этого нужно использовать код Visual Basic для приложений (VBA). В следующей статье описано, как это сделать: Как преобразовать числовое значение в слова в Excel

Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН("привет") возвращает результат "ПРИВЕТ".

Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?

Да, но для этого необходимо выполнить несколько действий. Сначала выделите нужные ячейки и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек. Затем на вкладке Выравнивание в разделе "Отображение" установите флажок Переносить по словам. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.

В этом примере использована формула ="Сегодня: "&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ").

Почему Excel преобразует введенные числа во что-то вроде "1,22E+07"?

Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.

Минда советует.

Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.

Когда в Excel будут импортированы данные из других источников (базы данных, таблицы с интернет страниц и т.п.) без сомнения вместе с данными вы получите лишние ненужные отступы в значениях ячеек. Некоторые из них будут в начале текста, другие в конце.

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

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

Ниже на рисунке примера показано как можно удалить лишние пробелы и отступы с помощью функции СЖПРОБЕЛЫ:

Сама по себе функция СЖПРОБЕЛЫ абсолютно проста. Достаточно лишь указать в ее аргументе ссылку на ячейку с исходным текстом, а после функция автоматически удалит из него все пробелы и отступы. За исключением одиночных пробелов между словами текстовой строки.

Но из-за своей простоты функция СЖПРОБЕЛЫ существенно обделена своими возможностями. Поэтому ее лучше использовать в формулах в комбинации с другими текстовыми функциями для решения более сложных задач по удалению лишних пробелов и отступов в тексте.

Функция СЖПРОБЕЛЫ служит для удаления лишних пробелов только одного вида 32-го кода символа по стандарту ASCII таблицы. Однако в стандарте символов Unicode могут быть и другие символы пробелов или отступов табуляции. Например, символ твердый пробел. Очень часто используется на страницах интернет сайтов. Его код равен 160 по таблице символов Unicode стандарта.

Чтобы удалить лишние пробелы в строке текста, которая содержит «твердый пробел» с кодом 160, необходимо использовать формулу с комбинацией 3-х функций:

1. СЖПРОБЕЛЫ – удалит только лишние пробелы в текстовой строке, кроме пробелов между словами.
2. ПОДСТАВИТЬ – найдет текстовые символы с кодом 160 и заменит их все на пробел с кодом 32.
3. СИМВОЛ – введет любой текстовый символ по номеру кода стандарта указанному в аргументе функции.

Все эти три задачи можно выполнить за одну операцию применив не сложную формулу:

Мы просто нашли и заменили лишние символы на те, с которыми умеет работать функция СЖПРОБЕЛЫ, после чего она беспрепятственно справилась со своей задачей.

Полезный совет! Если нужно удалить все пробелы используйте функцию ПОДСТАВИТЬ вместо СЖПРОБЕЛЫ как показано на рисунке:

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

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

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

Как удалить лишние пробелы в Excel в автоматическом режиме? Чтобы не испортить зрение, вглядываясь в каждую ячейку и убирая лишние доступы клавишей Backspace, можно воспользоваться специальной функцией – СЖПРОБЕЛЫ.

Синтаксис функции СЖПРОБЕЛЫ в Excel

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

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

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

Пример использования функции СЖПРОБЕЛЫ

На небольшом примере рассмотрим, как пользоваться функцией СЖПРОБЕЛЫ. Имеем таблицу, в которой указаны наименования детских игрушек и их количество. Не очень грамотный оператор вел примитивный учет: при появлении дополнительных единиц игрушек он просто впечатывал новые позиции, несмотря на то, что аналогичные наименования уже есть. Наша задача: подсчитать общую сумму каждого вида.

Невооруженным глазом видно, что некоторые позиции написаны с явными лишними пробелами. Рассмотрим, к чему это может привести.

Подсчитывать общее количество позиций по каждому виду игрушек будем через функцию СУММЕСЛИ. Вводим ее, протягиваем на остальные ячейки и смотрим результат. Программа выдала ответ по плюшевым зайцам: 3. Хотя мы видим, что их должно быть 3+2=5. В чем проблема? В лишних пробелах.

Запишем функцию в ячейке D3. В качестве аргумента введем ячейку B3, в которой значится наименование игрушки.

Теперь протянем функцию до 14 строки и увидим, что тексты действительно выровнялись, потому что пробелы удалились. Проверим это наверняка, изменив диапазон в команде СУММЕСЛИ. Вместо B3:B14 пропишем D3:D14 и посмотрим результат. Теперь плюшевых зайцев действительно 5, медведей – 6 и т.п. Получается, символы табуляции играют важную роль, и их нужно подчищать.

СЖПРОБЕЛЫ с другими функциями

Использование функции СЖПРОБЕЛЫ вместе с другими функциями расширяет возможности пользователя. Логично, что она будет использоваться вместе с теми функциями, которые исследуют массивы и возвращают данные. В частности, это функции НАЙТИ, ЛЕВСИМВ, ПРАВСИМВ и др. На практике оказывается, что чаще всего СЖПРОБЕЛЫ используется вместе с ВПР.

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

Если мы протянем формулу до конца таблицы, увидим следующее:

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

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

Избавиться от лишних пробелов можно и без использования функции СЖПРОБЕЛЫ. Воспользуемся старым проверенным способом, который нам знаком еще из WORD – команда НАЙТИ-ЗАМЕНИТЬ.

Пример останется тот же самый. Выделяем столбец, в котором прописаны наименования игрушек и нажимаем CTRL+H. В появившемся окне напротив НАЙТИ проставляем пробел, а напротив ЗАМЕНИТЬ НА не пишем ничего.

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

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

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

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

Как убрать пробелы в Excel при помощи «Найти и заменить»

Это более быстрый метод, который может быть полезен в следующих ситуациях:

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

Итак, найдем и заменим двойные интервалы независимо от их расположения.

Вот как это можно сделать:

• Выделите ячейки, из которых вы хотите их удалить.
• Перейдите на главное меню -> Найти и выбрать -> Заменить.(Также можно использовать сочетание клавиш - CTRL + H ).
• В диалоговом окне «Найти и заменить» введите:
  • Найти: Двойной пробел.
  • Заменить на: Одинарный.

  Нажмите «Заменить все».

  Обратите внимание, что если у вас есть три интервала между двумя словами, то теперь вы получите два (один будет удален). В таких случаях вы можете повторить эту операцию снова, чтобы удалить любые двойные пробелы, которые ещё могли остаться.

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

  1. Чтобы удалить все пробелы в тексте, выполните следующие действия:
  • Выделите нужные ячейки.
  • Перейдите в меню Главная -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш - CTRL + H ).
  • В диалоговом окне «Найти и заменить» введите:

  Найти: одинарный пробел.

  Заменить на: оставьте это поле пустым.

  Это удалит все пробелы в выбранном диапазоне.

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

  Функция СЖПРОБЕЛЫ.

  Если ваш набор данных содержит лишние пробелы, функция СЖПРОБЕЛЫ может помочь вам удалить их все одним махом - ведущие, конечные и несколько промежуточных, оставив только один интервал между словами.

  Стандартный синтаксис очень простой:

  Где A2 - ячейка, из которой вы хотите удалить.

  Как показано на следующем скриншоте, СЖПРОБЕЛЫ успешно удалила всё до и после текста, а также лишние интервалы в середине строки.

  
  

  И теперь вам нужно только заменить значения в исходном столбце новыми. Самый простой способ сделать это - использовать Специальная вставка > Значения.

  Формулы для удаления начальных и концевых пробелов.

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

  
  

  Как вы уже знаете, функция СЖПРОБЕЛЫ удаляет лишние интервалы в середине текстовых строк, чего мы в данном случае не хотим. Чтобы сохранить их нетронутыми, мы будем использовать немного более сложную конструкцию:

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

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

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

  
  

  И обратите снимание, что ее нужно вводить как формулу массива (с Ctrl+Shift+Enter ). В столбце A выровнять по правому краю получилось плохо из-за разного количества концевых пробелов в каждой ячейке. В столбце B эта проблема решена, и можно красиво расположить текст.

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

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

  Функция СЖПРОБЕЛЫ может избавиться от пробелов, но не может устранить непечатаемые символы. Технически она предназначена для удаления только значения 32 в 7-битной системе ASCII , которое как раз и является кодом пробела.

  Чтобы удалить ещё и непечатаемые символы в строке, используйте её в сочетании с функцией ПЕЧСИМВ (CLEAN в английской версии). Как следует из названия, ПЕЧСИМВ предназначена для очистки данных от ненужного «мусора» и умеет удалять любой из первых 32 непечатаемых символов в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).

  Предполагая, что очищаемые данные находятся в ячейке A2, формула будет следующей:

  
  

  • Воспользуйтесь инструментом Excel «Заменить все»: в поле «Найти» введите возврат каретки, нажав сочетание клавиш Ctrl + J . И в поле «Заменить» введите пробел. При нажатии кнопки «Заменить все» все разрывы строк в выбранном диапазоне заменяются пробелами.
  • Используйте следующую формулу для замены возврата каретки (код 13) и перевода строки (код 10) на пробелы:

  =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);" "); СИМВОЛ(10); " "))

  Как видите, почтовый адрес в колонке С выглядит вполне читаемо.

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

  Если после использования формулы СЖПРОБЕЛЫ и ПЕЧСИМВ некоторые упрямые знаки все еще остаются, то скорее всего, вы скопировали / вставили данные из интернета или из другой программы, и несколько неразрывных пробелов все же прокрались в вашу таблицу.

  Чтобы избавиться от неразрывных пробелов (html-код   ), замените их обычными, а затем попросите функцию СЖПРОБЕЛЫ удалить их:

  Чтобы лучше понять логику, давайте разберем формулу:

  • Неразрывный пробел имеет код 160 в 7-битной системе ASCII, поэтому вы можете определить его с помощью СИМВОЛ(160).
  • Функция ПОДСТАВИТЬ используется для превращения неразрывных пробелов в обычные.
  • И, наконец, вы вставляете ПОДСТАВИТЬ в СЖПРОБЕЛЫ, чтобы окончательно удалить всё лишнее.

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

  Следующий скриншот демонстрирует разницу в результатах:

  
  

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

  Если взаимодействие трех функций, описанных в приведенном выше примере, не смогло устранить весь мусор из текста, то это означает, что оставшиеся знаки имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).

  В этом случае используйте функцию КОДСИМВ, чтобы сначала идентифицировать код мешающего вам знака, а затем используйте ПОДСТАВИТЬ, чтобы заменить его обычным пробелом. А затем при помощи СЖПРОБЕЛЫ удалите его.

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

  1. В ячейке С2 определите код проблемного знака, используя одну из следующих функций:
     • Ведущий пробел или непечатаемый символ в начале строки:
  • Конечный пробел или непечатаемый символ в конце строки:
  • Пробел или непечатаемый символ в середине строки, где n - позиция проблемного знака:

  В этом примере у нас есть неизвестный знак в середине текста, в 11-й позиции, и мы определим его код:

  Получаем значение 127 (см. скриншот ниже).

  1. В ячейке C3 вы заменяете СИМВОЛ(127) обычным пробелом (" "), а затем просто удаляете его:

  Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ПОДСТАВИТЬ друг в друга для одновременного удаления всех нежелательных знаков:

  Результат должен выглядеть примерно так:

  
  

  Эту универсальную формулу мы и использовали. Как видите, успешно.

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

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

  Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем (""):

  Или вы можете просто ввести его (" ") в формуле, например:

  
  

  Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.

  Как посчитать пробелы в Excel

  Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:

  • Вычислите всю длину строки с помощью функции ДЛСТР: ДЛСТР (A2)
  • Замените все пробелы ничем: ПОДСТАВИТЬ(A2; " "; "")
  • Вычислить длину строки без пробелов: ДЛСТР(ПОДСТАВИТЬ(A2; " "; ""))
  • Вычтите этот результат из первоначальной длины.

  Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:

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

  На рисунке показаны обе формулы в действии:

  
  

  Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние, используя функцию СЖПРОБЕЛЫ.

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

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

  Пользователи Excel, которые ценят свое время и ценят удобство, могут воспользоваться текстовыми инструментами, включенными в надстройку Ultimate Suite for Excel . Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.

  После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы» , «Удалить символы» , «Преобразовать текст» и многое другое.

  Всякий раз, когда вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:

  Готово! Все лишние пробелы удалены одним щелчком мыши:

  
  

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

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

  ознакомительную версию Ultimate Suite. Благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!

  4 способа быстро убрать перенос строки в ячейках Excel - В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013… Как убрать пробелы в числах в Excel - Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки. Когда вы вставляете данные из… Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим… 5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем… Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…

  Читайте также:

    
  • Действует ли режим заменить на содержимое таблицы ворд
    
  • Ворд скилс программные решения для бизнеса
    
  • Enterprise vpn services что это за программа на андроид
    
  • 1с com соединение создать таблицу значений
    
  • 1с изменение значения ячейки отчет