Как перенести часть текста из одной ячейки в другую excel
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее.
Удаление переносов строк заменой
Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:
Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .
Удаление переносов строк формулой
Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:
Такой вариант, однако, не всегда бывает удобен, т.к. строки после этой операции могут склеиваться между собой. Чтобы этого не происходило, нужно не просто удалять символ переноса, а заменять его на пробел (см. следующий пункт).
Замена переносов строк формулой
А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:
Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.
Деление на столбцы по переносу строки
Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :
Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .
После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:
Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.
Деление на строки по Alt+Enter через Power Query
Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:
Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :
Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.
Останется только нажать на ОК и получить желаемое:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .
Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Макрос для деления на строки по Alt+Enter
Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)
Нашел на форуме тему с вопросом, похожим на мой - позаимствовал оттуда формулу. Она подошла, но не идеально - нужно донастроить.
Описание моего случая:
В ячейках в столбце А у меня находятся описания книг, как правило, состоящие из нескольких абзацев: цитата, о чем книга, кто автор. В столбец В мне надо переместить только информацию об авторе. Она не всегда есть в исходном столбце (в этом случае необходимо оставить соответствующую ячейку в столбце В пустой), но когда есть, то имеет постоянные признаки:
1. Абзац, который требуется перенести, всегда начинается с заголовка "Кто автор" (сам заголовок переносить не нужно);
2. Абзац с нужными сведениями числится последним в ячейке (что упрощает задачу составления формулы).
Задача:
Перенести текст со слов "Кто автор" из одной ячейки в другую. Если искомое значение в исходной ячейке отсутствует, то заполняемую ячейку необходимо оставить пустой.
Файл с примером прилагаю.
Нашел на форуме тему с вопросом, похожим на мой - позаимствовал оттуда формулу. Она подошла, но не идеально - нужно донастроить.
Описание моего случая:
В ячейках в столбце А у меня находятся описания книг, как правило, состоящие из нескольких абзацев: цитата, о чем книга, кто автор. В столбец В мне надо переместить только информацию об авторе. Она не всегда есть в исходном столбце (в этом случае необходимо оставить соответствующую ячейку в столбце В пустой), но когда есть, то имеет постоянные признаки:
1. Абзац, который требуется перенести, всегда начинается с заголовка "Кто автор" (сам заголовок переносить не нужно);
2. Абзац с нужными сведениями числится последним в ячейке (что упрощает задачу составления формулы).
Задача:
Перенести текст со слов "Кто автор" из одной ячейки в другую. Если искомое значение в исходной ячейке отсутствует, то заполняемую ячейку необходимо оставить пустой.
Файл с примером прилагаю.
Нашел на форуме тему с вопросом, похожим на мой - позаимствовал оттуда формулу. Она подошла, но не идеально - нужно донастроить.
Описание моего случая:
В ячейках в столбце А у меня находятся описания книг, как правило, состоящие из нескольких абзацев: цитата, о чем книга, кто автор. В столбец В мне надо переместить только информацию об авторе. Она не всегда есть в исходном столбце (в этом случае необходимо оставить соответствующую ячейку в столбце В пустой), но когда есть, то имеет постоянные признаки:
1. Абзац, который требуется перенести, всегда начинается с заголовка "Кто автор" (сам заголовок переносить не нужно);
2. Абзац с нужными сведениями числится последним в ячейке (что упрощает задачу составления формулы).
Задача:
Перенести текст со слов "Кто автор" из одной ячейки в другую. Если искомое значение в исходной ячейке отсутствует, то заполняемую ячейку необходимо оставить пустой.
Файл с примером прилагаю.
Прошу помочь. Автор - User799
Дата добавления - 08.03.2021 в 14:51
Добрый день! подскажите пожалуйста с помощью каких функций и как перенести часть данных которая написана до слова БО в другую ячейку?
Как перенести часть данных из ячейки эксель в другую ячейку
Просьба помочь! нужно перенести из одной ячейки: UAH:384, USD:15, RUB:1067, EUR:13 только 1067.
Часть символов с конца строки до первого пробела перенести в другую ячейку
Добрый день! Помогите, пожалуйста, решить такую задачу: Есть строка, в которой прописан адрес.
Как перенести часть данных из одной ячейки в другую
Необходимо перенести два числа в другую ячейку из ячейки, в которой данные состоят из цифр и букв
SQL как правильно перенести часть данных из одной таблицы в другую?
Есть таблица id | x | y 1 | 10| 20 2 | 6 | 18 3 | 17| 4 Я создаю вторую таблицу, куда мне.
Спасибо! если несложно можете пожалуйста на примере показать как перенести название организации и дату в левые ячейки ?
поиск ищет два символа("БО") и когда находит принимает значение первого, а не последнего ("Б"). Избавляемся от первого значение (-1) и от пробела перед БО (-1) итого -2.
А если "БО" встречается не на каждой строке выдает ошибку.
Добавлено через 7 минут
Кто подскажет как оБОйти ошибку, чтобы формула работала для строк с "БО" и переносила все данные из ячейки, если "БО" не встречается
=ЕСЛИ(еошибка(формула);что то другое;формула)
Добавлено через 5 минут
В примере несколько строк, фильтр остается из прежних условий "бо". Требуется из столбца А перенести в столбец Б слова или части слов
Скажите, а нормально плодить таких лошадей:
если нужно найти цвет в строке и оставить только его?
как-нибудь попроще это можно сделать?
Перенести значение ячейки в другую ячейку
НУЖЕН МУДРЫЙ ХЕЛП. Нужно перенести значение ячейки в другую ячейку(функцией) так, что бы.
Необходимо перенести значение в другую ячейку
Здравствуйте! Помогите пож-та, задача следующая: необходимо перенести значение (к примеру (в.
Вытащить из текста дату и перенести в другую ячейку
Доброго! Помогите, пожалуйста, второй день бьюсь с данной проблемой. Из столбца "Биография" нужно.
Перенести слово в другую книгу в определенную ячейку
Есть две excel файла. В первом файле, таблица диапазоном 100х100 в каждом ячейке одно слово. Во.
Подскажите, как вырезать часть текста и переместить в другую ячейку.
Искомый текст:
Иванов Иван Иванович 13/06/1999
Сидоров Николай Александрович 22/12/2000
Данные даты рождения нужно переместить в другую ячейку
Иванов Иван Иванович 13.06.1999
Сидоров Николай Александрович 22.12.2000
Спасибо
Переместить часть данных в другую таблицу
Доброго времени суток! Помогите с решением. Есть таблиц а в EXCEL с перечнем элементов. При.
Вырезать и заменить часть текста в строке Exel
Люди добрые)подскажите новичку как вырезать из строчки данные которые находятся между @ и ;;; и.
Перенести часть данных до определенного слова в другую ячейку
Добрый день! подскажите пожалуйста с помощью каких функций и как перенести часть данных которая.
Как перенести часть данных из ячейки эксель в другую ячейку
Просьба помочь! нужно перенести из одной ячейки: UAH:384, USD:15, RUB:1067, EUR:13 только 1067.
Если вопреки файлу "Задача1.xlsx" нужно перенести только даты, то :
копируем исходный столбец и вставляем рядом, затем Данные, Текст по столбцам, выбираем с разделителями, далее, затем выбираем только один разделитель пробел, далее, пропускаем первые три столбца (т.е. выделяем и пропустить столбец), для четвёртого устанавливаем формат дата (4й столбец можно не трогать) и Готово
Bostrikov, Вы сейчас к кому обращаетесь? Если ко мне, то я заметил, что в файле "Задача1.xlsx" не просто переносится дата, а столбец разделяется на фио и дату.
ФИО можно перенести в столбец С другой формулой, если не устраивает тогда макрос, но это другая ветка форума.
Часть символов с конца строки до первого пробела перенести в другую ячейку
Добрый день! Помогите, пожалуйста, решить такую задачу: Есть строка, в которой прописан адрес.
Вытащить из текста дату и перенести в другую ячейку
Доброго! Помогите, пожалуйста, второй день бьюсь с данной проблемой. Из столбца "Биография" нужно.
Перенос текста, если не хватает места в ячейке на другую ячейку
Ребят, выручайте, нужно сделать перенос текста в другую ячейку, если первая закончилась. Нашла.
После ввода текста и перехода на другую ячейку excel вылетает
win 7, office 2013. При попытке создать новый документ, после ввода текста и перехода на другую.
Вы можете скопировать и вкопировать определенное содержимое ячейки или атрибуты (например, формулы, форматы, при комментарии и проверку). По умолчанию при использовании значков Копировать и (или + C и +V) копируется все атрибуты. Чтобы выбрать определенный параметр вставки, можно воспользоваться меню Вставить либо выбрать команду Специальная вставка и указать нужный вариант в окне Специальная вставка. В этом случае при вставке содержимого исключаются все атрибуты, кроме выбранных.
Пункты меню "Вставить"
Выделите ячейки с данными и атрибутами, которые требуется скопировать.
На вкладке Главная нажмите кнопку Копировать .
Щелкните первую ячейку в области, куда требуется вставить скопированное содержимое.
На вкладке Главная щелкните стрелку рядом с кнопкой Вироватьи сделайте следующее. Содержимое меню Вставить зависит от типа данных в выбранных ячейках.
Что вставляется
Все содержимое и формат ячеек, включая связанные данные.
Формулы и форматы чисел
Только формулы и форматы чисел.
Сохранить исходное форматирование
Все содержимое и формат ячеек.
Все содержимое и формат ячеек, кроме границ ячеек.
Сохранить ширину столбцов оригинала
Только атрибуты ширины столбцов.
Транспонировать
Вставка содержимого скопированных ячеек с изменением ориентации. Данные строк будут вставлены в столбцы, и наоборот.
Вставить значения
Только значения в том виде, как они отображаются в ячейках.
Значения и форматы чисел
Только значения и форматы чисел.
Значения и исходное форматирование
Только значения, цвет и размер шрифта.
Форматирование
Все атрибуты форматирования ячеек, включая форматы чисел и исходное форматирование.
Вставить связь
Вставляемые значения связываются с исходными. При вставке связи в копируемые данные Excel вводит абсолютную ссылку на копируемую ячейку или диапазон ячеек в новом месте.
Вставить как рисунок
Скопированные данные как изображение.
Связанный рисунок
Скопированные данные как изображение со ссылкой на исходные ячейки (изменения, внесенные в исходных ячейках, отражаются и в ячейках, куда вставлено изображение).
ширины столбцов
Вставка ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.
Объединить условное форматирование
Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.
Параметры специальной вставки
Выделите ячейки с данными и атрибутами, которые требуется скопировать.
На вкладке Главная нажмите кнопку Копировать .
Щелкните первую ячейку в области, куда требуется вставить скопированное содержимое.
На вкладке Главная щелкните стрелку рядом с кнопкой В таблицуи выберите специальную ветвь.
Выберите нужные параметры.
Параметры вставки
Что вставляется
Все содержимое и формат ячеек, включая связанные данные.
Вставка только значений в том виде, как они отображаются в ячейках.
Содержимое и формат ячеек.
Вставка только примечаний к ячейкам.
Только правила проверки данных.
с исходной темой
Все содержимое и форматирование ячеек с использованием темы, примененной к исходным данным.
Содержимое и формат ячеек, кроме границ ячеек.
ширины столбцов
Атрибуты ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.
формулы и форматы чисел
Только формулы и форматы чисел.
значения и форматы чисел
Только значения и форматы чисел из выделенных ячеек.
все, объединить условное форматирование
Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.
Параметры операций
Параметры операций позволяют выполнить математические действия со значениями из областей копирования и вставки.
Вставка содержимого скопированной области без математического действия.
Добавление значений из области копирования к значениям в области вставки.
Вычитание значений из области копирования из значений в области вставки.
Умножение значений из области вставки на значения из области копирования.
Деление значений из области вставки на значения из области копирования.
Доступны и другие параметры:
Пропускать пустые ячейки
Позволяет предотвратить замену значений и атрибутов в области вставки, когда в скопированной области содержатся пустые ячейки.
Транспонировать
Вставка содержимого скопированных ячеек с изменением ориентации. Данные строк будут вставлены в столбцы, и наоборот.
Вставить связь
Если данные представляют собой рисунок, он связывается с исходным рисунком. В случае изменения исходного рисунка вставленный также меняется.
Совет: Некоторые параметры доступны как в меню В paste, так и в диалоговом окне Специальная ветвь. Их названия могут слегка различаться, но результат оказывается одинаковым.
Выделите ячейки с данными и атрибутами, которые требуется скопировать.
На панели инструментов Стандартная нажмите кнопку Копировать .
Щелкните первую ячейку в области, куда требуется вставить скопированное содержимое.
На вкладке Главная в области Правканажмите кнопку В таблицуи выберите специальную ветвь.
В диалоговом окке Специальная ветвь в области Ветвьсделайте следующее:
В этом формате включаем все содержимое и формат ячейки, включая связанные данные.
Вставка только формул в том виде, как они вводятся в строку формул.
В этом окте можно вировать только значения, отображаемые в ячейках.
В этом формате можно в нее в форматировать только ячейки.
Вставка только примечаний к ячейкам.
В этой области можно вировать правила проверки данных для скопированные ячейки.
с исходной темой
В этом формате можно в виде темы, примененной к исходным данным, в виде вложенного содержимого и форматирования ячейки.
В этом формате можно в нее в форматировать все содержимое и содержимое ячейки, кроме границ ячеев.
ширины столбцов
Вставка ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.
формулы и форматы чисел
В этом формате можно вировать только формулы и параметры форматирования для выбранных ячеек.
значения и форматы чисел
В этом формате можно вировать только значения и параметры форматирования из выбранных ячеек.
Объединить условное форматирование
Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.
Чтобы математически объединить значения между областями копирования и вжатия, в диалоговом окне Специальная вация в области Операция выберите математическую операцию, которую вы хотите применить к скопированные данные.
Вставка содержимого скопированной области без математического действия.
Добавление значений из области копирования к значениям в области вставки.
Вычитание значений из области копирования из значений в области вставки.
Умножение значений из области вставки на значения из области копирования.
Деление значений из области вставки на значения из области копирования.
Дополнительные параметры определяют, как будут обрабатываться пустые ячейки при вклеии, как в них вся скопированная информация, как строки или столбцы, и связывание скопированные данные.
Пропуск пустых страниц
Избегайте замены значений в области в области в виде висячего в том случае, если пустые ячейки встречаются в области копирования.
Транспонировать
Измените столбцы скопированные данные на строки и наоборот.
Вставить связь
Вставляемые значения связываются с исходными. При вставке связи в копируемые данные Excel вводит абсолютную ссылку на копируемую ячейку или диапазон ячеек в новом месте.
Примечание: Этот параметр доступен только при выборе параметра Все или Все, кроме границ в диалоговом окне "Специальная ветвь"
Совет: В Excel для Mac версии 16.33 или более высокой версии на панель быстрого доступа можно добавить действия "Вставку форматирования", "Вставку формул" и "Вставку значений" или пользовательские сочетания клавиш. Для сочетаний клавиш необходимо назначить сочетание клавиш, которое еще не используется для открытия диалогового окно Специальная вставку.
Читайте также: