Как в excel сделать разделение строки на подстроки?
Содержание
Всем привет! С вами на связи Александр Глебов. В этой мини статье я рассказываю про то, как разделить текст в ячейке excel по столбцам. Это очень полезная функция excel которая может значительно облегчить жизнь при работе с однотипным текстом. Не буду лить воду, поехали…
Для начала хотел бы вкратце рассказать, что имеется ввиду под понятием разделить текст в ячейке по столбцам. Предположим у вас есть список фамилий, написанный в строчку, а разделителем служит — ; (точка с запятой), и вам очень нужно все фамилии разбить по столбцам. 
Для этого используется специальный инструмент «Текст по столбцам», который за несколько шагов разобьет строку в отдельные ячейки (столбцы).
Как разделить текст в ячейке excel по столбцам по шагам
Итак, что такое разделения текста мы разобрались. Теперь давайте посмотрим, как собственно разбить текст по столбцам. Для этого выполним следующие шаги:
- Будем думать, что excel уже запущен, и нужный текст вставлен в ячейку.
- Далее переходим на вкладку, там нажимаем кнопку «Текст по столбцам».
- У вас откроется мастер: «Мастер текстов (разбор) — шаг 1 из 3», в котором необходимо выбрать формат исходных данных. С разделителями или фиксированная ширина. У нас есть разделитель, точка с запятой. Значит, нам необходимо выбрать форма с разделителями. Нажимаем Далее.
- На втором шаге, вам будет предложено выбрать разделитель. Так как у нас таким является точка с запятой, ставим галку напротив нее. Ниже сразу видно будущий результат. Нажимаем Далее.
- На третьем шаге вам предлагается выбрать формат получаемых данных. Можно для каждого столбца выбрать свой формат. Выбираем нужный вам формат, в большинстве случаем подходит «Общий». Нажимаем готово.
В результате выполнения данной операции вы получите список фамилий, каждая фамилия в отдельном окне. Как видите, разделить текст в ячейке excel по столбцам очень просто, буквально минута, и все готово. Что примечательно, можно использовать любой символ разделитель, хоть знак пробела.
Если у вас остались вопросы по тому, как разбить текст по столбцам в excel, то задавайте их в комментариях. Буду рад помочь.
(
оценок, среднее:
из 5)
Интересные статьи по теме:
| Кнопка «Текст по столбцам» в Excel |
Случайно обнаружил, что в MS Excel нет формулы для разделения строки на части с использованием символа или подстроки в качестве разделителя. Нет вообще, как таковой. В любом нормальном языке программирования есть, а в Excel нет. Для случаев, когда строка представляет из себя число с дробной частью,- там на помощь приходит деление, умножение и остаток, знакомые со школы, хотя для неопытного пользователя и эта задача может оказаться непосильной. А если разделить нужно именно текст? Формула для сращивания строк,- есть, а для разделения,- нет. Гуглинг по вопросу даёт совершенно дикие варианты, типа вот такого:
Формула (стиль A1):
=ЕСЛИ(ЕОШ(НАЙТИ(",";A1));A1;ПСТР(A1;1;НАЙТИ(",";A1)-1))Формула (стиль R1C1):
=ЕСЛИ(ЕОШ(НАЙТИ(",";RC));RC;ПСТР(RC;1;НАЙТИ(",";RC)-1))
К счастью, такая задача нечасто возникает, поскольку в большинстве случаев исходные данные можно сохранить как файл и прогнать через импорт, что вообщем-то решает задачу. К тому-же, если разделение строк нужно разово, а не в виде формулы для динамического пересчёта, то всё не так плохо:
в последних версия Excel есть волшебная кнопочка «Текст по столбцам» (у меня находится именно в виде кнопки в разделе «Данные»). Собственно делает практически тоже самое, что и импорт файлов в формате типа CSV,- предлагает выбрать разделитель, даёит возможность настроить формат получившихся столбцов и всё такое.
Но для решения этой простейшей задачи я потратил не меньше получаса.
Функция ПСТР в Excel предназначена для выделения подстроки из строки текста, переданной в качестве первого аргумента, и возвращает требуемое количество символов начиная с заданной позиции.
Примеры использования функции ПСТР в Excel
Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.
Как разделить текст на несколько ячеек по столбцам в Excel?
Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.
Вид исходной таблицы данных:
Для заполнения номера дня используем следующую формулу (использовать в качестве формулы массива):
Описание аргументов:
- A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
- 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
- 2 – номер последней позиции символа извлекаемой подстроки.
Аналогичным способом выделим номера месяца и годы для заполнения соответствующих столбцов с учетом, что номер месяца начинается с 4-го символа в каждой строке, а год – с 7-го. Используем следующие формулы:
=ПСТР(A2:A10;4;2)
=ПСТР(A2:A10;7;4)
Вид заполненной таблицы данных:
Таким образом нам удалось разрезать на части текст в ячейках столбца A. Удалось отдельно каждую дату разделить на несколько ячеек по столбцам: день, месяц и год.
Как вырезать часть текста ячейки в Excel?
Пример 2. В столбце таблицы хранятся текстовые записи с наименованием и маркой товаров. Разделить имеющиеся строки на подстроки с наименованием и маркой соответственно и записать полученные значения в соответствующие столбцы таблицы.
Вид таблицы данных:
Для заполнения столбца «Наименование» используем следующую формулу:
=ПСТР(A2;1;НАЙТИ(» «;A2))
Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:
Для заполнения столбца «Марка» используем следующую формулу массива:
=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)
Функция НАЙТИ возвращает позицию символа пробела. К полученному числу прибавляется единица для нахождения позиции первого символа названия марки продукта. Итоговое значение используется в качестве аргумента начальная_позиция функции ПСТР. Для упрощения, вместо поиска номера последней позиции (например, с помощью функции ДЛСТР) указано число 100, которое в данном примере гарантированно превышает количество знаков в изначальной строке.
В результате расчетов получим:
Как посчитать возраст по дате рождения в Excel?
Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».
Вид исходной таблицы:
Для возврата строки с фамилией и текущим возрастом используем следующую формулу:
Функция ПСТР возвращает часть строки до символа пробела, позиция которого определяется функцией НАЙТИ. Для нахождения возраста сотрудника используется функция ДОЛЯГОДА, полученное значение которой усекается до ближайшего меньшего целого, чтобы получить число полных лет. Функция ТЕКСТ преобразует полученное значение в текстовую строку.
Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:
Особенности использования функции ПСТР в Excel
Функция имеет следующую синтаксическую запись:
=ПСТР(текст;начальная_позиция;число_знаков)
Описание аргументов:
- текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
- начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
- число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.
Функция ПСТРБ имеет схожий синтаксис:
=ПСТРБ(текст;начальная_позиция;число_байтов)
Она отличается единственным аргументом:
- число_байтов – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – число байтов в исходной строке, характеризующий количество байт в возвращаемой подстроке.
Примечания:
- Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
- Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
- Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
- Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.
