Как убрать пробелы в цифрах в гугл таблице excel
Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки.
Когда вы вставляете данные из внешнего источника в электронную таблицу Excel (текстовые отчеты, числа с веб-страниц и т.д.), то с большой вероятностью получите лишние пробелы вместе с важными данными. Могут появиться начальные и конечные пробелы, несколько интервалов между словами или же разделители разрядов для чисел.
Как следствие, ваша таблица выглядит неаккуратно и становится неудобна в использовании. Найти клиента в столбце «Имя» может быть непросто, так как вы ищете «Иван Петров», в котором нет лишних интервалов между именами, а в таблице он выглядит как « Иван Петров ». Или же числа не суммируются. И здесь снова скорее всего виноваты лишние пробелы.
Ранее мы уже подробно рассмотрели, как удалить лишние пробелы и прочие ненужные символы из текста (ссылки смотрите в конце). В этой статье вы узнаете, как очистить свои цифровые данные.
Способ 1. Используем инструмент «Найти и заменить».
Рассмотрим пример, когда мы импортировали цифровые данные из отчета, подготовленного другой программой. Там в числах были разделители тысяч, чтобы их было удобнее читать. Естественно, эти разделители попали в нашу таблицу, а числа оказались импортированы как текст. В результате никакие математические операции с ними оказались невозможны.
Наша задача – преобразовать цифры в числа, убрав из них всё ненужное. Разберём пошагово.
Вот теперь вы совершенно точно не только удалили пробелы из чисел, но и превратили ваши цифры в полноценные числа, пригодные для математических вычислений. К сожалению, если будет импортирована новая партия данных, то все операции придется повторять снова.
2. Формула ПОДСТАВИТЬ.
Рассмотрим те же исходные данные, что и в предыдущем примере. Но в этот раз попробуем очистить их при помощи формул.
Используем функцию ПОДСТАВИТЬ, чтобы удалить все пробелы перед, после, а также между цифрами.
В результате мы имеем цифры, записанные в виде текста. Чтобы иметь возможность производить с ними различные математические вычисления, нужно преобразовать их в числа.
Для этого существует 2 способа.
Если мы попытаемся произвести с текстом какое-то действие как с числом (например, умножение или вычитание), то Excel в первую очередь попытается конвертировать этот текст в число, чтобы выполнить наше задание. Этим и воспользуемся. Если дважды умножить число на минус 1, то его значение не изменится. То же самое можно сделать, дважды поставив знак минус перед формулой. Назовем это «двойное отрицание».
В результате мы имеем настоящие числа. Ну а если нужны пробелы-разделители между разрядами, используйте для этого соответствующий числовой формат.
3. Функция СЖПРОБЕЛЫ.
Если у нас нет разделителей разрядов между цифрами, но есть начальные пробелы перед цифрами, то можно использовать функцию СЖПРОБЕЛЫ.
А затем, используя тот же метод, что был описан выше, мы превращаем цифры в числа.
Вот как это выглядит на скриншоте:
Примечание . Если после использования формулы вы все еще видите текст, а не число, значит, в ячейке есть какие-то непечатаемые символы. Вы их можете не видеть на экране.
В этом случае рекомендуем немного усложнить выражение:
Если же имеются неразрывные пробелы между цифрами, то вы их также не сможете убрать способами, описанными выше. Вот что можно предложить:
Более подробные пояснения по этим формулам смотрите в этом руководстве.
4. Форматирование ячеек.
Как видите, способ несложный. Но работает он только с числами. Если цифры записаны как текст, то никакого эффекта эти манипуляции не принесут.
Как удалить пробелы и пустые строки в Excel с помощью Regex - Хотите обрабатывать пробелы наиболее эффективным образом? Используйте регулярные выражения, чтобы удалить все пробелы в ячейке, заменить несколько пробелов одним символом, обрезать пробелы только между числами и т. д. Какие бы исходные…
4 способа быстро убрать перенос строки в ячейках Excel - В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013…
Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
При любой работе с текстовыми данными или числами в Google Таблицах рано или поздно вам придется иметь дело с теми надоедливыми лишними пробелами, которые попадают в ячейки.
Это могут быть начальные пробелы (которые встречаются перед содержимым ячейки), конечные пробелы (которые встречаются в конце содержимого ячейки) или символы двойного пробела, которые встречаются между текстовыми строками или числами.
И, как вы уже могли подумать, все это нежелательно.
В этом уроке я покажу вам несколько действительно простых и изящных способов удаления лишних пробелов в Google Таблицах .
Удаление лишних пробелов с помощью опции TRIM Whitespace
Google Таблицы продолжают добавлять новые функции каждые несколько месяцев, и одна из таких функций — удаление пробелов в ячейках.
Теперь есть встроенная функция, которая позволяет быстро удалять пробелы (например, начальные, конечные или двойные пробелы) всего двумя щелчками мыши.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро удалить все лишние пробелы.
Ниже приведены шаги для этого:
Вышеупомянутые шаги удалят все начальные, конечные и двойные пробелы и покажут вам подсказку, сообщающую, сколько пробелов было удалено.
Это, безусловно, самый простой способ удалить лишние пробелы в Google Таблицах .
Удаление лишних пробелов с помощью функции TRIM
В большинстве случаев использование функции TRIM позаботится обо всех начальных конечных и двойных пробелах в вашем наборе данных.
Она построена по той же причине.
Предположим, у вас есть набор данных имен, как показано ниже, где в именах есть ведущие, конечные и двойные пробелы.
Ниже приведена формула, которая удалит все эти лишние пробелы в Google Таблицах:
Скопируйте формулу во все ячейки столбца, чтобы получить результат для всех имен.
Почему функция обрезки может легко удалить все лишние пробелы в Google Таблицах, она не сможет удалить любой разрыв строки (что происходит, когда вы используете ALT + ENTER, чтобы получить новую строку в той же ячейке).
Если вы хотите удалить лишние пробелы, а также разрыв строки, вы можете использовать приведенную ниже комбинацию функции ОБРЕЗАТЬ (TRIM) и функции ОЧИСТИТЬ (CLEAN):
Преимущество использования функции TRIM (ОБРЕЗАТЬ) состоит в том, что вы можете комбинировать ее с формулами.
Например, если вы используете формулы поиска, в которых значение поиска или диапазон поиска могут содержать лишние пробелы, вы можете заключить их в функцию TRIM, чтобы удалить их и сделать данные согласованными.
Найти и заменить космические символы
Иногда вы можете не захотеть использовать формулу, потому что для этого потребуется дополнительный столбец, в котором вы будете получать результат.
Хотя использование функции TRIM — лучший способ удалить лишние пробелы в Google Таблицах, если вы не хотите использовать формулу, вы можете использовать технику поиска и замены для поиска и замены двойных пробелов.
Большим ограничением этого метода является то, что вы можете найти только двойные пробелы, и он не сможет удалить любые начальные или конечные пробелы в ячейке. С другой стороны, если вы хотите удалить все виды пробелов, будь то одинарные или двойные, вы можете сделать это с помощью функции «Найти и заменить».
Предположим, у вас есть набор данных, показанный ниже, и вы хотите удалить все двойные пробелы между именами и заменить их одним пробелом.
Ниже приведены шаги для этого:
Вышеупомянутые шаги удаляют все двойные пробелы и заменяют их одиночными пробелами.
Недостатком этого метода является то, что он не сможет удалить начальные или конечные пробелы (которые могут быть двойными или одинарными).
Итак, это три разных способа удаления лишних пробелов в Google Таблицах (начальные, конечные и двойные пробелы).
Самый простой способ — использовать опцию TRIM whitespace, также вы можете использовать формулу TRIM или найти и заменить.
Вы могли заметить, что в Google Таблицах обычно округляются десятичные числа, в которых после запятой слишком много цифр. Это желательно в большинстве случаев, так как это гарантирует, что число не перейдет за границу столбца в соседнюю ячейку, поэтому ваш рабочий лист не будет выглядеть неопрятным.
Однако это может стать проблемой, если вы имеете дело с научными данными, где точность до наименее значащей цифры может иметь решающее значение. В некоторых случаях вы можете обнаружить, что ваше число отображается в экспоненциальном формате (например, 1.23E + 03).
Если вам интересно, как заставить Google Таблицы прекратить округление, продолжайте читать! В этом уроке мы рассмотрим некоторые причины округления десятичных чисел и покажем вам несколько простых способов предотвратить округление в Google Таблицах.
Почему ваши десятичные числа округляются?
Прежде чем решать проблему, давайте разберемся, почему она возникает. Может быть несколько причин округления ваших чисел в Google Таблицах.
Вот несколько возможных:
- Ваши ячейки могут быть отформатированы для отображения меньшего количества цифр.
- Ваши ячейки могут быть в научном формате.
Давайте посмотрим, как решать каждую из вышеперечисленных проблем по очереди.
В вашем числе может быть более 11 цифр (включая десятичную точку)
Google Таблицы по умолчанию позволяют отображать в ячейке только 11 цифр (всего). Все цифры после этого округляются.
Например, в приведенном ниже примере в Google Таблицах отображается вся ячейка, если число состоит из 11 цифр. Однако, если ваш номер состоит из 12 или 13 цифр (как показано в ячейках A3 и A4), Таблицы Гугл округляют числа до 11-й цифры.
Это связано с тем, что ячейки в Google Таблицах предварительно отформатированы для отображения не более 11 цифр. Здесь важно понимать, что хотя в ячейках отображается всего 11 цифр, они фактически не удаляют 12-ю и 13-ю цифры.
Другими словами, основной номер остается неизменным и целым. Поэтому, когда вы используете ссылку на ячейку в вычислении, используется весь исходный номер (с цифрами после 11-го), а не только цифры, которые вы видите в ячейке.
Примечание . Чтобы посмотреть исходное число, просто щелкните ячейку, и вы увидите фактическое содержимое ячейки в строке формул (как показано на скриншоте выше).
Ваши ячейки могут быть отформатированы для отображения меньшего количества цифр
Кроме того, стандартное форматирование некоторых ячеек может округлять десятичное число до одного-двух десятичных знаков.
Если ваша ячейка была отформатирована в одном из следующих форматов, вы, скорее всего, увидите, что ваши числа округляются до 2 (а иногда и более) цифр:
- Числовой формат
- Формат бухгалтерского учета
- Финансовый формат
- Валютный формат
Ячейки, для которых задан один из указанных выше форматов, автоматически округляют значения до двух десятичных знаков. Даже если они были отформатированы для отображения более 2 (но менее 11) десятичных знаков, вы обнаружите, что десятичные дроби в конце округляются.
Если ваша ячейка была отформатирована в формате валюты (с округлением), вы обнаружите, что десятичные дроби полностью округляются до целого числа!
И если вы обнаружите, что ваши числа отображаются в экспоненциальной форме, то ваши ячейки, скорее всего, имеют научный формат.
На изображении выше показано одно и то же число 12.0123456789, отображаемое в разных форматах.
Независимо от причины, по которой десятичные числа отображаются как округленные, проблему можно решить двумя простыми способами.
Два простых способа предотвратить округление десятичных чисел в Google Таблицах
Есть два способа заставить Google Таблицы перестать округлять последние несколько цифр и отображать все число:
- Форматирование ячейки для отображения большего количества десятичных знаков
- Использование функции TRUNC
Форматирование ячейки для отображения большего количества десятичных знаков вместо округления
Этот метод напрямую влияет на ячейку и форматирует ее для отображения большего количества десятичных знаков. Для этого нужно просто выбрать ячейки, которые вы хотите отформатировать, и нажать кнопку «Увеличить десятичные разряды». Вы найдете эту кнопку на панели инструментов, и она выглядит так:
Каждый раз, когда вы нажимаете эту кнопку, в выбранной ячейке будет отображаться еще один десятичный разряд основного числа. Таким образом, нажатие кнопки один раз отобразит все число, хранящееся в ячейке A3, а двойное нажатие отобразит все число, хранящееся в ячейке A4, как показано ниже. Таким образом, вы можете отобразить свой номер целиком без округления.
Примечание. В Google Таблицах нельзя использовать десятичные числа, содержащие более 16 цифр (включая десятичную точку). Любая цифра после этого автоматически преобразуется в 0, даже если вы нажмете кнопку «Увеличить десятичные разряды». Это касается не только отображаемого числа, но и исходного исходного числа. Это основное ограничение дизайна Google Таблиц, и вы мало что можете с этим поделать. Однако это не относится к целым числам.
Использование функции TRUNC для предотвращения округления таблиц Google
Этот метод полезен, если вы хотите отобразить больше десятичных знаков в отдельной ячейке, не затрагивая исходную ячейку. Функция TRUNC используется для уменьшения или увеличения точности заданного значения. Слово TRUNC является сокращением от «Truncate».
Синтаксис этой функции следующий:
- value — это число, с которым вы хотите работать. Это может быть числовое значение или ссылка на ячейку, содержащую число.
- Параметр places не обязателен. Он определяет количество десятичных знаков, которое мы хотим сохранить. По умолчанию значение этого параметра равно 0. Поэтому, если этот параметр не указан, функция возвращает только целую часть значения.
Функция TRUNC в основном обрезает данное число до определенного количества значащих цифр, опуская менее значащие цифры, но без округления. Итак, если мы хотим отобразить номера ячеек от A2 до A4 (на следующем снимке экрана) без округления, мы можем использовать функцию TRUNC следующим образом:
Все ячейки A2: A4 (на скриншоте выше) содержат одно и то же базовое значение: 12.056789. Поскольку они были отформатированы как число, ячейки A2: A4 содержат только два десятичных знака и округляют вторую цифру до 6.
Примечание. Если вы решили отображать меньшее количество цифр без округления, все, что вам нужно сделать, это соответствующим образом изменить второй параметр функции TRUNC. Поэтому, если вы хотите отобразить только 3 значащих цифры ячейки A1, вы можете использовать TRUNC (A1, 3).
Если вы видите, что ваше число отображается в форме экспоненты, вы можете изменить его на числовой формат, выбрав ячейку и перейдя к Формат-> Число в главном меню.
Примечание . Если вы хотите отобразить более 11 цифр, вам нужно будет нажать кнопку «Увеличить десятичные разряды» даже после использования функции TRUNC.
Заключение
В этом руководстве мы обсудили, почему Google Таблицы округляют десятичные числа с большим количеством десятичных знаков, а также как предотвратить их округление в Google Таблицах.
Мы рекомендуем вам поэкспериментировать с этими методами и с разными десятичными знаками, чтобы лучше понять, как Google Таблицы работают с десятичными числами. Теперь вы точно знаете, как заставить Google Таблицы перестать округлять ваши числа!
Иногда вам может потребоваться удалить первый символ из строки и получить остальную часть строки в Google Таблицах. Хотя вы можете сделать это вручную для небольшого набора данных, когда у вас есть десятки или сотни таких ячеек, использование встроенных формул — правильный способ сделать это.
В этом коротком руководстве по Google Таблицам я покажу вам, как удалить первые символы из строки в Google Таблицах с помощью текстовых формул.
Как удалить первый символ из строки в Google Таблицах
Предположим, у вас есть набор данных, показанный ниже, и вы хотите избавиться от первого символа и получить только числовую часть:
Использование ПРАВИЛЬНОЙ формулы
Ниже приведена формула, которая удалит первый символ и предоставит вам оставшуюся часть строки:
= ПРАВО (A2; LEN (A2) -1)
Приведенная выше формула сначала проверяет длину строки (с помощью функции LEN), а затем вычитает из нее 1. Это дает нам количество символов, которое мы хотим получить из правой части строки.
Это значение затем используется в функции RIGHT для извлечения всех символов в строке, кроме первого.
Использование формулы MID
Другой способ удалить первый символ и получить все остальное — использовать формулу MID. Он использует ту же логику, что и функция RIGHT, с небольшим отличием в синтаксисе.
Приведенная ниже формула даст нам все, кроме первого символа в строке:
= MID (A2,2; LEN (A2) -1)
Формула MID запрашивает начальную позицию (с которой следует начинать извлечение символов) и количество извлекаемых символов.
Вы можете еще больше сократить эту формулу и использовать приведенную ниже:
В приведенной выше формуле я даю функции MID начальную позицию (которая будет равна 2, поскольку мне не нужен первый символ) и длину извлекаемых символов.
Вместо использования функции LEN для подсчета символов я использовал очень большое число для извлечения всех символов. Если количество символов меньше (как в нашем примере), он просто извлекает все.
Использование формулы REPLACE
Есть несколько способов сделать одно и то же с разными формулами в Google Таблицах.
Еще один быстрый способ сделать это — заменить первый символ пробелом, и вы можете сделать это с помощью функции REPLACE.
Ниже приведена формула, которая удалит первый символ и предоставит вам остальные:
Приведенная выше формула начинается с первой позиции и заменяет первый символ пробелом.
Примечание. Во всех трех рассмотренных здесь примерах результат выражается формулой. Если вам не нужна формула после получения результата, преобразуйте ее в значения. Таким образом, вы даже можете избавиться от исходных данных, если они вам не нужны.
Хотя я показал вам, как удалить первый символ в приведенных выше примерах, вы можете легко изменить формулы, чтобы удалить любое количество символов с самого начала. Просто настройте формулу, указав количество символов вместо 1.
В чем самая большая проблема с пробелами? Часто они невидимы для человеческого глаза. Внимательный пользователь может иногда заметить пустой интервал, скрывающийся перед текстом, или несколько ненужных пустот между словами. Но нет никакого способа визуально обнаружить конечные пробелы, те, которые находятся вне поля зрения в конце ячеек.
Не было бы большой проблемы, если бы эти лишние интервалы просто существовали, но они могут повлиять на результаты ваших формул. Дело в том, что две ячейки, содержащие один и тот же текст с пробелами и без них, даже если это всего лишь один знак, считаются разными значениями. В результате вы можете ломать голову, пытаясь понять, почему очевидно правильная формула не может соответствовать двум, казалось бы, идентичным записям.
Теперь, когда вы полностью осознаете проблему, пришло время найти решение. Есть несколько способов удалить пробелы из строки, и это руководство поможет вам выбрать метод, наиболее подходящий для вашей конкретной задачи и типа данных, с которым вы работаете.
Как убрать пробелы в Excel при помощи «Найти и заменить»
Это более быстрый метод, который может быть полезен в следующих ситуациях:
Обратите внимание, что этот метод не рекомендуется использовать для удаления начальных или конечных пробелов, так как хотя бы один их них там все равно останется.
Итак, найдем и заменим двойные интервалы независимо от их расположения.
Вот как это можно сделать:
- Выделите ячейки, из которых вы хотите их удалить.
- Перейдите на главное меню -> Найти и выбрать -> Заменить.(Также можно использовать сочетание клавиш - CTRL + H ).
- В диалоговом окне «Найти и заменить» введите:
- Найти: Двойной пробел.
- Заменить на: Одинарный.
Нажмите «Заменить все».
Обратите внимание, что если у вас есть три интервала между двумя словами, то теперь вы получите два (один будет удален). В таких случаях вы можете повторить эту операцию снова, чтобы удалить любые двойные пробелы, которые ещё могли остаться.
Недостатки этого метода вы видите сами: перед некоторыми из слов остался начальный пробел. Аналогично остались и конечные пробелы после текста, просто они не видны явно. Поэтому для текстовых выражений я не рекомендовал бы применение этого способа.
- Чтобы удалить все пробелы в тексте, выполните следующие действия:
- Выделите нужные ячейки.
- Перейдите в меню Главная -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш - 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, вы пишете два выражения:
- В ячейке С2 определите код проблемного знака, используя одну из следующих функций:
- Ведущий пробел или непечатаемый символ в начале строки:
- Конечный пробел или непечатаемый символ в конце строки:
- Пробел или непечатаемый символ в середине строки, где n - позиция проблемного знака:
В этом примере у нас есть неизвестный знак в середине текста, в 11-й позиции, и мы определим его код:
Получаем значение 127 (см. скриншот ниже).
- В ячейке C3 вы заменяете СИМВОЛ(127) обычным пробелом (" "), а затем просто удаляете его:
Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ПОДСТАВИТЬ друг в друга для одновременного удаления всех нежелательных знаков:
Результат должен выглядеть примерно так:
Эту универсальную формулу мы и использовали. Как видите, успешно.
Как удалить все пробелы
В некоторых ситуациях может потребоваться удалить абсолютно все пробелы в ячейке, включая те, что находятся между словами или числами. Например, если вы импортировали в вашу таблицу числовой столбец, в котором пробелы используются в качестве разделителей тысяч. Конечно, разделители разрядов упрощают чтение больших чисел, но они же препятствуют вычислению ваших формул. Такие разделители нужно создавать при помощи форматирования, а не вручную.
Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем (""):
Или вы можете просто ввести его (" ") в формуле, например:
Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.
Как посчитать пробелы в Excel
Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:
- Вычислите всю длину строки с помощью функции ДЛСТР: ДЛСТР (A2)
- Замените все пробелы ничем: ПОДСТАВИТЬ(A2; " "; "")
- Вычислить длину строки без пробелов: ДЛСТР(ПОДСТАВИТЬ(A2; " "; ""))
- Вычтите этот результат из первоначальной длины.
Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:
Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них, а затем вычтите ее из первоначальной длины:
На рисунке показаны обе формулы в действии:
Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние, используя функцию СЖПРОБЕЛЫ.
Простой способ удаления пробелов без формул.
Как вы уже знаете, лишние пробелы и другие нежелательные символы могут незаметно скрываться на ваших листах, особенно если вы импортируете данные из внешних источников. Вы также знаете, как удалять пробелы в Excel с помощью формул. Конечно, изучение нескольких формул – хорошее упражнение для оттачивания ваших навыков, но это может занять много времени.
Пользователи Excel, которые ценят свое время и ценят удобство, могут воспользоваться текстовыми инструментами, включенными в надстройку Ultimate Suite for Excel . Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.
После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы» , «Удалить символы» , «Преобразовать текст» и многое другое.
Всякий раз, когда вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:
Готово! Все лишние пробелы удалены одним щелчком мыши:
Здесь мы вместе с лишними пробелами удалили еще и переводы строки, чтобы значения располагались в привычном виде, в одну строку.
Вот как можно быстро удалить пробелы в ячейках Excel. Если вам интересно изучить другие возможности работы с текстовыми значениями (и не только), вы можете загрузить
ознакомительную версию Ultimate Suite. Благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Как удалить пробелы и пустые строки в Excel с помощью Regex - Хотите обрабатывать пробелы наиболее эффективным образом? Используйте регулярные выражения, чтобы удалить все пробелы в ячейке, заменить несколько пробелов одним символом, обрезать пробелы только между числами и т. д. Какие бы исходные…
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 с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
Читайте также: