Light-electric.com

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

Excel удаление пробелов в конце строки

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

Рассмотрим способы удаления пробелов в Excel (например, между словами, в начале или конце предложений, в числах).

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:

  • В начале или в конце предложений;
  • Дублирование пробелов;
  • Невидимые символы, в т.ч. неразрывный пробел;
  • В числах (в разделителях разрядов).

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

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

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

Рассмотрим 2 основных способа, как можно удалить лишние символы:

  • Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
  • С помощью функций. Формульный вариант удаления.

Инструмент «Найти и заменить»

Предположим, что у нас есть текст или таблица со словами, в котором присутствует дублирование пробелов.

Для начала выделяем диапазон ячеек с данными и выбираем на панели вкладок Главная -> Редактирование -> Найти и выделить -> Заменить либо просто нажимаем сочетание клавиш Ctrl + H:


Появляется стандартное диалоговое окно замены, затем вводим в поле Найти два пробела, а в Заменить на — один. Нажимаем Заменить все и повторяем операцию до тех пор, пока Excel находит повторения.

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

С помощью функций

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

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


Далее копируем содержимое столбца B и вставляем значения без связей в столбец A, после чего удаляем вспомогательный столбец.
Минусом данного способа является его неудобство при работе с большими объемами данных, так как для каждой редактируемой ячейки необходимо создавать дополнительное место где будет обрабатываться исходная ячейка.

Неразрывный пробел в Excel

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

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


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

Каждый символ в Excel имеет свою кодировку в таблице знаков ANSI (для ОС Windows), в том числе и интересные нам одиночный пробел (символ 32) и неразрывный (символ 160).

Поэтому с помощью функции ПОДСТАВИТЬ заменяем неразрывный пробел (символ 160) на обычный (символ 32), а затем к полученному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый результат:


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

Возникает вопрос, как это сделать?

Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.

Второй способ — воспользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая клавишу Alt нужно набрать 0160 на цифровой клавиатуре).

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

Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст.

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

Аналогичный результат можно получить и с помощью формулы:

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

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

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

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

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

Способ 1: с помощью инструмента «Заменить»

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

  1. Перейдите на вкладку «Главная».
  2. На панели инструментов, в правой ее части, нажмите по кнопке «Найти и выделить». Нажмите по ней.
  3. В появившемся выпадающем списке выберите пункт «Заменить».
  4. Появится окно, в котором в поле «Найти» введите двойной пробел. Для этого просто дважды нажмите по соответствующей клавише.
  5. В поле «Заменить на» пропишите один пробел.
  6. Нажмите кнопку «Заменить все».
  7. В окне с отчетом нажмите «ОК».

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

Способ 2: удаление пробелов между разрядами

Только что мы разобрались, как удалить пробелы в ячейках Excel с текстовыми выражениями, но что, если появилась потребность убрать лишние одинарные пробелы в числовых выражениях, которые зачастую устанавливаются автоматически? В этом случае нам также поможет инструмент «Заменить».

  1. В таблице выделите область, в которой находятся ненужные пробелы.
  2. Находясь на главной странице на панели инструментов, нажмите по кнопке «Найти и заменить» и выберите инструмент «Заменить».
  3. В появившемся окне на этот раз нужно ввести немного другие значения.
  4. В поле «Найти» поставьте один пробел.
  5. Поле «Заменить на» оставьте нетронутым.
  6. Нажмите кнопку «Заменить все».
  7. Подтвердите свои действия, ознакомьтесь с отчетом и примите его.

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

Способ 3: удаление путем форматирования текста

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

  1. Выделите область таблицы, в ячейках которой вы хотите избавиться от пробелов между числовыми выражениями.
  2. Кликните по выделению правой кнопкой мыши.
  3. Выберите пункт «Формат ячеек».
  4. Появится соответствующее окно, в котором перейдите в раздел «Числовой», расположенный во вкладке «Число».
  5. В правой части окна снимите отметку с пункта «Разделитель групп разрядов».
  6. Нажмите «ОК».
Читать еще:  Как сохранить в формате excel

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

Способ 4: с помощью специальной функции

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

  1. Установите курсор в той ячейке, которая находится параллельно столбцу или строке, пробелы из которых нужно убрать.
  2. Откройте окно Мастера функций, нажав по соответствующей кнопке, находящейся рядом со строкой функций.
  3. В списке «Категория» выберите «Текстовый», а в списке с перечнем функций выделите «СЖПРОБЕЛЫ» и нажмите «ОК».
  4. Появится окно ввода функции. В нем вам нужно указать ячейку, в которой хотите убрать пробелы. Для этого просто кликните по ней левой кнопкой мыши.
  5. Нажмите «ОК».

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

Извлечение слов из текста в Excel

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

Важное замечание!

Задача 1. Подсчет количества слов в тексте

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

Итак, слова в тексте отделены друг от друга пробелами. Пробелов всегда на 1 меньше, чем слов (действительно, между двумя словами — один пробел, между тремя — два и т.д.). Значит, достаточно подсчитать пробелы и прибавить к ним единицу, чтобы узнать число слов в ячейке.

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

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

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

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

Прибавим к ней единицу — и получим число слов в ячейке.

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

Задача 2. Извлечение первого слова из текста

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

Найти символ в тексте поможет функция НАЙТИ() . У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Результатом работы функции является позиция первого символа найденного текста в строке. Например, формула =НАЙТИ(«плюс»;»Один плюс один») вернет в результате число 6 (именно с этой позиции начинается искомое слово «плюс»). Если в строке несколько раз содержится искомое слово, то вернется результат для первого вхождения.

Функция НАЙТИ работает с учетом регистра и без подстановочных знаков. Если нужно их использовать, или не учитывать регистр — есть аналогичная функция ПОИСК() .

Теперь мы можем найти позицию первого пробела

Извлечь символы до пробела поможет функция ЛЕВСИМВ . Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5.

Формула для извлечения первого слова:

Если в строке одно слово (то есть нет пробела между первым и вторым словом), данная формула вернет ошибку. Чтобы этого избежать, нужно добавить к ней функцию ЕСЛИОШИБКА() .

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

Задача 3. Извлечение последнего слова из текста

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

  • подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

  • с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

  • теперь, когда последний пробел заменен на «*», мы можем узнать его позицию с помощью функции НАЙТИ() . В качестве текста, в котором ищем, укажем предыдущую формулу

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

Как и в предыдущей задаче, можно обернуть в ЕСЛИОШИБКА , чтобы верно работало с ячейками, в которых только 1 слово.

Читать еще:  Как распечатать excel

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

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

Function РЕВЕРС(str As String)

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

Берите на вооружение, если любите макросы.

Задача 4. Извлечение n-ного слова из текста

Самый интересный пример. Для решения подобной задачи нужно применить фантазию. Разберем решение поэтапно.

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

1) ПОВТОР() . Умеет повторять указанный текст указанное число раз.

2) ПСТР() . Имеет 3 аргумента:

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

Теперь мы вооружены всем нужным инструментарием. Приступаем:

  • Для начала воспользуемся функцией ПОВТОР() , чтобы вместо 1 пробела в тексте вставить число пробелов, равное длине исходного текста. Количество повторений узнаем через уже известную функцию ДЛСТР() . Итак, формула:

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

  • Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова):

=ДЛСТР(A1)*(n-1)+1

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

  • Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m ) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

  • Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

Не забудьте в формуле заменить n на номер извлекаемого слова или ссылку на ячейку с этим номером.

Задача 5. Извлечение имя файла из полного пути к нему

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

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

Берем формулу из задачи 3 и заменяем в ней пробелы на «».

Получим следующую формулу:

Вставляем в ячейку и вуаля! Имя файла извлечено.

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Извлечение слов из текста в Excel

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

Важное замечание!

Задача 1. Подсчет количества слов в тексте

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

Итак, слова в тексте отделены друг от друга пробелами. Пробелов всегда на 1 меньше, чем слов (действительно, между двумя словами — один пробел, между тремя — два и т.д.). Значит, достаточно подсчитать пробелы и прибавить к ним единицу, чтобы узнать число слов в ячейке.

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

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

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

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

Прибавим к ней единицу — и получим число слов в ячейке.

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

Задача 2. Извлечение первого слова из текста

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

Найти символ в тексте поможет функция НАЙТИ() . У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Результатом работы функции является позиция первого символа найденного текста в строке. Например, формула =НАЙТИ(«плюс»;»Один плюс один») вернет в результате число 6 (именно с этой позиции начинается искомое слово «плюс»). Если в строке несколько раз содержится искомое слово, то вернется результат для первого вхождения.

Функция НАЙТИ работает с учетом регистра и без подстановочных знаков. Если нужно их использовать, или не учитывать регистр — есть аналогичная функция ПОИСК() .

Теперь мы можем найти позицию первого пробела

Извлечь символы до пробела поможет функция ЛЕВСИМВ . Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5.

Формула для извлечения первого слова:

Если в строке одно слово (то есть нет пробела между первым и вторым словом), данная формула вернет ошибку. Чтобы этого избежать, нужно добавить к ней функцию ЕСЛИОШИБКА() .

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

Задача 3. Извлечение последнего слова из текста

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

  • подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1.
Читать еще:  Как автоматически пронумеровать ячейки в excel

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

  • с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

  • теперь, когда последний пробел заменен на «*», мы можем узнать его позицию с помощью функции НАЙТИ() . В качестве текста, в котором ищем, укажем предыдущую формулу

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

Как и в предыдущей задаче, можно обернуть в ЕСЛИОШИБКА , чтобы верно работало с ячейками, в которых только 1 слово.

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

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

Function РЕВЕРС(str As String)

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

Берите на вооружение, если любите макросы.

Задача 4. Извлечение n-ного слова из текста

Самый интересный пример. Для решения подобной задачи нужно применить фантазию. Разберем решение поэтапно.

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

1) ПОВТОР() . Умеет повторять указанный текст указанное число раз.

2) ПСТР() . Имеет 3 аргумента:

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

Теперь мы вооружены всем нужным инструментарием. Приступаем:

  • Для начала воспользуемся функцией ПОВТОР() , чтобы вместо 1 пробела в тексте вставить число пробелов, равное длине исходного текста. Количество повторений узнаем через уже известную функцию ДЛСТР() . Итак, формула:

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

  • Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова):

=ДЛСТР(A1)*(n-1)+1

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

  • Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m ) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

  • Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

Не забудьте в формуле заменить n на номер извлекаемого слова или ссылку на ячейку с этим номером.

Задача 5. Извлечение имя файла из полного пути к нему

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

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

Берем формулу из задачи 3 и заменяем в ней пробелы на «».

Получим следующую формулу:

Вставляем в ячейку и вуаля! Имя файла извлечено.

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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