Как в excel сделать разделение строки на подстроки?

как разбить строку на подстроку в excel?

оригинальная колонка выглядит так:

и я хотел бы разбить строку на следующие (:: как разделитель) в Excel

На этой странице объясняется, как это сделать с помощью функции «Text to Columns».

Скопировано для вашего удобства:

Выделите все ваши ячейки с данными.

Выберите Самую Верхнюю Ячейку В Столбце, E.G. A1 удерживайте CTRL+SHIFT и Затем Нажмите Стрелку Вниз. OK, как только мы это сделаем, перейдите в меню «Data» И Выберите «Text To Columns». В Окне Текст В Столбцы Выберите Пункт «Delimited» И Затем Нажал «Next». В Следующем Окне Выберите Пункт «Other» Для Типа Разделителя И Используйте Знак Минус / Дефис-Хит Заканчивать.

Теперь у вас будет два столбца, из вашего примера, первый столбец будет содержать такие данные, как «Animals», а другой столбец будет содержать данные «Дома восходящего солнца». (обратите внимание на SPACE перед «House»)

Чтобы избавиться от этого SPACE, мы будем использовать функцию TRIM.

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

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

Если вы хотите сделать это с помощью forumlae, а не функций «text to columns», которые вы можете использовать:

Предполагая строку в A1

Затем скопируйте C1 через D1:E1

Затем скопировать Г1 по H1:I1 И наконец-то .

Результаты разделения будут в F1:J1. Вы всегда можете скрыть столбцы B:E, так как они являются только внутренними для разбиения. Затем это может быть сделано на столько строк, сколько вам нужно, и если значение в A1 является update, Все остальные значения будут изменены. Однако он находится на фиксированном количестве столбцов, но при необходимости может быть легко расширен.

Excel (и OpenOffice) имеют функцию разбиения текста на столбцы. Выделите все столбцы, соответствующие этой схеме, затем перейдите в меню Данные и выберите «Text to Columns». Используйте разделитель с разделителями и укажите его как»:», обрабатывая последовательные разделители как один.

Вот очень простой способ извлечь 5-й символ слева из текстовой строки в Excel:

Предположим, что символьная строка ABCDEFGHIJ хранится в ячейке A1 в электронной таблице Excel, тогда следующая формула

=Справа(слева(A1, 5), 1)

производит 5-й символ слева в строке, а именно “E”.

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

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

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

Я хочу разделить нижеприведенную строку на подстроку, string = 000000000000000000111111111111111111111111000000 . Как разделить, как у нас есть 48 символов в указанной строке? Я хочу разделить.

Вообще-то я работаю на таленде. Мне нужно загрузить из файла excel в базу данных oracle 11g. Я не могу понять, как разбить поле моего файла ввода excel в talend и загрузить сломанную строку в базу.

У меня есть несколько строк с предложением, и мне нужно разбить его на подстроку максимум 40 символов. Но я не хочу разбивать предложение на середине слова. Я попробовал с ним .функция gsub, но она.

У меня есть строка, которая мне нравится это string s = 09:04:31 00358800917204 FINLAND 00:00:16 2 1.56 21:18:32 00358800917204 FINLAND 00:00:16 2 1.56; .Теперь, согласно моему требованию, я должен.

Я использую этот оператор insert в своем коде в vba excel, но я не могу разбить его более чем на одну строку SqlQueryString = Insert into Employee values( & txtEmployeeNo.Value & _ ,’ &.

Я получил NSString от сервера. Теперь я хочу разделить его на подстроку, которая мне нужна. Как разделить строку? Например: substring1:read от второго символа до 5-го символа substring2:read 10.

мне нужно разбить строку на массив, как 2 + 3 должно быть как 2,+,3 даже 2+3 должно быть 2,+,3

Привет у меня есть таблица excel с около 10 000 записей и около 8 столбцов. Я хотел бы разбить таблицу excel на разные электронные таблицы, содержащие данные из 250 строк каждая. Так, например.

Будучи новичком в программировании Excel, я искал в интернете function/UDF/VBA, который я мог бы использовать в Excel, чтобы найти подстроку в ячейку и выделить ее жирным шрифтом / цветом в этой.

Как мне разбить строку на каждое количество символов X? Например, я хотел бы разбить очень длинную строку каждые 1000 символов, и строка может быть полностью случайной каждый раз. var string =.

Если вам когда-либо приходилось сталкиваться с задачей разделения имен из одного столбца Microsoft Excel на отдельные столбцы для каждого компонента имен — первый, средний, последний — возможно, вы прибегли к трудоемкой комбинации функций для выполнения своей задачи. LEFT, MID и RIGHT могут изолировать части текстовой строки, названной в именах этих функций, но Excel предлагает вам экономящий время способ удовлетворить ту же потребность с гораздо меньшими усилиями. Если ваш текст, состоящий из одной ячейки, содержит символы-разделители, например пробелы между именами в каталоге компании, вы можете поместить свои данные в одну процедуру.

Читать еще:  Как в excel сделать условие на цвет?

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

Перейдите на вкладку «Данные» на ленте Microsoft Excel и найдите группу «Инструменты данных». Нажмите на значок «Текст в столбцы», чтобы вызвать мастер преобразования текста в столбцы.

Нажмите переключатель «Разграничить» на первом экране мастера, если Excel не выберет его автоматически. Мастер анализирует ваши данные и пытается определить, использует ли он разделители или число символов в качестве разделителя подстрок. Фиксированная ширина, альтернатива Delimited, применяется только в том случае, если все ячейки в вашем столбце содержат текст, который вы можете разделить, посчитав определенное количество символов для разделения на каждую подстроку. Нажмите кнопку «Далее», чтобы перейти к следующему экрану.

Выберите разделитель, который разделяет вашу текстовую строку. Excel включает готовые параметры для вкладок, точек с запятой, запятых и пробелов. Если в вашем тексте используется другой разделитель, установите флажок «Другое» и введите разделитель в поле ввода справа от метки параметра. Для текста, в котором используется более одного разделителя, например поле фамилии и имени с данными в формате «Доу, Джоан Мари», отметьте все применимые параметры. Если ваш текст содержит несколько разделителей подряд — например, двойные или несовместимые пробелы — используйте параметр «Рассматривать последовательные разделители как один». Используйте раскрывающееся меню «Определитель текста», чтобы выбрать одинарные или двойные кавычки, если ваш текст содержит слова, заключенные в любой из этих параметров.

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

Нажмите кнопку «Готово» или кнопку «Далее» в нижней части второго экрана мастера. Если вам не нужно устанавливать формат данных или получать доступ к дополнительным параметрам для отдельных столбцов в конечном результате, а исходный столбец данных не находится рядом или не окружен другими важными данными, выберите «Готово». Если применимо, нажмите кнопку «ОК» в диалоговом окне, которое спрашивает, можно ли перезаписать смежные данные. Однако если вы подразделяете столбец, который окружен или рядом с другими столбцами данных, мастер перезапишет соседние данные. Нажмите кнопку «Далее» вместо кнопки «Готово», если это не приемлемо.

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

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

Нажмите кнопку «Готово» на третьем экране мастера преобразования текста в столбцы, если вы использовали его параметры, чтобы уточнить форматирование данных и указать выходное местоположение. Excel подразделяет ваши данные с разделителями. Если ваш столбец исходных данных отображается рядом с другими столбцами данных, Excel отобразит диалоговое окно, в котором вас спросят, хотите ли вы перезаписать эти столбцы данных или отменить процесс подразделения.

Пример функция ПСТР для разделения текста на части в Excel

Функция ПСТР в Excel предназначена для выделения подстроки из строки текста, переданной в качестве первого аргумента, и возвращает требуемое количество символов начиная с заданной позиции.

Примеры использования функции ПСТР в Excel

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

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Читать еще:  Как сделать чтобы excel не ставил дату?

Вид исходной таблицы данных:

Для заполнения номера дня используем следующую формулу (использовать в качестве формулы массива):

  • A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
  • 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
  • 2 – номер последней позиции символа извлекаемой подстроки.

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

Вид заполненной таблицы данных:

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

Как вырезать часть текста ячейки в Excel?

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:

Для заполнения столбца «Марка» используем следующую формулу массива:

Функция НАЙТИ возвращает позицию символа пробела. К полученному числу прибавляется единица для нахождения позиции первого символа названия марки продукта. Итоговое значение используется в качестве аргумента начальная_позиция функции ПСТР. Для упрощения, вместо поиска номера последней позиции (например, с помощью функции ДЛСТР) указано число 100, которое в данном примере гарантированно превышает количество знаков в изначальной строке.

В результате расчетов получим:

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

Функция ПСТР возвращает часть строки до символа пробела, позиция которого определяется функцией НАЙТИ. Для нахождения возраста сотрудника используется функция РАЗНДАТ, полученное значение которой усекается до ближайшего меньшего целого, чтобы получить число полных лет. Функция ТЕКСТ преобразует полученное значение в текстовую строку.

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

  • текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
  • начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
  • число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

Она отличается единственным аргументом:

  • число_байтов – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – число байтов в исходной строке, характеризующий количество байт в возвращаемой подстроке.
  1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
  2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
  3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
  4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Как в excel сделать разделение строки на подстроки?

Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню.

Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных.

В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

  • БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
  • ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
  • ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
  • ЗНАЧЕН (Текст) – преобразует текст в число;
  • ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
  • ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
  • НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
  • ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
  • ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
  • СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;
Читать еще:  Таблица в excel как сделать на одну страницу в ширину

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

Пример 1Дан набор строк:

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

Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

=ПСТР(A2;НАЙТИ(«№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

  • =ПРАВСИМВ(A2;8)
  • но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:
  • =ЗНАЧЕН(ПРАВСИМВ(A2;8))
  • а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

=ЗНАЧЕН(СЦЕПИТЬ(«01»;ПРАВСИМВ(A2;6))) или =ЗНАЧЕН(«01″&ПРАВСИМВ(A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

  1. Пример 2 В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».
  2. Формула будет следующая:
  3. =ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК, последней будет ПОДСТАВИТЬ.

Прикрепленный файл: text_func_excel.zip

Excel подстрока из строки

​Смотрите также​ | 0011​’ Purpose :​​ цифр и вставить​​начальная_позиция – обязательный аргумент,​​Вид таблицы данных:​​ качестве первого аргумента,​

  • ​ регистру.​ лишнего пробела в​ функцию​Скопируйте образец данных из​
  • ​ПОИСКБ​Эта функция возвращает​ строках попадаются​ использовать функцию ПРАВСИМВ(),​ извлечь число или​ возвращает строку «»​
  • ​В этой статье описаны​​В соседней ячейке​
  • ​ Выделяет из текста​ ее в соседнюю​ принимающий целые числа​
  • ​Для заполнения столбца «Наименование»​ и возвращает требуемое​Заменяет определенный текст или​ конце или начале​СЦЕПИТЬ​ следующей таблицы и​не учитывают регистр.​4​английские буквы​
  • ​ но сколько символов​ дату из текстовой​ (пустую строку).​ синтаксис формулы и​ надо получить three​ субстринг/и, ориентируясь по​
  • ​ ячейку справа.​ из диапазона от​ используем следующую формулу:​ количество символов начиная​ символ на требуемое​ строки сложно отследить,​. Она последовательно объединяет​ вставьте их в​ Если требуется учитывать​, так как «н»​. Их также можно​ извлечь? Два? А​

​ строки.​Если значение «начальная_позиция» меньше,​ использование функций​Формула, которая это​ символам-разделителям​

  1. ​китин​
  2. ​ 1 до N,​
  3. ​=ПСТР(A2;1;НАЙТИ(» «;A2))​ с заданной позиции.​
  • ​ значение. В Excel​​ данная функция становится​ значения указанных ячеек​ ячейку A1 нового​
  • ​ регистр, используйте функции​​ является четвертым символом​ обнаружить и извлечь,​ если в других​Данная статья является сводной,​ чем длина текста,​ПСТР​
  • ​ делает (я проверял!)​​’ Notes :​: как то так​ где N –​
  • ​Функция НАЙТИ возвращает номер​​Один символ в языках​ текстовую функцию​ просто незаменимой. На​ в одной строке.​
  • ​ листа Excel. Чтобы​НАЙТИ​ в слове «принтер».​ см. Есть ли в​ адресах номер дома​
  • ​ т.е. в ней​ но сумма значений​и​ такова:​ Substring(текст; символ_разделитель; Начальный_Номер_фрагмента,​ формула массива​ длина строки, из​ позиции символа пробела​
  • ​ с однобайтовой кодировкой​ПОДСТАВИТЬ​ рисунке ниже видно,​Если в Excel необходимо​
  • ​ отобразить результаты формул,​и​Можно также находить слова​ слове в MS​
  • ​ состоит из 1​ содержатся ссылки на​ «начальная_позиция» и «число_знаков»​ПСТРБ​

​СЖПРОБЕЛЫ(ЛЕВСИМВ(ПРАВСИМВ(ПОДСТАВИТЬ(D2,»|»,ПОВТОР(» «,99)),198),99))​ Конечный_Номер_фрагмента), где​200?’200px’:»+(this.

scrollHeight+5)+’px’);»>=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);ПОИСКПОЗ(1=1;ЕЧИСЛО(—ПСТР(ПОДСТАВИТЬ(A2;» «;»»);СТРОКА($1:$50);10));0);10)​ которой требуется извлечь​ « » в​ соответствует 1 байту.

​применяют, когда заранее​ что содержимое ячеек​ сделать все буквы​ выделите их и​НАЙТИБ​ в других словах.​ EXCEL латинские буквы,​

Ссылка на основную публикацию
Adblock
detector