Excel пробел в формуле

Функция СЖПРОБЕЛЫ в формуле Excel с функцией ПОДСТАВИТЬ

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

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

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

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

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

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

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

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

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



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

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

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

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

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

Функция TRIM (СЖПРОБЕЛЫ) в Excel. Как использовать?

Функция СЖПРОБЕЛЫ (TRIM) в Excel используется для удаления лишних пробелов в тексте.

Что возвращает функция

Текст без лишних пробелов.

=TRIM(text) – английская версия

=СЖПРОБЕЛЫ(текст) – русская версия

Аргументы функции

• text (текст) – текст из которого нужно удалить лишние пробелы. Он может быть введен вручную, быть ссылкой на ячейку с текстом. Например, если нам нужно удалить лишние пробелы из текста в ячейке “А1”, то нам поможет формула: =TRIM(A1) или =СЖПРОБЕЛЫ(A1) .

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

Пример 1. Убираем лишние пробелы перед словами, после слов, двойные пробелы

На примере ниже текст наполненный массой лишних пробелов.

С помощью функции СЖПРОБЕЛЫ мы легко уберем все лишнее:

=TRIM(A1) – английская версия

=СЖПРОБЕЛЫ(A1) – русская версия

Скопируйте и протяните эту формулу напротив каждой строки, в которой вы хотите убрать лишние пробелы.

Пример 2. Корректно подсчитываем количество слов в тексте с учетом лишних пробелов

Пробелы это хороший индикатор при подсчете слов в тексте.

Например, если нам нужно понять, какое количество слов во фразе “Как посчитать количество слов в тексте?” то нам поможет формула:

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1 – английская версия

=ДЛСТР(A1)-ДЛСТР(СЦЕПИТЬ(A1;” “;””))+1 – русская версия

Суть формулы заключается в том, что она считает количество пробелов во фразе и вычитывает “1”, таким образом получая количество слов в тексте.

Если в тексте есть лишние пробелы, то формула будет считать слова не корректно.

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

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1 – английская версия

=ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(СЦЕПИТЬ(A1;” “;””))+1 – русская версия

Работа с функцией СЦЕПИТЬ в Microsoft Excel

Одной из интересных функций приложения Microsoft Excel является функция СЦЕПИТЬ. Её основной задачей является соединение содержимого двух или нескольких ячеек в одной. Данный оператор помогает решить некоторые задачи, которые с помощью других инструментов воплотить невозможно. Например, с его помощью удобно производить процедуру объединения ячеек без потерь. Рассмотрим возможности данной функции и нюансы её применения.

Применение оператора СЦЕПИТЬ

Функция СЦЕПИТЬ относится к группе текстовых операторов Excel. Её основная задача — объединение в одной ячейке содержимого нескольких ячеек, а также отдельных символов. Начиная с версии Excel 2016, вместо данного оператора используется функция СЦЕП. Но в целях сохранения обратной совместимости оператор СЦЕПИТЬ тоже оставлен, и его можно использовать наравне со СЦЕП.

Синтаксис этого оператора выглядит следующим образом:

В качестве аргументов могут выступать, как текст, так и ссылки на ячейки, которые его содержат. Количество аргументов может варьироваться от 1 до 255 включительно.

Способ 1: объединение данных в ячейках

Как известно, обычное объединение ячеек в Эксель приводит к потере данных. Сохраняются только данные расположенные в верхнем левом элементе. Для того, чтобы объединить содержимое двух и более ячеек в Эксель без потерь можно применить функцию СЦЕПИТЬ.

1. Выделяем ячейку, в которой планируем разместить объединенные данные. Кликаем на кнопку «Вставить функцию». Она имеет вид пиктограммы и размещена слева от строки формул.

Открывается Мастер функций. В категории «Текстовые» или «Полный алфавитный перечень» ищем оператор «СЦЕПИТЬ». Выделяем это наименование и жмем на кнопку «OK».

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

Устанавливаем курсор в первое поле окна. Затем выделяем ссылку на листе, в которой содержатся данные, нужные для объединения. После того, как координаты отобразились в окошке, аналогичным образом поступаем со вторым полем. Соответственно выделяем другую ячейку. Подобную операцию проделываем, пока координаты всех ячеек, которые нужно объединить, не будут внесены в окно аргументов функции. После этого жмем на кнопку «OK».

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

Способ 2: применение функции с пробелом

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

1. Выполняем задачу по тому же алгоритму, который описан выше.
2. Двойным щелчком левой кнопки мыши по ячейке с формулой активируем её для редактирования.

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

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

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

Способ 3: добавление пробела через окно аргументов

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

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

Выполняем те же действия, что и при первом способе применения функции СЦЕПИТЬ, вплоть до открытия окна аргументов оператора. Добавляем значение первой ячейки с данными в поле окна, как это уже было описано ранее. Затем устанавливаем курсор во второе поле, и выделяем ту пустую ячейку с пробелом, о которой шла речь ранее. Появляется ссылка в поле окна аргументов. Для ускорения процесса можно её скопировать, выделив и нажав сочетание клавиш Ctrl+C.

Затем добавляем ссылку на следующий элемент, который нужно добавить. В очередном поле опять добавляем ссылку на пустую ячейку. Так как мы скопировали её адрес, то можно установить курсор в поле и нажать сочетание клавиш Ctrl+V. Координаты будут вставлены. Таким способом чередуем поля с адресами элементов и пустой ячейки. После того, как все данные внесены, жмем на кнопку «OK».

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

Внимание! Как видим, вышеуказанный способ значительно ускоряет процедуру корректного объединения данных в ячейках. Но следует учесть, что данный вариант таит в себе и «подводные камни». Очень важно, чтобы в элементе, который содержит пробел, со временем не появились какие-то данные или он не был сдвинут.

Способ 4: объединение колонок

С помощью функции СЦЕПИТЬ можно быстро объединять данные нескольких колонок в одну.

С ячейками первой строки объединяемых колонок проделываем на выбор те действия, которые указаны во втором и третьем способе применения аргумента. Правда, если вы решили воспользоваться способом с пустой ячейкой, то ссылку на неё нужно будет сделать абсолютной. Для этого, перед каждым знаком координат по горизонтали и вертикали этой ячейки ставим знак доллара ($). Естественно, что лучше всего это сделать в самом начале, чтобы в другие поля, где содержится этот адрес, пользователь мог копировать его, как содержащий постоянные абсолютные ссылки. В остальных полях оставляем относительные ссылки. Как всегда, после выполнения процедуры, жмем на кнопку «OK».

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

После выполнения этой процедуры данные в указанных столбцах будут объединены в одном столбце.

Способ 5: добавление дополнительных символов

Функцию СЦЕПИТЬ можно также использовать для добавления дополнительных символов и выражений, которых не было в первоначальном объединяемом диапазоне. Более того, можно с помощью данной функции внедрять и другие операторы.

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

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

Оператор СЦЕПИТЬ – единственная возможность объединения ячеек без потерь в Excel. Кроме того, с его помощью можно соединять целые столбцы, добавлять текстовые значения, проводить некоторые другие манипуляции. Знание алгоритма работы с этой функцией позволит облегчить решение многих вопросов для пользователя программы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Excel: разделяем слипшийся текст

Случается, что содержание одного столбца нужно разбить на несколько. Например:

• слишком обширное описание товара (а нужно, чтоб наименование, характеристики и актуальное наличие были разбиты);
• полный адрес в одном столбце (а нужно, чтоб индекс, область, район и прочие данные были разбиты);
• ФИО в одном столбце (а нужно – в отдельных).

Какая бы ситуация ни сложилась у вас, есть несколько способов разделить слипшийся текст.

Задача №1: Разбить текст по столбцам

Решение: Выделите нужные ячейки, перейдите в меню Данные → Текст по столбцам.

В появившемся окне Мастер текстов (разбора) укажите формат данных:

• с разделителями – в случае, когда содержимое будущих отдельных столбцов разделяется каким-либо символом (укажите, каким именно).

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

• фиксированной ширины – если вы хотите задать произвольную ширину столбца:

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

• Общий – самый универсальный, сохранит данные в исходном виде.
• Дата – для столбцов, где указан день-месяц-год (порядок значений вы также выберете самостоятельно).
• Текстовый – незаменим для ячеек с числовыми данными, которые Excel должен воспринимать как текст. Пример: если столбцу с номерами банковских счетов не назначить текстовый формат, цифры округлятся до 15-ти символов – офис распознает их как числа.

Задача №2: Выборочно выдернуть слова из текста

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

Function Substring(Txt, Delimiter, n) As String
Dim x As Variant
x = Split(Txt, Delimiter)
If n > 0 And n — 1

