Ref-sgain.ru

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

Как скопировать строки, если столбец содержит определенный текст / значение в Excel?

Получение данных другого листа (в excel) с помощью формулы

Эта формула позволяет получить данные (значения) с текущего листа с любого другого листа в рабочей книге, изменяя номер строки, номер столбца и/или имя листа по своему усмотрению. Для этого просто введите номер строки в ячейку A1, номер столбца в ячейку B1 и имя листа в ячейку C1. Формула используется только для указания функций категории Ссылки и таблицы.

Если же не убирать галочку в «Сервис» — «Параметры» — вкладка «Диаграмма», то можно этим воспользоваться.
Умышленно скрывая строки мы можем получить из одной диаграммы несколько. В файле примере на диаграмме показывается динамика по «Западу» в то время, как диаграмма содержит данные и по «Востоку». Все дело в том, что строка 4 скрыта. Если же мы отобразим ее, то на диаграмме уже появиться 2 ряда столбцов.
См. файл-пример.

Прикрепления: ne_ubirat.xls (19Kb)

Извлечь число из ячейки смешанного содержания (например 35 шт.; ук467рв) цифры в ячейке должны идти подряд
Доступно только для пользователей =ПСТР(A15;ПОИСКПОЗ(0;(ЕОШИБКА(ПСТР(A15;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A15)));1)*1)*1);0);ДЛСТР(A15)-СУММ((ЕОШИБКА(ПСТР(A15;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A15)));1)*1)*1))) *

Условие ЕСЛИ ячейка содержит определенный текст
Доступно только для пользователей =ЕСЛИ(ЕЧИСЛО(ПОИСК(«текст»;A7;1));»содержит»;»не содержит»)

Подсчет количества по нескольким условиям (Тема обсуждения)
Доступно только для пользователей =СЧЁТ(ЕСЛИ((A2:A11=»Южный»)*(C2:C11=»Мясо»);D2:D11)) *

Подсчет «слов» в ячейке после символа №
Доступно только для пользователей =ДЛСТР(СЖПРОБЕЛЫ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000)))-ДЛСТР(ПОДСТАВИТЬ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000);» «;»»))

Расчет времени между датами. Следующая формула возвращает возраст в годах, месяцах, днях относительно даты в ячейке B2
Доступно только для пользователей =РАЗНДАТ(B2;СЕГОДНЯ();»y»)&» лет «&РАЗНДАТ(B2;СЕГОДНЯ();»ym»)&» мес. «&РАЗНДАТ(B2;СЕГОДНЯ();»md»)&» дн.»

Возвращение последнего значения в столбце.
Если столбец, последнее значение которого нужно найти, не имеет пустых ячеек:
Доступно только для пользователей =СМЕЩ(А1;СЧЕТЗ(А:А)-1;0)
Если столбец может содержать пустые строки:
Доступно только для пользователей =ИНДЕКС(F1:F30;МАКС(СТРОКА(F1:F30)*(F1:F30<>«»))) *
Эта формула возвращает содержимое последней не пустой ячейки в первых тридцати строках столбца F, не стоит злоупотреблять слишком большим количеством строк в столбце, это будет снижать скорость вычислений.

Перечень дней без суббот и воскресений
формула позволяет получить набор дат без суббот и воскресений
Доступно только для пользователей =ЕСЛИ(ДЕНЬНЕД(B2;2)=5;B2+3;B2+1)
(в ячейку B2 следует поместить дату от которой будем отталкиваться, а саму формулу поместить в ячейку B3)

Нумерация строк, которая сохраняется при удалении строк из середины списка
Доступно только для пользователей =МАКС($B$1:B1)+1
формулу ставить в В2 и растянуть вниз.

Извлечение уникальных значений.
Доступно только для пользователей =ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183))))) *
G7:g183 массив значений.

Слегка исправлено, чтобы не было ошибок #NUMBER!

Доступно только для пользователей =ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)))));»»;ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)))))) *

Зебра
Выделить ячейки таблицы (кроме «шапки»), открыть меню Формат — Условное форматирование (Format — Conditional Formatting), выбрать в раскрывающемся списке вариант Формула вместо Значение и ввести такую формулу:
Доступно только для пользователей =ОСТАТ(СТРОКА(A7);2)=0

Буквально: Поиск точного совпадения (0 или FALSE) со значением ячейки A4 в столбце C листа2 и возврат данных из столбца 10, но если значение не найдено, то возвращается текст «None».

Объединить текст в Excel

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

В этой статье мы рассмотрим, как объединить ячейки с текстом в Excel на примерах. Объединить текст из нескольких ячеек можно несколькими способами. Давайте рассмотрим каждый из них.

Все представленные ниже методы объединения текста применимы ко всем версиям: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016. Давайте рассмотрим каждый из них.

Объединить ячейки с текстом с помощью знака амперсанда

Итак, первый способ. Самый простой способ объединить текст в нескольких ячейках в Excel – это использовать знак амперсанда (&).

Читайте так же:
Как сложить или суммировать времена за 24 часа в Excel?

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

1-obedinit-tekst-v-excel

Объединить текст в Excel – Таблица с данными

В одном столбце должны быть фамилия, имя и отчество. Объединить текст в ячейках Excel — вот способ сделать это.

1. Переместитесь в ячейку D2 . Текст из нескольких ячеек будет объединен в этой ячейке.

2. В ячейке с фамилией ( A2 ) введите знак амперсанда &, затем нажмите Shift+7.

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

Разделителем не обязательно должен быть пробел, вы можете использовать любой символ, знак или комбинацию. Для этого необходимо заключить их в инвертированные запятые. Например, если вы хотите разделить текст, который должен быть объединен запятыми, используйте запятую и пробел после («, «).

4. Далее в ячейку с именем ( B2) вводится знак амперсанда &.

5. Опять таки не забываем по разделитель – пробел в кавычках ( » » ).

6. Остается только ввести второе имя. Введите знак & и щелкните по ячейке C2 .

7. Нажмите Enter и полюбуйтесь результатом.

Получаемая формула выглядит следующим образом

И в нашей ячейке D2 отображается объединенный текст из трех ячеек нашей ячейки D2.

2-obedinit-tekst-v-excel

Объединить текст в Excel – Объединить текст с помощью амперсанда

Чтобы заполнить все оставшиеся ячейки достаточно выделить ячейку, и перетащить маркер заполнения вниз. Теперь все ячейки заполнены объединенным текстом, и столбец « ФИО » выглядит следующим образом:

3-obedinit-tekst-v-excel

Объединить текст в Excel – Объединенный текст для всех сотрудников

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

3_1-obedinit-tekst-v-excel

Объединить текст в Excel – Объединить 2 ячейки с текстом

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

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

Введите следующую формулу, чтобы получить желаемый результат:

Это позволяет получить следующую таблицу:

4-obedinit-tekst-v-excel

Объединить текст в Excel – Объединенный текст фамилии и инициалов с помощью амперсанда

Обратите внимание, что в этом случае мы используем точку и пробел (. » ) в качестве разделителей. Перейдем к следующему методу объединения текста в ячейках Excel.

Объединить текст в ячейках с помощью функции СЦЕПИТЬ

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

В таблице должен быть столбец с названием города.

5-obedinit-tekst-v-excel

Объединить текст в Excel – Дополнение исходной таблицы

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

Затем в ячейку мы вводим следующую формулу:

«;B2;»;C2;» живущий в городе «;D2)

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

6-obedinit-tekst-v-excel

Объединить текст в Excel – Объединение текста с помощью формулы СЦЕПИТЬ

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

Объединить текст с датой или числами

В этом случае для объединения текста мы будем использовать функции Link и TEXT. Мы уже изучили функцию Link, теперь давайте познакомимся с функцией TEXT.

Читайте так же:
Как создать выпадающий список с изображениями в Excel?

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

Предположим, у нас есть следующая таблица:

7-obedinit-tekst-v-excel

Объединить текст в Excel – Исходная таблица с данными

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

Для этого нам нужно ввести следующую формулу в ячейку (D2) для первого сотрудника:

=CREATE(A2; «; TEXT($B$1; «DD.MM.YYYYY»);» проданные единицы товара «; B2;» единицы товара, равные «; TEXT(C2; «0%»);» общему объему продаж»)

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

1. А2 – ссылка на ячейку фамилии нашего сотрудника.

2. » « – далее мы вставляем разделитель, в нашем случае это пробел.

3. ТЕКСТ($B$1;»ДД.ММ.ГГ») – при помощи функции ТЕКСТ мы задаем вид даты.

$B$1 – это абсолютная ссылка на дату, ДД.ММ.ГГ – это формат отображаемой даты. В нашем случае исходная дата 13 ноября 2017 г., а мы ее преобразовываем в 13.11.17 (ДД.ММ.ГГ).

4. » реализовал » – продолжаем формировать необходимое нам предложение.

5. B2 – ссылка на ячейку, где хранится информация о количестве реализованной продукции.

6. » единиц товаров, что составило » – добавляем текст.

7. ТЕКСТ(C2;»0%») – ссылка на ячейку C2 указывает на количество процентов, а «0%» – процентный формат.

8. » от общего объема продаж» – завершаем наше предложение.

И заполните оставшуюся часть работ маркером с заливкой.

Результат получился следующим:

8-obedinit-tekst-v-excel

Объединить текст в Excel – Объединить текст в ячейке с датой и числами

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

Объединить текст в ячейках с помощью функции ОБЪЕДИНИТЬ

В версии MS Excel 2016 появилась еще одна функция для объединения текста – функция ОБЪЕДИНИТЬ. Если вы пользователь более ранних версий, то для того чтобы объединить текст, используйте вышеописанные способы.

Слияние доступно только в том случае, если вы подписаны на Office 365.

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

Для начала рассмотрим синтаксис функции UNIT:

В этом случае вместо CELIBUT(separator; disregard_blank; [text1] и [text2]) используется CELIBUT.

  • разделитель – текстовая строка, которой будет разделятся объединенный текст;
  • игнорировать_пустые – принимает значение ИСТИНА (1) или ЛОЖЬ (0). Если необходимо пропустить пустые ячейки, то устанавливаем значение ИСТИНА;
  • текст1 – текстовый элемент, который необходимо объединить;
  • [текст2]; . – дополнительные текстовые элементы для объединения.

Рассмотрим, как объединить текст в Excel с помощью функции КОЛЛЕКТА.

4-obedinit-tekst-v-excel

Объединить текст в Excel – Объединить текст с помощью функции ОБЪЕДИНИТЬ

Статья о том, как объединить текст в Excel, подходит к концу. Как мы видели, в Excel есть несколько способов объединения текста в ячейках, включая амперсанд ( & ), функцию CLOCK, функцию UNITE, а также способ объединения текста с датой или числом в соответствующем формате.

Как разделить текст в excel с помощью формулы

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

Ранее мы рассмотрели деление текста на колонки на примере деления имени на составные части.

Для этого мы используем инструмент Excel «Текст по столбцам».

Читайте так же:
Как скрыть каждую вторую строку в Excel?

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

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

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

Содержание

  • Как разделить текст Имя по столбцам с помощью формул
  • 1.1 Начните делить первую часть текста — Имя
  • 1.2 Начните делить вторую часть текста — Фамилия
  • 1.3 Начните делить третью часть текста — Фамилия
  • 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы.

Пример 1. Разделение текста с именами по столбцам с помощью формул Если мы рассмотрим пример разделения имен, мы можем разделить текст с помощью текстовых формул Excel, используя функции ПСТР и Найти, которые мы уже видели в предыдущих статьях.

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

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

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

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

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

Мы видим, что второй пробел в любом случае находится после первого, но мы уже нашли позицию первого пробела, поэтому, добавив 1 к позиции первого пробела, мы говорим функции ПОИСКПОЗ найти пробел, начиная с первой буквы после первого пробела.

Функция будет выглядеть так

Затем раскройте формулу, чтобы получить положения пространств 1 и 2.

Приступаем к делению первой части текста — Фамилии

Для этого мы будем использовать функцию PSTR. Вот напоминание о ее синтаксисе:

=PSTR(текст-начало_позиции-число_символов), hvor

  • текст — это ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
  • число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

Формула выглядит следующим образом:

Приступаем к делению второй части текста — Имя

Мы снова используем функцию =PSTR(начальная_позиция_текста-число_символов), где

  • текст — это тот же текст ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
  • число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

Приступаем к делению третьей части текста — Отчество

Опять же, функция =PSTR(текст-начало_позиции-число_знаков), где

  • текст — это тот же текст ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
  • — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50
Читайте так же:
Как создать динамический список имен листов в Excel?

Видео: Формулы в Эксель

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

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

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

  • Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
  • Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
  • Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

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

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

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

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

Как разделить текст в excel с помощью формулы

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

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

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

Теперь основная суть приема.

Шаг 1: В колонке справки найдите позицию первого разделителя с помощью функции ПОИСК. Я не буду подробно описывать эту функцию, так как мы уже сталкивались с ней ранее. Напишите формулу в D1 и перетащите ее на все строки.

Это означает, что вы должны искать запятую в тексте, начиная с положения 1.

Читайте так же:
Как создать круг на круговой диаграмме в Excel?

Шаг 2: Далее в ячейке E1 введите формулу для нахождения второго числа (в нашем случае запятой). Формула аналогична, но с небольшими изменениями.

  1. Во-первых: давайте зафиксируем столбец с поисковым значением и текстом так, чтобы при растягивании формулы вправо ссылки на ячейки не перемещались. Для этого введите доллар перед столбцами B и A — либо вручную, либо выделите A1 и B1, трижды нажмите F4, и ссылки станут абсолютными, а не относительными.
  2. Второй: Третий аргумент — начало позиции будет вычислено как позиция предыдущего разделителя (мы нашли его выше) плюс 1, т.е. D1+1, потому что мы знаем, что второй разделитель находится точно после первого и нам не нужно его учитывать.

Давайте напишем формулу и загрузим ее в систему.

Шаг 3: Найдите позиции всех остальных разделителей. Для этого нужно развернуть формулу, использованную для нахождения второго разделителя (шаг 2), вправо на столько ячеек, сколько в них может быть отдельно разделенных значений с небольшим запасом. Мы получим все позиции разделителей. Ошибка #Sign означает, что значения закончились и формула больше не находит разделители. Мы получим следующее

Шаг 4: Выделите первое число из текста, используя функцию PSTR.

Наша начальная позиция равна 1, мы вычисляем количество цифр как первую позицию разделителя минус 1: D1-1 мы растягиваем формулу вниз

Как разделить текст в excel с помощью формулы

Шаг 5. Найдите второе слово с помощью функции PSTR также в ячейке P1

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

Количество цифр — это разница между положением третьего разделителя и второго разделителя минус одна цифра, т.е. E1-D1-1 Определите колонку A исходного текста так, чтобы она не сдвигалась при перетаскивании формулы вправо.

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

Как разделить текст в excel с помощью формулы

Шаг 7: В принципе, наша проблема решена, но для красоты мы напишем отладочную формулу в той же ячейке P1 и заменим ее на пустое значение.

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

Как разделить текст в excel с помощью формулы

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

Когда я писал задачу, я понял, что ее можно упростить. Поэтому в столбце C нужно ввести 0 для первого разделителя. После этого найдите значение первого разделителя

А первый текст — как

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

В столбец «А» вставляются данные, в столбце «В» указывается разделитель, формулы распределяются на необходимое количество ячеек и получается результат.

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

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

Или второе решение — в шаге 3, когда мы составляем формулу для расчета позиций разделителей, заполнить ее. Сделайте проверку, если есть ошибка, то введите сознательно большое число, например 1000.

Как разделить текст в excel с помощью формулы

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector