Как сделать перенос строки и перенос текста в ячейке excel
Рассмотрим различные способы как можно сделать перенос текста внутри ячеек в Excel — в виде ручного ввода символа переноса или посредством применения формул.
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
При работе с текстовыми данными в Excel мы часто сталкиваемся с проблемой разделения текста по разным строкам, иногда нам нужно перенести текст, чтобы запись стала более компактной, читаемой, целиком помещалась в ячейке и т.д.
В принципе в Excel для этого есть стандартная команда Перенести текст, либо в панели вкладок выбираем Главная -> Выравнивание:
Либо щелкаем правой кнопкой мышки по ячейке, во всплывающем окне выбираем Формат ячеек и на вкладке Выравнивание ставим галочку напротив Переносить текст:
Применение этой команды позволяет нам переносить слова в строке в зависимости от ширины столбца (по принципу шире столбец — меньше строк, уже столбец — больше строк).
Но почти всегда бывают случаи, когда нам необходимо принудительно перенести текст с одной строки на другую вне зависимости от ширины столбца.
Перенос строки в ячейке Excel – как перенести и уместить текст в одной ячейке используя символ, Enter и формулы
- Возьмем для примера только первую строку с 4 столбцами.
- Выделим область для переноса и направим курсор на строку формул. Запишем:
- =СЦЕПИТЬ(A1;СИМВОЛ(10);B1;СИМВОЛ(10);C1;СИМВОЛ(10);D1)
- Нажмем клавишу «Enter».
- Зададим «переносить по словам». Получим:
- Количество ячеек для сцепления может быть любое.
- Главное достоинство этого способа — смена данных строк и столбцов не меняет формулу, она будет иметь тот же заданный алгоритм.
Найдем количество всех значений «Яблоки», т.е. просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты в точности равно «Яблоки» (без учета РЕГИСТРА).
Пример 2. Как разделить текст по столбцам в Excel с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью инструмента текст по столбцам, который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.
Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки
Шаг 2. Далее в ячейке E1 прописываем формулу для нахождения второго знака (в нашем случае запятой). Формула аналогичная, но с небольшими изменениями.
Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.
Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.
Начальная позиция у нас 1 , количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз
Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1
Начальная позиция второго числа у нас начинается после первой запятой. Позиция первой запятой у нас есть в ячейке D1, прибавим единицу и получим начальную позицию нашего второго числа.
Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1
Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.
Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.
Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением. Так же можно сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получим итоговое решение задачи
Примечание. Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений. Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя
После этого можно сразу протягивать формулу на остальные значения. Именно этот вариант оставляю как пример для скачивания. В принципе файлом можно пользоваться как шаблоном. В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.
Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.
Таким образом последний текст будет рассчитываться начиная от последней запятой до чуть меньше 1000 знаков, то есть до конца строки, что нам и требуется.
Как искать в Excel слова, текст, ячейки и значения в таблицах
- текст — это тот же текст ФИО, в нашем примере это ячейка A2;
- начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1 ;
- число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2
В редакторе находим панель «VBAProject» и кликаем на искомый файл. Правой кнопкой вызываем контекстное меню. Выбираем сначала «Insert», затем «Module».
