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

Глава 7. Импорт больших текстовых файлов в Power Query

Это продолжение перевода книги Кена Пульса и Мигеля Эскобара. Язык M языка Power Query. Главы не являются независимыми, поэтому я рекомендую читать их последовательно.

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

• Нет разделительных символов,
• Поля на разных строках разделены разным количеством пробелов,
• Есть непечатаемые символы,
• Строки заголовка повторяются.

Power Query автоматически решает большинство из этих проблем. Создайте новую книгу Excel, и новый запрос: Данные –> Из текстового/CSV-файла. Загрузите файл с примерами GLJan—Mar.txt. В окне предварительного просмотра нажмите Изменить. Power Query помещает данные в один столбец:

Рис. 7.1. Импорт неструктурированного текстового файла; чтобы увеличить изображение, щелкните его правой кнопкой мыши и выберите Открыть изображение в новой вкладке

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

Несмотря на отсутствие разделителей в файле, Power Query не делал никаких предположений о том, что содержат данные. Теперь у вас есть возможность обрабатывать данные вручную. Для начала данные представляются как можно быстрее в одинаковых столбцах. Верхние 10 строк кажутся лишними, но 11-я строка напоминает заголовки. В меню Главная выберите Удалить строки -> Удалить верхние строки -> 10.

Следующий шаг — удаление пробелов. Это стандартная практика в Excel при обработке текста. Например, SPACE() удаляет все ведущие, отстающие и повторяющиеся пробелы, а PEXIMV() удаляет непечатаемые символы. Power Query также может это сделать. Щелкните правой кнопкой мыши на столбце Column1 -> Преобразовать -> Удалить. Затем Column1 -> Convert -> Truncate. Tuncate Power Query работает немного иначе, чем функция SPACE() в Excel. Truncate удаляет только передние и задние пробелы, оставляя внутренние пробелы без изменений.

Разделение столбцов

На следующем этапе столбцы будут разделены. Учитывая, что дата содержит 10 символов, постарайтесь оставить еще несколько символов, например, 12 для первой колонки. Главная -> Разделить столбец -> По количеству символов -> 12. Установите переключатель Split следующим образом:

Рис. 7.2: Выделение первой колонки

Усеките две новые колонки еще раз:.

Рисунок 7.3: Промежуточное отображение запросов

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

Рис. 7.4. Выделены четыре столбца

Чтобы разделить последний столбец, выберите Home -> Split Column -> By Separator -> ˽˽ (два пробела). Усеките два получившихся столбца. Power Query автоматически добавляет еще один шаг при каждом разделении столбцов, который заключается в изменении типа нового столбца на текст. Это ненужный шаг, поэтому удалите его. Главная -> Первая строка используется в качестве заголовка. Шаг разделения массива данных на столбцы выполнен:

Рисунок 7.5. Неструктурированные данные, разделенные на колонки

Удаление мусора

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

Рисунок 7.6. Строки заголовка второй страницы перемешаны с данными

Щелкните правой кнопкой мыши на столбце TranDate -> Изменить тип -> Использовать язык -> Дата -> Дата -> Английский (США). Для получения дополнительной информации см. Глава 2: Изменение параметров по умолчанию для Power Query. Отображается ряд ошибок:

Рис. 7.7. Часть ячеек в столбце TranDate содержит ошибки или нули

В отличие от многих других программ, ошибки в Power Query очень функциональны. Вы можете отслеживать их и принимать соответствующие меры. Если вы посмотрите на ошибки на рисунке 7.7, вы заметите, что они появляются только в непригодных строках. Значение null также сигнализирует о том, что эти строки не нужны. Выделите столбец TranDate, щелкните правой кнопкой мыши и выберите Remove Errors (Удалить ошибки). Отфильтруйте столбец TranDate -> снимите выделение null.

На этом этапе все еще есть несколько неактуальных строк; чтобы найти их, отсортируйте TranDate сначала по возрастанию (рис. 7.8), а затем по убыванию, отменив выделение ненужных строк.

Рисунок 7.8. Несколько неактуальных строк

Щелкните правой кнопкой мыши столбец TranDate -> Переименовать -> Дата. Щелкните правой кнопкой мыши по столбцу TranAmount -> Изменить название -> Сумма. Щелкните правой кнопкой мыши над колонкой Сумма -> Изменить тип -> Использовать язык -> Дробный -> Английский (США). Отфильтруйте столбец Amount -> снимите флажок null. Все мусорные строки будут удалены.

Если посмотреть на данные в последних двух столбцах, становится ясно, что деление столбцов выполнено неправильно:

Рисунок 7.9. Последние две колонки разделены неправильно

Объедините столбцы: выберите столбец Reference Information, удерживая клавишу Ctrl, выберите столбец VendorName. Обратите внимание, что порядок выбора столбцов определяет, какой столбец будет объединен первым. Перейдите в меню Преобразовать -> Объединить колонки -> Разделитель -> Пробел. Повторно разделите столбцы с помощью разделителя тире. Дайте разделенным столбцам разумные имена: Категория и Поставщик.

Чистовая обработка

В окне Разделить колонку по разделителю установите переключатель на самый левый разделитель, если в названиях некоторых поставщиков (Vendor) присутствует дефис:

Рис. 7.10. Настройка разделения колонок

Вы не ограничены в использовании разделителей символов при настройке этой операции. В этом поле можно ввести полное слово.

В колонке «Поставщик» могут быть оставлены двойные пробелы. Вы не можете использовать усечение, поскольку оно не работает между словами в столбце. Щелкните правой кнопкой мыши на Vendor -> Replace Values -> Поиск значения — два пробела в строке -> Заменить на — один пробел -> Ok. Если вы подозреваете, что два пробела в строке могут встречаться более одного раза, повторите шаг замены. Дайте запросу говорящее имя, например, Transactions.

Цель достигнута: у вас есть собственный набор данных (рисунок 7.11), который можно загрузить в таблицу: Start -> Close and load.

Рис. 7.11. Удаленные данные

Данные будут загружены в лист Excel и отформатированы как таблица:

Рисунок 7.12. Данные загруженные в таблицу

Проверка импорта

Чтобы просмотреть качество данных, щелкните на любой ячейке таблицы, Вставка -> Сводная таблица -> На существующий лист -> Диапазон G2. Настройте сводную таблицу:

Рисунок 7.13. Сводная таблица проверок качества данных

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

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

Автоматическая обработка подобных файлов

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

• Щелкните правой кнопкой мыши на уведомлении о транзакции -> Изменить
• Перейдите к шагу 1 и нажмите на значок шестеренки
• Переименуйте файл GL Apr-Jun.txt
• Главная -> Закрыть и загрузить.

Рисунок 7.14. Сводная таблица данных за второй квартал

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

Найти и заменить

«Найти и заменить» — это самый простой из всех возможных методов убрать пробелы в ячейке Excel. Чтобы все получилось, нужно соблюдать следующий порядок действий:

1. Выберите ячейку, столбец, строку или их количество (где необходимо удалить «пробелы»). Затем нажмите Ctrl+H на клавиатуре. Эта последовательность клавиш вызывает окно специального инструмента под названием «Найти и заменить».
2. В колонке «Найти» нужно ввести двойной пробел, а в нижней части «Заменить на» только пробел. Вы также можете поставить пробел в строке «Найти» и оставить «Заменить на» пустым, если хотите полностью избавиться от пробелов.
3. После этого остается только нажать «Заменить все».

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

Способ 2: Функция «Найти и заменить»

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

1. На вкладке «Главная» найдите опцию «Найти и выделить», разверните ее выпадающее меню и выберите «Заменить».
2. Откроется окно, которое отвечает за настройку быстрой замены символов. В поле «Найти» дважды нажмите клавишу Пробел, чтобы обозначить искомые данные.

Если вставка пробела с клавиатуры не сработала, можно также попробовать вставить пробел, скопированный из самой таблицы, в поле «Найти». Многие люди считают, что этот вариант помогает.

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

Google Sheets — это мощный и бесплатный инструмент для работы с электронными таблицами, предлагаемый как часть веб-службы Документов Google. Многие физические лица, организации и компании считают, что Google Sheets являются бесценным дополнением к их вычислительной жизни, поскольку электронная таблица (хотя и не обладающая достаточным охватом и возможностями платных программ, таких как Excel), предлагает широкий спектр функциональных возможностей с плавной кривой обучения. И Google Sheets бесплатно.

Несмотря на то, что Google Sheets не обладает таким количеством функций электронных таблиц, как Excel, это все равно отличная альтернатива облачному программному обеспечению. Что касается основных функций электронных таблиц, то они практически идентичны Excel.

Говоря о возможностях, в этой статье мы расскажем вам, как использовать инструмент поиска и замены в Google Sheets и удобное дополнение для удаления пустых пробелов из ячеек вашей таблицы.

Функция TRIM

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

Функция TRIM позволяет удалять начальные и конечные пробелы из ячеек, а также лишние пробелы в тексте. В этом примере откройте новую электронную таблицу Google и введите значение 455 643. B3 в ячейку с тремя начальными пробелами, двумя конечными пробелами и тремя пробелами между цифрами.

Далее выделите ячейку B4 и нажмите на панели FX, затем введите функцию = TRIM (B3) на панели FX и нажмите Enter. Ячейка B4 будет содержать те же значения, что и исходная ячейка B3, с пробелом между числами, как показано ниже. 455,643″ становится «455,643» без ведущего пробела, отстающего пробела и дополнительного пробела.

Функция ЗАМЕНИТЕЛЯ

В Google Sheets также есть функция REPLACEMENT, которая заменяет текст в ячейках. Используя эту функцию, пользователи могут изменить содержимое ячейки или стереть все интервалы между ячейками. SUBSTITUTE (text_to_search, search_for, replace_with, [occurrence_number]) — это синтаксис функции. Это похоже на функцию «найти и заменить», в которой вы ищете текст в ячейке и заменяете его другим.

Чтобы настроить эту функцию для удаления всего пробела из текстовой строки, щелкните ячейку B5. Затем введите = ЗАМЕНА (B3, «», «») в функциональной панели и нажмите Enter. Теперь B5 вернет число 455643 без пробелов в текстовой строке, как показано непосредственно ниже.

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

Инструмент поиска и замены Google Sheet

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

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

Затем в диалоговом окне нужно нажать «Заменить все», затем «Готово». Это удалит все интервалы из ячейки B3. Текст также будет выровнен по правому краю ячейки (потому что Google Sheets теперь считает, что ячейка содержит число, а числа выравниваются по правому краю), поэтому вам может понадобиться настроить выравнивание по мере необходимости.

В качестве альтернативы можно удалить лишние пробелы, не удаляя их все. Сбросьте интервал в ячейке B3, нажав Отменить щелчок, а затем снова выделите ячейку. Нажмите Ctrl + H, введите двойной пробел в поле Найти, нажмите Заменить все, затем нажмите Выполнено, Этот процесс уменьшает все конечные и ведущие интервалы до одного пробела и уменьшает интервал текста до одного пробела.

Удаление пробелов с помощью надстройки Power Tools

В Google Sheets также есть различные надстройки, которые расширяют его возможности и инструменты. Power Tools — это надстройка для листов, с помощью которой вы можете удалять пробелы и разделители из ячеек. Нажмите кнопку + Free на странице дополнений в Google Sheets добавить электроинструменты на листы.

После добавления инструмента Power Tools в Google Sheets выберите ячейку в электронной таблице, чтобы удалить пробелы. В выпадающем меню выберите «Дополнительно», а затем «Инструменты». Затем выберите Удалить, чтобы открыть боковую панель Инструменты, как показано на скриншоте ниже.

Выберите Удалить, чтобы открыть опции удаления пробелов, показанные ниже.

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

• Удалить все пробелы, чтобы удалить все пробелы из ячейки
• Удалить начальные и конечные пробелы, чтобы удалить только начальные и конечные пробелы
• Удалить пробелы между словами, чтобы оставить начальные и конечные пробелы, но удалить дополнительные пробелы между словами
• Удалить элементы HTML, чтобы удалить все теги HTML
• Удалить все удаленные элементы, чтобы удалить элементы, используемые для разделения полей, такие как запятые, используемые в файлах с разделенными запятыми значениями (CSV) или табуляции, используемые в файлах с разделителями табуляции

Это отличный способ сделать лист Google Docs более удобным для пользователя, удалив ненужные пробелы и символы. Например, вы можете загрузить список, содержащий несколько полей, поставщику услуг электронной почты (ESP), и вам нужно очистить файл перед экспортом в CSV-файл, чтобы успешно загрузить его.

Итак, есть две функции и встроенный инструмент поиска и замены, который можно использовать для удаления оставленных пробелов в Google Sheets, а также надстройка (Power Tools), включающая эту функцию и другие полезные инструменты для Google Sheets.

Вам также может понравиться Как объединить ячейки в Google Sheets.

Какие у вас есть советы и рекомендации по работе с Google Sheets? Мы будем рады услышать ваши мысли в комментариях ниже!

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например

По началу слова «Захар» ПОИСК выдал позицию 10. Где это может быть полезно?

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