Remkomplekty.ru

IT Новости из мира ПК
67 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel перенос строки в ячейке

Статья Как в Excel сделать перенос текста в ячейке

Dragokas

Very kind Developer

Часто требуется внутри одной ячейки Excel сделать перенос текста на новую строку. То есть переместить текст по строкам внутри одной ячейки как указано на картинке. Если после ввода первой части текста просто нажать на клавишу ENTER, то курсор будет перенесен на следующую строку, но другую ячейку, а нам требуется перенос в этой же ячейке.

Это очень частая задача и решается она очень просто — для переноса текста на новую строку внутри одной ячейки Excel необходимо нажать ALT+ENTER (зажимаете клавишу ALT, затем не отпуская ее, нажимаете клавишу ENTER)

Как перенести текст на новую строку в Excel с помощью формулы

Иногда требуется сделать перенос строки не разово, а с помощью функций в Excel. Вот как в этом примере на рисунке. Мы вводим имя, фамилию и отчество и оно автоматически собирается в ячейке A6

Для начала нам необходимо сцепить текст в ячейках A1 и B1 (A1&B1), A2 и B2 (A2&B2), A3 и B3 (A3&B3)

После этого объединим все эти пары, но так же нам необходимо между этими парами поставить символ (код) переноса строки. Есть специальная таблица знаков (таблица есть в конце данной статьи), которые можно вывести в Excel с помощью специальной функции СИМВОЛ(число), где число это число от 1 до 255, определяющее определенный знак.
Например, если прописать =СИМВОЛ(169), то мы получим знак копирайта ©

Нам же требуется знак переноса строки, он соответствует порядковому номеру 10 — это надо запомнить.
Код (символ) переноса строки — 10
Следовательно перенос строки в Excel в виде функции будет выглядеть вот так СИМВОЛ(10)

Примечание: В VBA Excel перенос строки вводится с помощью функции Chr и выглядит как Chr(10)

Итак, в ячейке A6 пропишем формулу

В итоге мы должны получить нужный нам результат.
Обратите внимание! Чтобы перенос строки корректно отображался необходимо включить «перенос по строкам» в свойствах ячейки.
Для этого выделите нужную нам ячейку (ячейки), нажмите на правую кнопку мыши и выберите «Формат ячеек. »

В открывшемся окне во вкладке «Выравнивание» необходимо поставить галочку напротив «Переносить по словам» как указано на картинке, иначе перенос строк в Excel не будет корректно отображаться с помощью формул.

Как в Excel заменить знак переноса на другой символ и обратно с помощью формулы

Можно поменять символ перенос на любой другой знак, например на пробел, с помощью текстовой функции ПОДСТАВИТЬ в Excel

Рассмотрим на примере, что на картинке выше. Итак, в ячейке B1 прописываем функцию ПОДСТАВИТЬ:

A1 — это наш текст с переносом строки;
СИМВОЛ(10) — это перенос строки (мы рассматривали это чуть выше в данной статье);
» » — это пробел, так как мы меняем перенос строки на пробел

Если нужно проделать обратную операцию — поменять пробел на знак (символ) переноса, то функция будет выглядеть соответственно:

Напоминаю, чтобы перенос строк правильно отражался, необходимо в свойствах ячеек, в разделе «Выравнивание» указать «Переносить по строкам».

Как поменять знак переноса на пробел и обратно в Excel с помощью ПОИСК — ЗАМЕНА

Бывают случаи, когда формулы использовать неудобно и требуется сделать замену быстро. Для этого воспользуемся Поиском и Заменой. Выделяем наш текст и нажимаем CTRL+H, появится следующее окно.

Если нам необходимо поменять перенос строки на пробел, то в строке «Найти» необходимо ввести перенос строки, для этого встаньте в поле «Найти», затем нажмите на клавишу ALT, не отпуская ее наберите на клавиатуре 010 — это код переноса строки, он не будет виден в данном поле.

После этого в поле «Заменить на» введите пробел или любой другой символ на который вам необходимо поменять и нажмите «Заменить» или «Заменить все».

Кстати, в Word это реализовано более наглядно.

Если вам необходимо поменять символ переноса строки на пробел, то в поле «Найти» вам необходимо указать специальный код «Разрыва строки», который обозначается как ^l
В поле «Заменить на:» необходимо сделать просто пробел и нажать на «Заменить» или «Заменить все».

Вы можете менять не только перенос строки, но и другие специальные символы, чтобы получить их соответствующий код, необходимо нажать на кнопку «Больше >>», «Специальные» и выбрать необходимый вам код. Напоминаю, что данная функция есть только в Word, в Excel эти символы не будут работать.

Как поменять перенос строки на пробел или наоборот в Excel с помощью VBA

Рассмотрим пример для выделенных ячеек. То есть мы выделяем требуемые ячейки и запускаем макрос

1. Меняем пробелы на переносы в выделенных ячейках с помощью VBA

Перенос строки в пределах ячейки в Microsoft Excel

Как известно, по умолчанию в одной ячейке листа Excel располагается одна строка с числами, текстом или другими данными. Но, что делать, если нужно перенести текст в пределах одной ячейки на другую строку? Данную задачу можно выполнить, воспользовавшись некоторыми возможностями программы. Давайте разберемся, как сделать перевод строки в ячейке в Excel.

Читать еще:  Vba excel if then else

Способы переноса текста

Некоторые пользователи пытаются перенести текст внутри ячейки нажатием на клавиатуре кнопки Enter. Но этим они добиваются только того, что курсор перемещается на следующую строку листа. Мы же рассмотрим варианты переноса именно внутри ячейки, как очень простые, так и более сложные.

Способ 1: использование клавиатуры

Самый простой вариант переноса на другую строку, это установить курсор перед тем отрезком, который нужно перенести, а затем набрать на клавиатуре сочетание клавиш Alt+Enter.

В отличие от использования только одной кнопки Enter, с помощью этого способа будет достигнут именно такой результат, который ставится.

Способ 2: форматирование

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

    Выделяем ячейку, в которой текст выходит за пределы границ. Кликаем по ней правой кнопкой мыши. В открывшемся списке выбираем пункт «Формат ячеек…».

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

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

    Способ 3: использование формулы

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

    1. Отформатируйте ячейку, как указано в предыдущем варианте.
    2. Выделите ячейку и введите в неё или в строку формул следующее выражение:

    Вместо элементов «ТЕКСТ1» и «ТЕКСТ2» нужно подставить слова или наборы слов, которые хотите перенести. Остальные символы формулы изменять не нужно.

  • Для того, чтобы результат отобразился на листе, нажмите кнопку Enter на клавиатуре.
  • Главным недостатком данного способа является тот факт, что он сложнее в выполнении, чем предыдущие варианты.

    В целом пользователь должен сам решить, каким из предложенных способов оптимальнее воспользоваться в конкретном случае. Если вы хотите только, чтобы все символы вмещались в границы ячейки, то просто отформатируйте её нужным образом, а лучше всего отформатировать весь диапазон. Если вы хотите устроить перенос конкретных слов, то наберите соответствующее сочетание клавиш, как рассказано в описании первого способа. Третий вариант рекомендуется использовать только тогда, когда данные подтягиваются из других диапазонов с помощью формулы. В остальных случаях использование данного способа является нерациональным, так как имеются гораздо более простые варианты решения поставленной задачи.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Перенос строк в одной ячейке

    Работая с таблицами в Microsoft Excel, пользователи сталкиваются с самыми разными проблемами и ситуациями. Бывает, что приходится работать с блоком текста, находясь в одной области таблицы. К сожалению, у многих пользователей такая задача вызывает определённые трудности. В этой статье разберёмся как в Excel сделать перенос текста в ячейке. Давайте же начнём. Поехали!

    Как известно, даже находясь в одной ячейке, нажав клавишу Enter, вы перенесёте курсор уже на следующую строку таблицы, что является неприемлемым в нашем случае. Подобная задача возникает довольно часто и решается проще, чем думают большинство пользователей. Чтобы перенести текст в одной ячейке, воспользуйтесь комбинацией клавиш Alt+Enter. Такой способ хорош в том случае, если вам нужно выполнить операцию один раз или несколько.

    Вы можете сделать то же самое при помощи функций Экселя. Использование функций позволяет существенно облегчить выполнение большинства задач в программе. Например, ввести в отдельных ячейках контактные данные (телефон, email, домашний адрес), чтобы они автоматически построчно собирались в одной ячейке.

    Сперва сцепите области текста («телефон:» + «12345678»; «email:» + «abcde@mail.com»; «Адрес:» + «ул.А, дом. 27-Г»). Делается это путём добавления знака «&» (амперсанд) между адресами ячеек.

    Теперь необходимо составить формулу, которая должна иметь следующий вид: «=B1&C1&СИМВОЛ(10)&B2&C2&СИМВОЛ(10)&B3&C3».

    СИМВОЛ(10) — это перенос строки, который добавляется после каждой пары связанных областей.

    В том случае, если перенесённый текст отображается некорректно, щёлкните правой кнопкой мыши по нужной области и в появившемся меню выберите пункт «Формат ячеек». В открывшемся окне перейдите на вкладку «Выравнивание». В разделе «Отображение» отметьте птичкой пункт «переносить по словам».

    Читать еще:  Rows count vba excel описание

    Если необходимо написанные в столбик слова перенести в ячейку так, чтобы они стояли в одну строку через пробел, напишите следующую формулу в специальном поле для функций: «=ПОДСТАВИТЬ(B2);СИМВОЛ(10);” “».

    Чтобы выполнить операцию в обратную сторону, поменяйте в формуле местами «СИМВОЛ(10)» и пробел (” “).

    Существует способ, который позволяет не прибегать к использованию формул. Сперва нужно выделить текст и нажать комбинацию клавиш Ctrl+H. В появившемся окне в поле «Найти» введите 010 держа нажатой клавишу Alt. Обратите внимание, что во введённый вами код переноса строки отображаться не будет. Далее, в области «Заменить на» введите тот символ, на который вы хотели бы заменить знак переноса строки, в нашем случае это пробел (” “). Вы можете найти всевозможные специальные знаки и символы, которые отсутствуют на клавиатуре, в специальной таблице Экселя.

    Для тех, кто использует VBA (Visual Basic) в Excel, также будет актуален следующий способ. Отметив необходимые области, вызовите окно макросов. Затем, в зависимости от того, что нужно сделать пропишите одни или другие команды, как показано на скриншоте ниже:

    Для удобства копирования команды тоже самое текстом ниже:

    1. Меняем пробелы на переносы в выделенных ячейках с помощью VBS:

    For Each cell In Selection

    cell.Value = Replace (cell.Value, Chr (32), Chr (10))

    2. Меняем переносы на пробелы на в выделенных ячейках с помощью VBS:

    For Each cell In Selection

    cell.Value = Replace (cell.Value, Chr (10), Chr (32))

    Где Chr(10) и Chr(32) — коды переноса строки и пробела соответственно.

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

    Как удалить переносы строк (возвраты каретки) из ячеек в Excel 2013, 2010 и 2007

    Эта инструкция познакомит Вас с тремя способами удалить возвраты каретки из ячеек в Excel. Вы также узнаете, как заменить символы переноса строки другими символами. Все предложенные решения работают в Excel 2013, 2010, 2007 и 2003.

    Переносы строк могут появиться в тексте по разным причинам. Обычно возвраты каретки встречаются в рабочей книге, например, когда текст скопирован с веб-страницы, когда они уже есть в рабочей книге, полученной от клиента, или, когда мы сами добавляем их нажатием клавиш Alt+Enter.

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

    Все три представленных способа довольно быстры. Выбирайте тот, который Вам больше подходит:

    Замечание: Первоначально термины “Возврат каретки” и “Перевод строки” использовались при работе на печатных машинках и обозначали две различных операции. Любознательный читатель может самостоятельно найти подробную информацию об этом в интернете.

    Компьютеры и программное обеспечение для работы с текстами разрабатывались с учётом особенностей печатных машинок. Вот почему теперь для обозначения разрыва строки используют два различных непечатаемых символа: Возврат каретки (Carriage return, CR или ASCII код 13) и Перевод строки (Line feed, LF или ASCII код 10). В Windows используются оба символа вместе, а в системах *NIX применяется только перевод строки.

    Будьте внимательны: В Excel встречаются оба варианта. При импорте из файлов .txt или .csv данные обычно содержат возвраты каретки и переводы строки. Когда перенос строки вводится вручную нажатием Alt+Enter, Excel вставляет только символ перевода строки. Если же файл .csv получен от поклонника Linux, Unix или другой подобной системы, то готовьтесь к встрече только с символом перевода строки.

    Удаляем возвраты каретки вручную

    Плюсы: Этот способ самый быстрый.

    Минусы: Никаких дополнительных плюшек

    Вот так можно удалить переносы строк при помощи инструмента «Найти и заменить»:

    1. Выделите все ячейки, в которых требуется удалить возвраты каретки или заменить их другим символом.
    2. Нажмите Ctrl+H, чтобы вызвать диалоговое окно Найти и заменить (Find and Replace).
    3. Поставьте курсор в поле Найти (Find what) и нажмите Ctrl+J. На первый взгляд поле покажется пустым, но если посмотрите внимательно, то увидите в нём маленькую точку.
    4. В поле Заменить на (Replace With) введите любое значение для вставки вместо возвратов каретки. Обычно для этого используют пробел, чтобы избежать случайного склеивания двух соседних слов. Если нужно просто удалить переносы строк, оставьте поле Заменить на (Replace With) пустым.
    5. Нажмите кнопку Заменить все (Replace All) и наслаждайтесь результатом!

    Удаляем переносы строк при помощи формул Excel

    Плюсы: Доступно использование последовательных или вложенных формул для сложной проверки текста в обрабатываемой ячейке. Например, можно удалить возвраты каретки, а затем найти лишние начальные или конечные пробелы, или лишние пробелы между словами.

    Читать еще:  Excel сводные таблицы формулы функция впр

    В некоторых случаях переносы строк необходимо удалять, чтобы в дальнейшем использовать текст в качестве аргументов функций, не внося изменения в исходные ячейки. Результат можно использовать, например, как аргумент функции ПРОСМОТР (LOOKUP).

    Минусы: Потребуется создать вспомогательный столбец и выполнить множество дополнительных шагов.

    1. Добавьте вспомогательный столбец в конце данных. В нашем примере он будет называться 1 line.
    2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления/замены переносов строк. Ниже приведены несколько полезных формул для различных случаев:
      • Эта формула подходит для использования с комбинациями возврат каретки / перенос строки, характерными для Windows и для UNIX.

    =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);»»);СИМВОЛ(10);»»)
    =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),»»),CHAR(10),»»)
    Следующая формула подходит для замены переноса строки любым другим символом (например, “, ” – запятая + пробел). В таком случае строки не будут объединены и лишние пробелы не появятся.

    =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);»»);СИМВОЛ(10);», «)
    =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),»»),CHAR(10),», «)
    А вот так можно удалить все непечатаемые символы из текста, включая переносы строк:

  • Скопируйте формулу во все ячейки столбца.
  • По желанию, можете заменить исходный столбец новым, с удалёнными переносами строк:
    • Выделите все ячейки в столбце C и нажатием Ctrl+C скопируйте данные в буфер обмена.
    • Далее выделите ячейку B2, нажмите сочетание клавиш Shift+F10 и выберите Вставить (Insert).
    • Удалите вспомогательный столбец.
  • Удаляем переносы строк при помощи макроса VBA

    Плюсы: Создаём один раз – используем снова и снова с любой рабочей книгой.

    Минусы: Требуется хотя бы базовое знание VBA.

    Макрос VBA из следующего примера удаляет возвраты каретки из всех ячеек на активном листе.

    Если Вы не слишком близко знакомы с VBA, рекомендую изучить статью о том, как вставить и выполнить код VBA в Excel.

    Как перенести строки в Excel? Перенос строк в Excel

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

    Как перенести строки в Excel на новый лист?

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

    Пользователю дается возможность:

    1. Быстро вызывать диалоговое окно надстройки одним кликом по кнопке, расположенной на одной из вкладок ленты Excel;

    2. Задавать как одно, так и несколько значений для поиска, используя в качестве знака-разделителя «;» точку с запятой;

    3. Учитывать либо не учитывать регистр при поиске текстовых значений;

    4. Использовать в качестве диапазона ячеек для поиска заданных значений как используемый диапазон, так и любой произвольный диапазон листа, заданный пользователем;

    5. Выбирать одно из восьми условий для ячеек выбранного диапазона:

    а) совпадает либо не совпадает с искомым значением;

    б) содержит либо не содержит искомое значение;

    в) начинается либо не начинается с искомого значения;

    г) заканчивается либо не заканчивается искомым значением.

    6. Устанавливать ограничения для выбранного диапазона, используя номера строк.

    Перенос строки в ячейке

    Как перенести текст в ячейке на новую строку

    Для того чтобы в пределах одной ячейки перенести текст на новую строку, необходимо для переноса текста нажимать сочетание клавиш Alt+Enter.

    Как заменить перенос строки на пробел

    Часто требуется избавиться от переносов строк, удалить их, чтобы привести значения ячеек к обычному виду. Если посмотреть в таблицу ASCII-кодов,

    можно увидеть, что приложением используется код с номером 10 и названием «Перевод строки», которому соответствует сочетание клавиш Ctrl+J. Зная это, можно удалить переносы строки при помощи стандартного средства Excel, таким как «Найти и заменить». Для этого необходимо вызвать диалоговое окно сочетанием клавиш Ctrl+F (при этом поле останется пустым), перейти на вкладку «Заменить», поместить курсор в поле «Найти», нажать сочетание клавиш Ctrl+J и нажать кнопку «Заменить» либо «Заменить все». Аналогичным образом можно заменить перенос строки на пробел.

    Замену перевода строки на любой символ, например на пробел, можно осуществить и при помощи макроса.

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×