Далее идем в меню Формулы → Вставить функцию , выбираем категорию «Определенные пользователем» , применяем синтаксис =SUBSTRING и указываем значения:

• Txt – адрес ячейки с нужным текстом.
• Delimeter – разделяющий знак (запятая, тире, пробел и т.д.).
• N – порядковый номер фрагмента, который нужно «выдернуть».

Задача №3: Разделить текст без пробелов

Если о знаках-разделителях речи даже не идет – то есть текст абсолютно слипшийся (например, ФИО «ПетровскийАндрейИванович»), используйте следующую макрофункцию:

Function CutWords(Txt As Range) As String
Dim Out$
If Len(Txt) = 0 Then Exit Function
Out = Mid(Txt, 1, 1)

For i = 2 To Len(Txt)
If Mid(Txt, i, 1) Like «[a-zа-я]» And Mid(Txt, i + 1, 1) Like «[A-ZА-Я]» Then
Out = Out & Mid(Txt, i, 1) & » »
Else
Out = Out & Mid(Txt, i, 1)
End If
Next i
CutWords = Out
End Function

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

Читайте нас дальше, чтобы работа с Excel давалась ещё легче.

Функция СЦЕПИТЬ (CONCATENATE)

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

Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.

Однако эти самые пробелы являются первым неприятным моментом при использовании функции СЦЕПИТЬ, т.к. функция склеивает ячейки без «шва» и пробелы приходится добавлять вручную. Если нужно соединить только несколько слов, то беда не большая, но когда нужно склеить хотя бы с десяток ячеек, то ручная добавка пробелов слегка утомляет. Чтобы немного облегчить процесс, можно посоветовать вставить пробел в отдельную ячейку и затем просто делать на нее ссылку.

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

Кстати, не все знают, что в Excel для склеивания ячеек также предусмотрен специальный оператор – & (амперсанд). Его нужно просто поставить между связываемыми ячейками или символами.

Вставлять амперсанд не удобно – нужно вначале переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется воспользоваться специальной комбинацией – удерживая клавишу Alt нажать 3 и 8 на цифровой клавиатуре. Здорово помогает сохранить душевное равновесие.

Формула СЦЕПИТЬ и амперсанд исправно работают до тех пор, пока не приходится соединять большое количество ячеек. Замучаешься вставлять «;» либо «&». Более того, между ячейками, как привило, добавляется пробел. К сожалению, указать в качестве аргумента целый диапазон нельзя (уже можно). Выход из этой ситуации следующий.

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

Затем нажимаем F9, для того, чтобы формула выдала результат вычисления, в нашем случае это будет массив.

Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.

Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).

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

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

Рассмотрим только вертикальное расположение данных (горизонтальное будет работать аналогично). Суть в том, чтобы рядом с данными создать столбец с формулой СЦЕПИТЬ, которая последовательно будет присоединять по одному слову по мере продвижения вниз. В нашем случае первая формула будет содержать только слово «Александр » (и пробел), следующая ниже добавляет «Сергеевич » (и пробел) и т.д.

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

Соединение текста и даты

И еще один трюк с формулой СЦЕПИТЬ. Довольно часто приходится соединять текст с датой. Дата, как известно, это простое порядковое число (отсчет начинается с 1 января 1900 года), которому придается нужный формат даты. Если же соединить напрямую текст и дату, то на выходе получится указанный текст и простое число. Возьмем, например, слово «Сегодня » (не забываем добавить пробел в конце) и функцию СЕГОДНЯ (всегда возвращающую текущую дату).

Как видно, ничего не получилось.

Для правильной склейки нужно «обернуть» дату в формулу ТЕКСТ, которая придает указанный формат числу.

Формат ДД.ММ.ГГ указывает, в каком виде должна отражаться дата. Если, например, указать формат ДД.ММ.ГГГГ, то дата будет выглядеть так: 03.09.2015 (год состоит из 4-х цифр).

Кстати, добавлять текст к числу или дате можно не только с помощью функции СЦЕПИТЬ. Можно использовать пользовательский числовой формат. У меня когда-то в рабочем файле красовалась автоматически обновляемая надпись.

Поэтому я всегда был прекрасно осведомлен о текущей дате и дне недели. При этом содержимое ячейки состояло только из одной функции СЕГОДНЯ.

Реализовать подобную идею довольно просто. Но это уже чистое форматирование. Расскажу при случае подробней.

P.S. Все проблемы выше решены в новых функциях Excel СЦЕП и ОБЪЕДИНИТЬ .