Tw-city.info

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Удаление пробелов в excel

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

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

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

Просто и красиво. Но есть пара нюансов.

Неразрывные пробелы не удаляются

Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа «г. Москва» или «Иванов И.И.», чтобы эти фразы не разбивались между строчками. В Microsoft Word такой спецсимвол вводится сочетанием клавиш Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:

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

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

Если необходимо удалить только начальные пробелы (т.е. не трогать концевые и пробелы между словами), то придется делать это хитрыми формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к сожалению, нет.

Формула для удаления лишних пробелов только слева будет выглядеть так:

В английской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))

Формула для удаления лишних пробелов только справа будет чуть посложнее и должна вводиться уже как формула массива (с Ctrl+Shift+Enter):

В английском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99)))

Ссылки по теме

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

Пробел в начале строки: =ЕСЛИ(ЛЕВСИМВ(А1;1)=» «;ПРАВСИМВ(А1;ДЛСТР(А1)-1))
Пробел в конце строки: =ЕСЛИ(ПРАВСИМВ(А1;1)=» «;ЛЕВСИМВ(А1;ДЛСТР(А1)-1))

А еще так можно .
Для удаления пробелов в начале и в конце строки
можно использовать функцию =СЦЕПИТЬ(). Допустим, ячейка A1 проблемная ,у неё пробел вначале и в конце, нам это не надо.
Вставляем новый столбец и обрабатываем формулой =СЖПРОБЕЛЫ(A1), далее ВыделитьВставить значения, теперь повторяющихся пробелов не будет, но в начале и в конце они остались.
Вставим еще один столбец, а в столбец формулу =СЦЕПИТЬ(«&&»;A1;»&&») , тут главное чтобы && не встречался в тексте. Опять ВыделитьВставить значения . Теперь у нас в начале и конце строки не пробел, а дважды амперсанд+ пробел.
Далее Ctrl+F ищем &&[пробел] — заменить ничем, опять ищем [пробел]&& — заменить ничем, там где были пробелы они будут удалены вместе с амперсандами, там где пробелов не было остались наши &&
Проводим последний Поиск -замену Ctrl+F Найти && Заменить ничем.

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

Удаление пробелов в Excel

1, там такая колонка:

есть одно пространство перед содержанием. например: есть пробел перед #01 . Когда я выбрал столбец и использовал левое оправдание, но содержимое не идет на пробел слева. Как удалить пространство с помощью пакета?

Читать еще:  Num2text в excel скачать

2, есть такая колонка.

Я хочу сделать всю линию до 160 с партией. как мне это сделать?

2 Ответа

В Excel используйте следующую формулу =RIGHT(nameofcell, LEN(nameofcell)-1) и нажмите кнопку перетащить первую ячейку формулы, чтобы создать столбец, который будет таким же, как и связанные ячейки, но без начального пробела.

Формула =TRIM(nameofcell) также удаляет ведущее и trailing пространства данной ячейки.

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

Либо =160 , либо =nameofcellthatis160 с помощью щелчка и перетаскивания будет работать метод для вашего 2-го вопроса. Обратите внимание, что при втором варианте он может настроить формулу на имя ячейки выше непосредственно над ней от щелчка и перетаскивания, но если они все 160, то это не будет иметь значения.

NOTE: A1-это имя ячейки.

Использование TRIM

Для удаления лишних пробелов , которые существуют между двумя словами (на start/ конца)

Использование SUBSTITUTE

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

Вот демо-версия

Похожие вопросы:

Возможный Дубликат : Как удалить лишнее пустое пространство в NSString? я пытаюсь выяснить, как удалить все пробелы из строки, но оставить только один. То есть если у меня есть строка типа this is.

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

Удаление прописных слов в Excel VBA У меня есть Excel лист, как этот: A B 1 Used CONTENT VERSION SYSTEM for the FALCON Project 2 USA beats UK at Soccer Cup 2008 3 DARPA NET’s biggest contribution.

Программа, экспортирующая в Excel, создает файл со списком отступов в одном столбце, как показано ниже: Column A First Text Second Text Third Text Fourth Text Fifth Text Как я могу создать функцию в.

У меня есть скрипт, который циклически выполняет несколько операций поиска / замены regex в python, одна из этих операций-удаление пробелов trailing Я уже пробовал: re.sub(rs+$, », str) re.sub(r.

У меня есть опция trim_trailing_white_space_on_save включена. И для некоторых файлов я должен предотвратить удаление пробелов trailing, потому что они важны. Как удалить это поведение для некоторых.

Каков наилучший способ удаления пробелов на сайте на основе html, JavaScript и php. Есть ли какой-то способ настроить Apache для автоматического удаления пробелов из всех html, JavaScript.

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

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

Первые 10 способов очистки данных

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

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

Читать еще:  Как посчитать сумму строк в excel

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

Для очистки данных нужно выполнить следующие основные действия:

Импортируйте данные из внешнего источника.

Создайте резервную копию исходных данных в отдельной книге.

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

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

Затем выполните задачи, требующие операций со столбцами. Для работы со столбцами нужно выполнить следующие действия:

Вставьте новый столбец (B) рядом с исходным (A), который требуется очистить.

Добавьте формулу, которая будет преобразовывать данные, вверху нового столбца (B).

Заполните вниз формулу в новом столбце (B). В таблице Excel будет автоматически создан вычисляемый столбец с заполненными вниз значениями.

Выберите новый столбец (B), скопируйте его, а затем вставьте как значения в новый столбец (B).

Удалите исходный столбец (A). При этом новый столбец B станет столбцом A.

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

Microsoft Excel

трюки • приёмы • решения

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

Многие ошибки возникают из-за того, что вы даже не можете увидеть: из-за пробела. Рассмотрим пример, показанный на рис. 198.1. Ячейка В2 содержит формулу, которая ищет название цвета в ячейке В1 и возвращает соответствующий код из таблицы: =ВПР(B1;D2:E9;2;ЛОЖЬ) .

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

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

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

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

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

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

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

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

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

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

Довольно частой проблемой при работе с расчетами в Excel является неверное занесение данных в ячейки. Чаще всего в числовое значение добавляется ненужный символ и программа не воспринимает ячейку числовой, а соответственно не считает формулу . Уверен, вы встречались с такой проблемой при выгрузке данных из учетной системы или при копировании данных с сайта. Часто таблицы с ошибками могут присылать друзья/коллеги. Самым распространенным символом, мешающим правильной работе Excel, является пробел. Как убрать пробелы в Excel в числовых и текстовых данных, расскажу в этой статье. Почти каждый пользователь Excel хотя бы раз сталкивался с неправильным заполнением ячеек. Знакома ли вам такая запись данных:

Думаю знакома. Как убрать любой символ (в случае на картинке) можно прочитать в отдельной статье . А вот чтобы пробел мешал расчетам, должно произойти что-то необычное, однако, это случается сплошь и рядом. После очередного случая, повлекшего серьезные последствия, решил написать отдельную статью, с несколькими способами как убрать пробел в Excel. На статью можно будет ссылаться при возникновении проблем.

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

Пробел в числе Excel, как это выглядит?

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

Здесь это не числа, а текст и формула для обоих случаев считаться не будет. Пример

В ячейке B2 внесено 2 или более пробела.

В B3 не пробел, а другой символ похожий на него.

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

Как тогда отследить, где правильно занесено, а где нет? Читаем ниже.

Так пробел или формат числа?

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

Но я пользуюсь кнопкой на ленте задач Excel

Числовое значение при этом приобретет выравнивание и 2 знака после запятой. Текст останется без изменений.

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

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

Самый простой способ воспользоваться инструментом Найти и заменить.

Выделите столбец, нажмите Ctrl+H и ведите символ в верхнее окно в данном случае пробел, в нижнее окно ничего не вводите. Кликните по кнопке Заменить все. Тем самым вы замените пробел на пусто.

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

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector