Excel как извлечь часть текста из ячейки
Извлечение части текста – самая распространенная задача при работе с текстом в Excel. Часто к этой задаче и сводится вся работа над текстом. Чтобы получить часть символов из строки, нужно владеть функциями поиска, удаления лишних символов, определения длины строки и др.
Для получения части текста, в Эксель есть 3 функции:
- ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;
Функция ЛЕВСИМВ в Эксель
- ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .
Функция ПРАВСИМВ в Excel
- ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).
Функция ПСТР в Эксель
Все эти функции в подсчёте количества символов учитывают лишние пробелы, непечатаемые символы, поэтому рекомендую сначала очистить текст от лишних знаков.
Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это простой и мощный инструмент, если используется в комбинации с другими текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.
А следующий пост мы посвятим поиску нужного текста в строке. Заходите и читайте. Только хорошее владение функциями позволит вам эффективно выполнять задачи в Microsoft Excel!
Если вы еще не прочли посты о написании формул и применении функций – обязательно это сделайте, без них изучение функций Excel будет сложнее и дольше!
Как в Эксель (Excel) вытащить часть текста из ячейки в другую ячейку?
Как в Excel извлечь часть текста из ячейки?
Например, в ячейке написана категория товара и информация о товаре.
Как выташить в отдельную ячейку только название категории («Перчатки хозяйственные», «Молоток слесарный» и т.п.)?
Если у Вас данные (которые нужно обработать, все эти «молотки» и «перчатки») всегда отделены от остальной части текста запятой и первая ячейка с данными это B2, то формула такая
напишите ее в любую свободную ячейку (например правее) в той же строке, а потом растяните вниз и все ваши тысячи строк будут обработаны.
Очевидно, что вытащить часть текста в другую ячейку можно с помощью специальных функций для работы со строками.
В Excel их довольно много, и в первую очередь можно выделить такие функции, как:
ЛЕВСИМВ и ПРАВСИМВ — излекают определённое число символов слева и справа соответственно.
ДЛСТР — длина строки.
НАЙТИ — возвращает позицию, с которой подстрока или символ входит в строку.
ПОДСТРОКА — извлекает подстроку из текста, которая отделена определённым символом-разделителе м.
ПСТР — извлекает указанное число знаков из строки (начиная с указанной позиции).
КОНЕЦСТРОКИ и НАЧАЛОСТРОКИ — возвращает строку после / до указанной подстроки.
Но здесь всё зависит от того, как именно эти данные расположены в исходной строке — одно дело в самом конце / начале, а другое — в середине.
В любом случае нужно постараться найти какой-то признак — слово или символ, до или после которого в ячейке находятся нужные данные, после чего использовать его в качестве аргумента в функциях, про которые я написал выше.
Пример 1
Исходные данные такие:
Предположим, нужно извлечь в отдельную ячейку цену товара (3500 рублей, 4200 рублей).
Можно увидеть, что в этих ячейках цене предшествует текст «размеры, » — то есть можно воспользоваться функцией КОНЕЦСТРОКИ и вытащить всё, что находится после этого текста.
Итак, ставим курсор в ячейку, куда нужно извлечь цену, и на вкладке «Формулы» выбираем «Текстовые» -> «КОНЕЦСТРОКИ».
Указываем аргументы функции (обязательные):
ТЕКСТ — указываем ячейку, из которой нужно извлечь подстроку (B2 или B3).
НАЙТИ — указываем подстроку, после которой должно начаться извлечение текста («размеры, «).
Нажимаем на кнопку «OK» и получаем то, что было нужно:
Формула получилась такая:
А если требуется, чтобы было только число (без рублей), то можно, например, использовать функцию НАЧАЛОСТРОКИ.
В этом случае в качестве 1 аргумента (исходной строки) вводим формулу, созданную выше, а в качестве 2 аргумента — » «.
Пример 2
Нужно извлечь в отдельную ячейку название цвета (красный, коричневый и т.п.).
Здесь всё проще, так как название цвета находится в самом конце строки — и можно, например, использовать функцию ПРАВСИМВ.
У этой функции 2 аргумента:
Текст — указываем ячейку, из которой нужно извлечь подстроку.
Число_знаков — это разность между длиной исходной строки (функция ДЛСТР) и позицией запятой в этой строке (функция НАЙТИ), также дополнительно нужно отнять единицу, так как после запятой стоит пробел.
Формула и результат:
Но мне всё же больше нравится вариант с упомянутой выше функцией КОНЕЦСТРОКИ.
Она менее громоздкая и не содержит вложенных функций.
Эксель многие любят за то, что можно быстро обрабатывать и менять таблицы, так как надо.
Вот и в этом случаи, для того, чтобы вытащить из ячейки текст, нужно в пустой рядом столбик ввести формулу. Но тут не так всё просто. В зависимости от того, с какой стороны нужен текст, вводим формулу Левсимв и Правсимв. Одна из этих функций выведет нужный текст справа, другая слева. При этом формула будет выглядеть примерно так:=ЛЕВСИМВ(В1;10). В данном случаи 10 число символов. Но если число символов не одинаковое, то метод не совсем подойдёт.
Тогда можно будет попробовать функцию текстовые, конец строки. Если перед нужной вам фразой стоит одно и тоже слово в каждой строке. Появится окошко, и в строке найти добавить это слова. Нужный текст после этого слова переместится.
Если в таблице одна или 2 строки, тогда можно воспользоваться функцией нажатия клавиш Ctrl+C скопировать и Ctrl+V вставить, а если в таблице нужно поменять цену для большого количества параметров, переходите в шапку инструментов, и действуйте по алгоритму, который находится под кнопой формулы — текстовые и в выпадающем меню находите среди абракадабры из сокращений «конецстроки»
далее следуя указаниям меняете в открытых окнах параметры. Подставляете какие заданы, а машина сама все посчитает. За это эксель и любят бухгалтеры, по мне так самая кривая программа после ворда. имхо для бв.
Что касается абракадабры в выпадающем меню, на это есть подсказки, например
ЛЕВСИМВ — левые символы
ПРАВСИМВ — правые символы
ДЛСТР — длина строки
НАЙТИ — возвращает позицию, с которой подстрока или символ входит в строку.
КОНЕЦСТРОКИ возврат строки до конца
НАЧАЛОСТРОКИ — возврат строки в начало
Открывайте ячейку из которой надо вытащить часть текста, клацаете по тексту что бы курсор в тексте начал моргать, выделяете эту часть текста которую хотите утащить в другое место, щелкаете по выделенке ПКМ (правой кнопкой мышки) выбираете «копировать»
Переходите в окно куда нужно вставить, щелкаете в нем ЛКМ (левой кнопкой мышки) что бы активировать работу ввода данных в этой ячейки, следом щелкаете ПКМ, выбираете «вставить» и все.
Довольно сложный вопрос, но в Ексель можно сделать и такое, в этом редакторе есть подобные функции работы со строками.
Эти функции мы ищем в верхнем меню во вкладке «Формулы» — «Текстовые»:
Желательно, чтобы записи в ячейках были бы хоть как-то структурированы, например, если в ячейках сначала записано наименование товара, потом через запятую, в конце записи, цена товара, с такими ячейками будет работать несложно. Поработаем вот с этими ячейками, попробуем цену товара перенести в отдельные ячейки:
Текст у нас написан для этого отлично, цена товара стоит в конце строки, после слова «размеры» и запятой, поэтому мы воспользуемся функцией КОНЕЦСТРОКИ из вкладки «Текстовые» (см. выше). Открывается вот такое окошечко, в поле ТЕКСТ указываем столбец, в котором находятся наши ячейки, в поле НАЙТИ — слова, после которых текст надо переносить в отдельную ячейку.
Нажимаем ОК, получаем то, что хотели:
Теперь можно, используя тот же алгоритм, поработать с новыми ячейками с помощью функции НАЧАЛОСТРОКИ и получить число без рублей:
Как вытащить число или часть текста из текстовой строки в Excel
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
=ПСТР(текст; начальная_позиция; количество_знаков)
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
В нашем случае формула будет иметь вид:
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
Искомый_текст – обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Как вытащить часть текста из ячейки excel?
Действительно можно столкнуться с ситуацией, когда необходимо из какой-то одной или несколько ячеек в программе эксель необходимо вытащить часть текста.
В программе эксель существует несколько функций, которые работают с текстом и позволяют решить поставленную в этом вопросе задачу. Сначала можно выделить их:
1. ПРАВСИМВ и ЛЕВСИМВ – данные функции наиболее часто используют для решения поставленного вопроса. Функция ПРАВСИМВ возвращает указанное количество символов справа, ЛЕВСИМВ слева.
2. ДЛСТР – это функция позволяет узнать количество символов строки.
3. НАЙТИ – находит в тексте положение определенного элемента или слова.
Именно с помощью применения данных четырех функций можно легко решить стоящую перед вами задачу. А теперь рассмотрим несколько простых примеров.
Пример №1.
Перед нами таблица, в которой указаны ссылка на разные статьи, нам необходимо убрать из каждой ссылки буквы: .html
Чтобы решить данную задачу, мы будем использовать две функции ДЛСТР и ЛЕВСИМВ. С помощью функции ДЛСТР будем определять длину строки, так как строки имеют у нас разную длину. А функцией ЛЕВСИМВ, будем оставлять нужное количество символов. Также заметим, что строчка .html состоит из 5 символов, т.е. в независимости какая длина строки, всегда нужно убирать пять элементов. И так в ячейке «С2» напишем формулу: =ЛЕВСИМВ(B2;ДЛСТР(B2)-5). После её скопируем в оставшиеся ячейки.
Пример № 2. Перед нами другая в таблица, в которой указана номенклатура товара, наша задача убрать из текста весовые параметры продукта, которые находятся после запятой.
В этом примере в отличие от первого, мы заранее не знаем сколько нужно отрезать символов, поэтому просто посчитать их количество, нам ничего не даст. Но мы знаем, что они все отделены запятой, поэтому будем использовать функцию «НАЙТИ», чтобы понимать, где запятая. Поэтому в ячейке «С2» пишем формулу: =ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)-1) И копируем её на оставшиеся ячейки.
Таким образом, каждая задача нетривиальная и вам придется решать какой набор функций использовать, чтобы получить нужный результат.
Примеры использования функции ЛЕВСИМВ в Excel работа с текстом
Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.
Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?
Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.
Исходная таблица данных:
Для выделения первого символа, которым является искомое название переменной, используем формулу:
Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:
Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.
Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?
Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.
Выделим код ошибки с помощью следующей формулы:
- ЛЕВСИМВ(B5;НАЙТИ(«3»;B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
- 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).
В результате получим:
Выделим часть текста ошибки:
В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).
В результате получим:
Округление чисел текстовыми функциями в текстовой строке
Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.
Форма для округления числовых значений имеет следующий вид:
В ячейке B3 введена следующая сложная формула:
=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’ > 
- Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
- Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
- Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то есть целой части числа +1 по правилам округления.
- Если
Как вытащить число или часть текста из текстовой строки в Excel
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
=ПСТР(текст; начальная_позиция; количество_знаков)
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
В нашем случае формула будет иметь вид:
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
Искомый_текст – обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Как перенести текст в ячейке на следующую строку в Microsoft Excel
Большинство настроек Microsoft Excel задаются по умолчанию, среди них ограничение: одна ячейка – одна строка. Но бывает множество случаев, когда в ячейке нужно сделать несколько строчек. Для этого можно использовать несколько способов.
Помимо расположения слов в одной ячейке, часто оказывается проблемой написание длинного слова: либо его часть переносится в другую строку (при использовании Enter), либо оно заезжает на соседний столбец.
Офисный продукт Excel на многие задачи имеет вариативную линейку решений. Перенос строки в ячейке Excel не исключение: от разрыва вручную до автоматического переноса с заданной формулой и программирования.
Используем комбинацию клавиш
В этом случае используется сочетание клавиш «Alt+Enter». Пользователю будет удобнее сначала зажать «Alt», затем, не отпуская клавиши нажать «Enter».
Переносить текст в ячейке этой комбинацией можно в момент набора или после того, как все слова написаны в одну строчку. Нужно перед нужным словом поставить курсор и нажать указанное сочетание клавиш – это сделает новую строку в ячейке.
Формат ячеек
Когда требуется написать большой текст можно применить команду «формат ячеек», что позволит уместить текст в одной ячейке Эксель.
Нажатием мышки выделить требуемую область. Правой кнопкой мыши вызвать диалоговое окно с командами. Выбрать «формат ячеек».
На верхней панели выделить раздел «выравнивание».
Поставить галочку против команды «переносить по словам» (графа «отображение»). Нажать кнопку ОК.
После чего диалоговое окно закроется. Теперь ячейка заполняется длинным текстом, перенос слов будет осуществляться автоматически. Если слово не помещается по ширине, то программа Excel сама перенесет строку в ячейке.
Если таких ячеек должно быть несколько, нужно выделить их все левой кнопкой мышки и произвести указанный выше алгоритм действий. Во всей выделенной области появится автоматический перенос.
Использование формул
Когда пользователю нужно не только поделить текст на строки, но предварительно собрать информацию с нескольких мест, то вышеперечисленные способы не подходят. В этом случае применяют несколько видов формул переноса по словам:
- символ ();
- сцепить ();
- подставить ().
Символ
Внутри скобок указывается код – цифровое значение от 1 до 255. Берется код из специальной таблицы, где указано число и соответствующий ему символ переноса. Перенос имеет код – 10. Следовательно, используется формула – «символ(10)».
Разберем на конкретном примере, как работать с формулой «символ(10)». Для начала заполним ячейки, которые впоследствии объединим. 4 столбца первой строки – фамилия, пол, возраст, образование. Вторая – Иванова, жен, 30, высшее.
Выбираем, затем выделяем ячейку, куда осуществим перенос. Ставим курсор в строку формул.
Заполняем формулу (для выбранной ячейки):
Где знак «&» означает сцепление соответствующих ячеек, а символ(10) – перенос строки после каждой сцепленной пары.
После написания формулы нажать клавишу «Enter». В выделенной области появится результат.
На скриншоте видно, что не задан формат. Используем «формат строки», как было указано выше. После того, как будет отмечена галочка напротив пункта «переносить по словам», ячейка приобретет вид, как на картинке ниже.
Есть еще один способ быстро использовать данную команду. Справа вверху есть раздел «Формат». Следует нажать на маленькую черную стрелочку, чтобы вызвать диалоговое окно. Внизу находиться искомая команда.
Если заменить исходные данные другими, содержимое ячейки также будет меняться.
Сцепить
Функция «сцепить()» похожа на предыдущую. Она тоже задается формулой, но в скобках указывается не код, а формула «символ(10)».
Возьмем для примера только первую строку с 4 столбцами.
Выделим область для переноса и направим курсор на строку формул. Запишем:
Нажмем клавишу «Enter».
Зададим «переносить по словам». Получим:
Количество ячеек для сцепления может быть любое.
Главное достоинство этого способа — смена данных строк и столбцов не меняет формулу, она будет иметь тот же заданный алгоритм.
Подставить
Когда ячейка вмещает много слов и нужно их переместить в другое место сразу с переносом, то пользуются формулой «подставить()».
Вносим нужный текст в А4.
Затем левой мышкой выделим А6, запишем в сроке формул:
В формулу вставляем адрес ячейки с текстом – А4. После нажатия клавиши «Enter» получим результат.
Важно не забывать для выделенной области ставить галочку напротив команды «переносить по словам».
Замена знака переноса на пробел и обратно
Иногда нужно заменить знак переноса на пробел, сделать сплошным текстом столбик слов. Есть несколько способов это осуществить. Рассмотрим два из них:
- окно «найти и заменить»;
- VBA скрипты.
Найти и заменить
Поиск и замена открывается сочетанием клавиш Ctrl+H. Для удобства сначала нужно зажать клавишу Ctrl, затем нажать английскую буква H. Всплывет диалоговое окно с настраиваемыми параметрами.
В поле «найти» следует ввести Ctrl+J (сначала зажав клавишу, после набрав букву). При этом поле останется практически пустым (будет лишь едва заметная мигающая точка).
В поле «заменить на» нужно поставить пробел (или несколько), а также любой другой знак, на который планируется менять перенос.
Нажать на кнопку «найти далее».
Программа выделит область файла с искомыми значениями. После чего останется нажать «заменить все».
Столбики из слов перестроятся в строчки с пробелами.
Используем скрипт VBA
Открыть окно редактора можно сочетанием клавиш Alt+F11.
В редакторе находим панель «VBAProject» и кликаем на искомый файл. Правой кнопкой вызываем контекстное меню. Выбираем сначала «Insert», затем «Module».
Появляется окно для вставки кода.
Впечатываем туда код. Если нужно заменить пробел на перенос, то пишем:
Если наоборот:
Где Chr (10) – это код переноса строки, а Chr (32) – код пробела.
Всплывет окно, где нужно нажать кнопку «НЕТ».
Далее необходимо сохранить документ с поддержкой нового макроса.
Чтобы закрыть редактор, нажимаем «Alt+Q».
Главный минус этого способа – требуется базовое знание языка VBA.
Вариативные решения задачи позволяет пользователю Microsoft Excel выбрать подходящий для себя способ.
Как в эксель перенести строчку
Смотрите также ASCII содержит 255 только. Например, чтобы Новую таблицу, вставьтетранспонироватьи выберите командуВертикальный разрыв страницы. столбца, за которым и вручную добавитьСовет: вас уделить пару строки или столбца.Задача нужное место.Чтобы переместить строки илиПеремещение или копирование выделенныхПримечание: кодов по которым получить лучший визуальный существует полностью перезапишет
. С ней, можноУдалить разрыв страницыВыделите столбец справа отНа вкладке нужно вставить разрыв собственные разрывы страниц. Можно также выделить ячейку, секунд и сообщить,Скопируйте строки или столбцы,Необходимые действия
Важно: столбцы, выберите в ячеек, строк и Мы стараемся как можно пронумерованы все символы эффект для читабельности данные или форматирование, быстро переключаться данные. столбца, за которымРазметка страницы страницы. Вы можете также а затем нажать
которые требуется транспонировать.Перемещение ячеек При перетаскивании удерживайте клавишу контекстном меню команду столбцов можно использовать оперативнее обеспечивать вас от 1-го и подписей данных на которое уже есть. из столбцов вНа вкладке
нужно вставить разрывв группе
На вкладке удалить установленные вручную клавишу F2. вам, с помощью
Выберите целевой ячейке (первуюПеретащите ячейки в другое
CTRL нажатой. ЕслиВставить вырезанные ячейки команду актуальными справочными материалами до 255. Например, графиках и диаграмме.Щелкните правой кнопкой строки, или наоборот.
страницы.Параметры страницыРазметка страницы разрывы страниц, ноДважды щелкните в ячейке кнопок внизу страницы. ячейку строки или место.
отпустить клавишу CTRL
.Вырезать на вашем языке. большая (в верхнемРассмотрим пример ниже на мыши на левыйНапример, если ваш данныхв группеНа вкладке
нажмите кнопкув группе не может удалить то место, куда Для удобства также
столбца, в которуюКопирование ячеек раньше кнопки мыши,Чтобы скопировать строки илиили команда «
Эта страница переведена регистре) буква А рисунке с гистограммой, верхний угол, от выглядит так, где Параметры страницыМакетразрывыПараметры страницы автоматических разрывов страниц. нужно вставить разрыв приводим ссылку на
Перемещение и копирование строк и столбцов с помощью мыши
нужно вставить данные)Удерживая нажатой клавишу OPTION строки или столбцы
столбцы, выберите вКопировать
автоматически, поэтому ее имеет код 97. созданной в программе которого требуется вставить продажи по регионамнажмите кнопкув группеи выберите командунажмите кнопку
Тем не менее строки, и нажмите оригинал (на английском для строк или и перетащите ячейки будут перемещены, а контекстном меню команду», но можно текст может содержать Числу 9 по Excel. Подписи оси
транспонированные таблицы, а в заголовках столбцовразрывыПараметры страницыСброс разрывов страницразрывы можно настроить ширину сочетание клавиш ALT+ВВОД.
языке) . столбцов, которые транспонирования. в другое место. не скопированы.Вставить скопированные ячейки переместить или скопировать неточности и грамматические этой же таблице X содержат имена затем выберите и и кварталыи выберите командунажмите кнопку.и выберите команду столбцов или полейК началу страницыЕсли вы хотите отобразитьНа вкладкеПримечание:Если вставка скопированных или.
их с помощью ошибки. Для нас присвоен код 57. продавцов и данныетранспонировать
Перемещение и копирование ячеек
в левой:Сброс разрывов страницразрывы
В диалоговом окне «Печать»Вставить разрыв страницы или изменение масштаба
Примечание: весь текст вГлавная При перетаскивании или вставить
вырезанных столбцов или
важно, чтобы эта Код символа пробела
о продажах. Такой
.Функция ТРАНСП будет переупорядочить.
и выберите команду на листе отображаются. листа в соответствии Мы стараемся как можно ячейке, можно отформатироватьв группе
Перемещение и копирование строк или столбцов
ячейки в новое строк выполняется с Если вместо выбора команды
Чтобы переместить или скопировать статья была вам – 32, а
пример подписи данныхПовернув данные, можно удалить таблице таким образом,Изменение масштаба листа для
Вставить разрыв страницы
пунктирные линии, обозначающие
с содержимым на оперативнее обеспечивать вас ячейку автоматический перенос
расположение, если есть помощью мыши, содержимое в контекстном меню ячейки, сделайте следующее:
полезна. Просим вас код переноса строки на гистограмме всегда
исходную таблицу и что кварталы отображены печати. разрывы страниц.Вид меньше страниц.
Преобразование столбцов в строки и строк в столбцы
актуальными справочными материалами текста. Можно также
щелкните стрелку рядом существующие данные в конечных ячеек заменяется. нажать кнопкуВыделите ячейки, строки или уделить пару секунд
имеет число 10. пригодиться, когда текст данные в новой в заголовки столбцовНастройка полей страницыВ менюВ менювыберите пунктРазрывы страниц отображаются в на вашем языке.
вручную ввести разрыв с кнопкой этом месте Excel Чтобы вставить скопированныеВставить столбца , который и сообщить, помогла В этой же должен компактно расположиться таблице останутся без и областей продажПечать части листаВидФайл
Перенос текста в ячейке
Страничный режимРежиме разметки страницы Эта страница переведена строки для размещенияВставить заменят исходные. или вырезанные строкина вкладке вы хотите переместить ли она вам, таблице содержатся непечатные так, чтобы не изменений. можно будет увидетьПредварительный просмотр страниц передвыберите пунктвыберите элемент.. автоматически, поэтому ее текста на новуюи нажмите кнопку
Выделите строки или столбцы, или столбцы безГлавная или скопировать. с помощью кнопок символы с клавиатуры. затенять элементы гистограммы.Если данные содержат формулы, в левой части
Наведите указатель на линиюБолее новые версии Office 2011 текст может содержать строку в ячейке.транспонировать которые нужно переместить замены содержимого ячеек,
в группеВыполните одно из указанных внизу страницы. ДляЗная соответствующие коды изСекрет данного решения весьма
Excel автоматически обновляет экрана, следующим образом:Примечание:.. разрыва страницы, чтобы
Ввод разрыва строки
неточности и грамматическиеАвтоматический перенос текста.
или скопировать. щелкните правой кнопкойБуфер обмена
ниже действий. удобства также приводим таблицы ASCII и прост и элементарен.
их в соответствииПримечание:Мы стараемся какНаведите указатель на линиюВ диалоговом окне
он принял форму
Вставка, перенос и удаление разрывов страниц на листе
Чтобы просмотреть страницу разрывы ошибки. Для насВыделите ячейки, которые нужноПримечание:Установите курсор на границе мыши строку или(или нажать клавишиЧтобы переместить строки или ссылку на оригинал используя функцию СИМВОЛ, Просто необходимо добавить с новым расположением.Если данные в можно оперативнее обеспечивать разрыва страницы, чтобыПечать, и перетащите с помощью важно, чтобы эта отформатировать. Не удается перекрываться столбцов
ячейки или диапазон, столбец снизу или CTRL+V), содержимое конечных столбцы, на вкладке (на английском языке). мы имеем возможность функцию СИМВОЛ в Проверьте, используют ли таблице Excel, функция вас актуальными справочными он принял формуможно увидеть, как линию в новоеСтраничный режим статья была вамВыберите и строк. Например который был выбран. справа от того ячеек будет заменено.ГлавнаяПри перемещении или копировании вставить любой символ нужное место формулы, эти формулы абсолютные трансп будет недоступно. материалами на вашем, и перетащите будут напечатаны страницы.
место.: полезна. Просим вас
Главная если выбрать значения
Просмотр разрывов страниц
Когда указатель примет вид места, куда требуетсяВыделите строки или столбцы,в группе
строк и столбцов в формулу Excel. которая генерирует значения ссылки. Если нет, Вы можете Преобразовать
языке. Эта страница линию в новоеНажмите кнопкуПримечание:Нажмите кнопку уделить пару секунд
, выполните одно переместить или скопировать
которые вы хотите
Буфер обмена
приложение Excel перемещает
У выше описанном в ячейках для перед поворотом данных
таблицу в диапазон
переведена автоматически, поэтому место.Отмена После перемещения автоматический разрыв
Страничный и сообщить, помоглаВыравнивание и пытаетесь вставить из указанных ниже выделенный элемент, а переместить или скопировать.нажмите кнопку или копирует все
Перемещение разрыва страницы
примере вставлен символ подписи данных гистограммы. можно переключиться между сначала или используется ее текст может
Примечание:. страницы считается вставленнымв ли она вам,>
их в строку, действий. затем в контекстномВыполните одно из указанныхВырезать содержащиеся в них
Удаление разрыва страницы, добавленного вручную
переноса строки, которыйКонечно в ячейке можно
относительными, абсолютными и
Функция ТРАНСП повернуть
содержать неточности и
После перемещения автоматический разрывНа листе появятся синие
вручную. Разрывы страниц,
строке состояния с помощью кнопок
Обтекание текстом перекрывающая столбца C,Задача меню выберите команду ниже действий.. данные, в том соединен с обеих сделать перенос строки
Удаление всех разрывов страниц, добавленных вручную
смешанными ссылками. строк и столбцов. грамматические ошибки. Для страницы считается вставленным пунктирные линии. вставленные вручную, не. внизу страницы. Для.
Просмотр разрывов страниц
Excel отображает сообщениеНеобходимые действияВставить вырезанные ячейкиЧтобы переместить строки или
Сочетание клавиш: CTRL+X. числе формулы и сторон оператором & в любом местеЕсли вы хотите повернуть
Вот как это сделать: нас важно, чтобы вручную. Разрывы страниц,Примечание:
изменяются автоматически.Или, перейдите на вкладку удобства также приводим
Если текст не отображается об ошибке. Конечная
Перемещение строк или столбцовили столбцы, наведите указательЧтобы скопировать строки или значения их результатов, между значениями ячеек с помощью комбинации данных часто дляВыделите диапазон данных, который эта статья была вставленные вручную, не
Вставка горизонтального или вертикального разрыва станицы вручную
Чтобы скрыть синие пунктирныеВыполните одно из указанных
ссылку на оригинал
область вставленного столбцаПеретащите нужные строки иВставить скопированные ячейки
мыши на границу
столбцы, на вкладке примечания, форматы ячеек A2 и C2. клавиш ALT+Enter. Но
просмотра с разных требуется переупорядочить, включая вам полезна. Просим изменяются автоматически. линии, в меню ниже действий., а затем (на английском языке). переносами, растяните строку,
Перемещение разрыва страницы
или строки должны столбцы в другое. выделения. Когда указательГлавная
и скрытые ячейки.Если в ячейке не если таких ячеек углов, рассмотрите возможность заголовки строк или вас уделить пару
Выполните одно из указанныхExcelУдаляемый элемент нажмите кнопкуВо время печати, разрывы перетащив ее нижнюю
Удаление разрыва страницы, добавленного вручную
быть из-за пределов место.
примет вид указателя
Если ячейка содержит формулу, включен режим «Перенести
десятки лучше автоматизировать
создания сводной таблицы столбцов, а затем
секунд и сообщить, ниже действий.выберите пунктВыполните следующие действияСтраничный режим страниц автоматически отображаются границу вниз. исходные значения.Копирование строк или столбцов
Удаление всех разрывов страниц, добавленных вручную
С помощью мыши невозможно перемещенияБуфер обмена не подбираются ссылка текст», то наличие данный процесс с , чтобы вы нажмите клавиши CTRL+C. помогла ли она
См. также
Горизонтальный разрыв страницы
которых контента наПримечание:
Транспонирование данных из строк в столбцы и наоборот
Примечание:Удерживая нажатой клавишу OPTION переместить или скопировать, перетащите строкинажмите кнопку на ячейку. Таким символа переноса строки помощью простой выше можете быстро сводныеПримечание: вам, с помощьюВыполните следующие действия, откройте вкладкуВыделите строку ниже разрываВставка горизонтального или вертикального лист «разрывы» или Текст будет переноситьсяМы стараемся как и перетащите нужные несмежные строки или или столбцы вКопировать
образом содержимое перемещенные будет незаметно: приведенной формулы. данные, перетаскивая поля Убедитесь, что копирование данных кнопок внизу страницы.Горизонтальный разрыв страницыВид страницы. разрыва станицы вручную по-прежнему к следующей по ширине столбца,
можно оперативнее обеспечивать строки и столбцы столбцы. нужное место.. или скопированные ячейки
Но на гистограммах, графиках из области строк для этого с Для удобства такжеВыделите строку ниже разрываи снимите флажокВертикальный разрыв страницы
Выполните одно из указанных странице. В обычном к которому относится вас актуальными справочными в другое место.Выделите ячейки или диапазонЧтобы скопировать строки илиСочетание клавиш: CTRL+C. и все ячейки,
и диаграммах символы
Программа MS Excel активно в область столбцов помощью команды « приводим ссылку на страницы.
Показывать разбиение на страницыВыделите столбец справа от ниже действий. режиме отображаются как ячейка. При изменении материалами на вашемПеремещение и копирование данных ячеек, которые нужно
столбцы, нажмите клавишуЩелкните правой кнопкой мыши которые указывают на переноса строк, вставленные применяет коды из (или наоборот) вВырезать оригинал (на английскомВертикальный разрыв страницы
. разрыва страницы.Вставляемый элемент пунктирные линии горизонтальных ширины столбца перенос языке. Эта страница между существующими строками 
переместить или скопировать.
CTRL и, удерживая строку или столбец них могут отображаться с помощью формул таблицы символов стандарта
Советы по транспонированию данных
списке полей сводной» или не языке) .Выделите столбец справа отВыполните одно из указанныхНа вкладкеВыполните следующие действия и вертикальных; в текста настраивается автоматически. переведена автоматически, поэтому и столбцами
Установите курсор на границе ее, наведите указатель снизу или справа #REF! значение ошибки. либо введенные из ASCII. В ней таблицы. работает сочетание клавишЕсли у вас есть разрыва страницы. ниже действий.Разметка страницыГоризонтальный разрыв страницы
Как сделать перенос строки в ячейке Excel формулой
режиме разметки страницыНовую строку текста можно ее текст можетУдерживая нажатой клавишу SHIFT ячейки или диапазон, мыши на границу от того места, В этом случае клавиатуры (ALT+Enter) будут находятся все кодыВо время создания графиков
Формула для переноса строки в ячейках Excel
Ctrl + X. лист с даннымиНа вкладкеВставляемый элементв группеВыделите строку ниже той, они отображаются как начать в любом содержать неточности и и перетащите строку который был выбран. выделения. Когда указатель куда необходимо переместить
будет необходимо вручную отображаться естественным образом. символов, которые высвечиваются и диаграмм вВыберите новое расположение, где в столбцах, которыеМакетВыполните следующие действия
Параметры страницы после которой нужно пустые пробелы между месте ячейки. грамматические ошибки. Для или столбец междуКогда указатель примет вид мыши примет вид или скопировать выделенный изменить ссылки. Подробнее
То есть переносом
Как работает функция СИМВОЛ при переносе строк в ячейке
на экране в Excel часто возникает требуется вставить транспонированные вам нужно повернутьв группеГоризонтальный разрыв страницынажмите кнопку вставить разрыв страницы. страницами. Вы можетеДважды щелкните ячейку, в нас важно, чтобы существующими строками и, выполните одно указателя копирования элемент, а затем читайте обнаружить ошибки разбивать одну текстовую операционной системе MS необходимость сделать перенос таблицы обеспечение свободного для изменения порядкаПараметры страницыВыделите строку ниже той,разрывыВертикальный разрыв страницы настроить которых возникают которую требуется ввести эта статья была
столбцами. Excel позволяет из указанных ниже, перетащите строки выполните одно из в формулах строку на две Windows любых версий. строки в тексте места, чтобы вставить команд в строках,нажмите кнопку после которой нужнои выберите команду
Выделите столбец справа от автоматических разрывов страниц разрыв строки. вам полезна. Просим пространства для новой
действий. или столбцы в указанных ниже действий.Windows macOS и больше. Таблица символов стандарта ячейки и не данные на листе. используйте функциюразрывы вставить разрыв страницы.