Разделение текста на столбцы с помощью мастера распределения текста по столбцам
С помощью мастера распределения текста по столбцам текст, содержащийся в одной ячейке, можно разделить на несколько.
Проверьте, как это работает!
- Выделите ячейку или столбец с текстом, который вы хотите разделить.
- На вкладке Данные нажмите кнопку Текст по столбцам.
- В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
- Выберите разделители для своих данных. Например, запятую и пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.
- Нажмите кнопку Далее.
- В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.
- Нажмите кнопку Готово.
Как вытащить число или часть текста из текстовой строки в Excel
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
=ПСТР(текст; начальная_позиция; количество_знаков)
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
В нашем случае формула будет иметь вид:
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
Искомый_текст – обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Comments are currently closed.
Как из ячейки извлечь часть текста excel
Добрый день. Обращаюсь к знатокам Экселя за помощью 🙂
У меня есть ячейки с описанием товаров, мне из всего текста нужно выбрать только артикул. Выглядит это так:
03256СА Жабка»Неллоу!», арт. 03256СА (шт.)
06233 СА «Щаслив родина ж, арт. 06233 СА (шт.)
2551А «Народження», арт. 2551А (шт.)
4006709 Скульпт»Назавжди», арт. 4006709 (шт.).
Мне необходимо составить колонку только из артикулов, то есть, мне нужна часть, которая идет после ‘арт.’ и до ‘(шт.)’
Заранее кланяюсь в ноги 🙂
покупка
Как извлечь часть текстовой строки из ячейки в Excel?
В этом руководстве показаны методы извлечения подстроки из левой, средней или правой части ячейки, а также объясняется, как извлекать текст до или после определенного символа, как показано на скриншотах ниже.
- Извлечь подстроку слева, посередине или справа
Метод A: формулаМетод B: удобный инструмент - Извлечь подстроку после или до указанного символа
Метод A: формулаМетод B: удобный инструмент - Извлечь подстроку между двумя символами
Метод A: формулаМетод B: удобный инструмент расширения - Извлечь адрес электронной почты из строки
- Извлекать числовые или буквенные символы из строки
- Скачать образец файла
- Другие операции (статьи), связанные с пространствами
Извлечь n-й символ из строкиИзвлечь время из datetimeСкоро..
Извлечь подстроку слева, посередине или справа
Метод A: извлечь подстроку слева, посередине или справа с помощью формулы
В Excel есть несколько формул, которые помогут вам быстро извлечь часть текста.
Извлечь первые n символов
Предположим, вы хотите извлечь первые 3 символа из данных данного списка, выберите пустую ячейку, в которую вы хотите поместить извлеченный результат, затем используйте эту формулу
B3 — это ячейка, из которой вы извлекаете символы, 3 — это количество символов, которые вы хотите извлечь.
Нажмите Enter ключ, чтобы получить извлеченный результат. Затем перетащите маркер заливки над ячейками, чтобы применить эту формулу.
Извлечь последние n символов
Например, извлеките последние 6 символов из списка строк, выберите пустую ячейку, в которую вы хотите поместить извлеченный результат, и используйте эту формулу:
= ПРАВЫЙ (B9,6)
B9 — это ячейка, из которой вы извлекаете символы, 6 — это количество символов, которые вы хотите извлечь.
Нажмите Enter ключ, чтобы получить извлеченный результат. Затем перетащите маркер заливки над ячейками, чтобы применить эту формулу.
Извлечь n символов из середины
Если вы хотите извлечь 3 символа, начинающиеся с 4-го символа строки, вы можете использовать следующую формулу:
B15 — это ячейка, из которой вы извлекаете символы, 4 представляет собой извлекаемые символы из 4-го символа (счетчик слева), 3 — это количество символов, которые вы хотите извлечь.
Нажмите Enter ключ, чтобы получить извлеченный результат. Затем перетащите маркер заливки над ячейками, чтобы применить эту формулу.
Примечание:
Если вы хотите переместить извлеченные результаты в другое место, сначала скопируйте и вставьте извлеченные результаты как значение.
Метод B: извлечь подстроку слева, посередине или справа с помощью Kutools for Excel
Если вы не знакомы с формулами, вы можете попробовать Kutools for ExcelАвтора Извлечь текст функция, с которой легко справиться с этой работой.
Прежде чем использовать утилиту Extract Text, потратьте 3 минуты на бесплатную установку. Kutools for Excel: 30-дневная бесплатная пробная версия»> Скачать бесплатно прямо сейчас!
1. Выберите ячейки, из которых нужно извлечь подстроки, щелкните Кутулс > Текст > Извлечь текст.
2. Во всплывающем Извлечь текст диалог под Извлечь по местоположению tab, первые три параметра помогут вам извлечь подстроку слева, посередине или справа.
Первый символ N: извлечь подстроку слева. Например, извлеките первые 2 символа, установите этот флажок и введите 2 в текстовое поле.
Последний символ N: извлечь подстроку справа от строки. Например, извлеките последние 2 символа, установите этот флажок и введите 2 в текстовое поле.
Начальные и конечные символы: извлечь определенное количество символов из середины строки. Например, извлеките с 4-го символа по 9-й, отметьте эту опцию и введите 4 и 9 в текстовые поля отдельно.
Вставить как формулу: установите этот флажок, результатом будет формула, которую можно изменить при изменении исходной строки, в противном случае результат будет фиксированным.
3. Указав нужное местоположение, нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Щелкните ОК.
Extract text string before, after or between characters without formula
Извлечь подстроку после или до указанного символа
Если вы хотите извлечь подстроку после или до указанного символа, вы можете применить один из следующих методов для обработки задания.
Метод A: извлечь подстроку после или до определенного символа с помощью формулы
Предположим, вы хотите извлечь символы после символа « — »Из списка строк, используйте эту формулу:
= ВПРАВО (B3; LEN (B3) -ПОИСК («-«; B3))
B3 — это ячейка, из которой вы хотите извлечь символы, — — это символ, после которого нужно извлечь строку.
Нажмите Enter ключ, чтобы получить извлеченный результат. Затем перетащите маркер заливки над ячейками, чтобы применить эту формулу.
Если вы хотите извлечь подстроку перед определенным символом, вы можете использовать следующую формулу:
= ВЛЕВО (B10; ПОИСК («-«; B10) -1)
Пример результата показан ниже:
Внимание
Данные могут быть потеряны или изменены при копировании и вставке результатов формулы в другое место. Чтобы предотвратить возникновение этой проблемы, вы можете скопировать и вставить результаты формулы как значение после применения формулы. Или вы можете попробовать Способ B.
Метод B: извлечь подстроку после или перед определенным символом с помощью Kutools for Excel
Для прямого извлечения подстроки после или до указанного символа вы можете использовать Извлечь текст полезности Kutools for Excel, который может помочь вам извлечь все символы после или до символа, а также может извлечь определенную длину символов до или после символа.
Прежде чем использовать утилиту Extract Text, потратьте 3 минуты на бесплатную установку. Kutools for Excel: 30-дневная бесплатная пробная версия»> Скачать бесплатно прямо сейчас!
1. Выберите ячейки, в которые нужно извлечь символы, нажмите Кутулс > Текст > Извлечь текст.
2. Во всплывающем Извлечь текст диалога под Извлечь по местоположению перейдите к параметрам перед текстом и после текста, чтобы указать нужную настройку.
Перед текстом: извлекать подстроки перед введенными символами. Например, введите — в текстовое поле, все символы перед — будут извлечены.
После текста: извлечь подстроки после введенного символа (ов). Например, введите — в текстовое поле, все символы после — будут извлечены.
Вставить как формулу: установите этот флажок, результатом будет формула, которую можно изменить при изменении исходной строки, в противном случае результат будет фиксированным.
3. Нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.
Теперь строка до или после определенного символа (ов) была извлечена.
13 текстовых инструментов, которые вы должны иметь в Excel, которые повысят вашу эффективность на 90%
▲ Пакетное редактирование текстовой строки в ячейках, например добавление одного и того же текста в ячейки сразу, удаление символов в любой позиции и так далее.
▲ Кроме инструментов, показанных на картинке, есть еще 300 дополнительных инструментов. Kutools for Excel, который может решить ваши 82% головоломок Excel.
▲ Станьте экспертом по Excel за 5 минут, получите признание и продвижение по службе.
▲ 110000+ высокоэффективных сотрудников и 300+ всемирно известных компаний.
30 -дней бесплатной пробной версии, кредитная карта не требуется Узнать больше Скачать сейчас
Извлечь подстроку между двумя символами
Возможно, в некоторых случаях вам нужно извлечь подстроку между двумя символами, вы можете выбрать один из следующих методов для обработки задания.
Метод А: извлечение по формуле
Предполагая, что из заданного списка извлекаются символы между скобками (), вы можете использовать следующую формулу:
=MID(LEFT(B3,FIND(«)»,B3)-1),FIND(«(«,B3)+1,LEN(B3))
В формуле B3 — это ячейка, из которой вы хотите извлечь строку, ( и ) — это два символа, между которыми вы хотите извлечь строку.
Нажмите Enter ключ, чтобы получить извлеченный результат. Затем перетащите маркер заливки над ячейками, чтобы применить эту формулу.
Внимание
Если формула для вас немного сложна, вы можете попробовать метод B, который использует удобный инструмент для быстрого решения этой проблемы.
Метод B: Извлечь по Kutools for Excel
In Kutools for Excelсотни функций, есть функция — Извлечь строки между указанным текстом может быстро извлекать подстроки между двумя символами.
Прежде чем использовать утилиту Extract Text, потратьте 3 минуты на бесплатную установку. Kutools for Excel: 30-дневная бесплатная пробная версия»> Скачать бесплатно прямо сейчас!
1. Выберите ячейку, которая использовалась для размещения извлеченной подстроки, щелкните Кутулс > Формула Помощник > Текст > Извлечь строки между указанным текстом.
2. в Помощник по формулам диалога, перейдите к Ввод аргументов раздел, затем выберите или напрямую введите ссылку на ячейку и два символа, которые вы хотите извлечь между ними.
По умолчанию, когда вы выбираете ссылку на ячейку, которая использовалась для извлечения подстроки, ссылка на ячейку будет абсолютной, что не может использовать дескриптор автозаполнения для заполнения формулы, пожалуйста, измените его на относительный.
3. Нажмите Ok, теперь первый результат получен, затем перетащите дескриптор автозаполнения на ячейки, которым нужна эта формула.
Наконечник:
Если вы хотите извлечь строки между двумя символами (включая два символа), Извлечь текст полезности Kutools for Excel также может оказать вам услугу в этой операции.
1. Выберите ячейки, в которые нужно извлечь подстроку между символами, нажмите Кутулс > Текст > Извлечь текст.
2. во всплывающем Извлечь текст диалога под Извлечь по правилу вкладка, перейдите в Текст раздел, введите символы, между которыми вы хотите извлечь строку, и строка может быть заменена подстановочным знаком * . Если вы хотите извлечь строку фиксированной длины, подстановочный знак ? можно использовать, один? указать один символ.
Затем нажмите Добавить добавить правило к Описание правила .
3.Click Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.
Теперь строка между двумя определенными символами была извлечена.
Извлечь адрес электронной почты из строки
Если вы хотите извлечь адрес электронной почты из заданной строки или диапазона ячеек, вы можете использовать Извлечь адрес электронной почты функция для одновременной обработки этой работы, а не для поиска их по очереди.
Прежде чем использовать утилиту извлечения адреса электронной почты, потратьте 3 минуты на бесплатную установку. Kutools for Excel: 30-дневная бесплатная пробная версия»> Скачать бесплатно прямо сейчас!
1. Выберите ячейки, в которых будет извлечен адрес электронной почты, затем нажмите Кутулс > Текст > Извлечь адрес электронной почты.
2. Затем появится диалоговое окно, в котором вы можете выбрать ячейку для вывода адресов электронной почты.
3. Нажмите OK, адреса электронной почты в каждой ячейке были извлечены.
Извлекать числовые или буквенные символы из строки
Если есть список данных, смешанных с числовыми, буквенными и специальными символами, вы просто хотите извлечь числа или буквенные значения, вы можете попробовать Kutools for Excelутилита удаления символов.
1. Перед использованием утилиты удаления символов вам необходимо иметь копию данных, как показано на скриншоте ниже:
2. Затем выберите эту копию данных, нажмите Кутулс > Текст > Удалить символы.
3. в Удалить символы диалог, проверьте Нечисловой , нажмите Ok.
Теперь остались только числовые символы.
Чтобы извлечь только алфавитные значения, установите флажок Не альфа вариант в Удалить символы Диалог.
Скачать образец файла
Другие операции (статьи), связанные с преобразованием файлов
Извлечь время из строки даты и времени
Предоставляет трюки для извлечения времени (чч: мм: сс) или часов / минут / секунд только из строки даты и времени (мм / дд / гггг чч: мм: сс)
Извлечь строки, соответствующие критериям
В этой статье он может помочь вам быстро извлечь эти строки, соответствующие критериям, в другое место в Excel, за исключением поиска и копирования их по одной.
Извлечь n-й символ из строки
Здесь будут представлены методы извлечения n-го символа из строки, например, извлечение 3-го символа из строки a1b2c3, результат — b.
Извлечь подстроку между двумя символами
Показать методы извлечения подстроки между двумя одинаковыми или разными символами.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .
Office Tab — приносит интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!