Auditportal.ru

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

Как извлечь первые / последние n символов из строки в Excel?

Перенос строки в Excel 2010 — 2013

Простой перенос (разрыв) строки текста в ячейке таблицы Excel сделать достаточно легко –
надо только установить курсор в нужное место текста и нажать комбинацию клавиш Ctrl + Enter.

Строка текста будет разорвана, равно как-бы в Worde нажали «на просто Enter»

Если же нужно разорвать текст непосредственно в формуле Excel,
то тут поможет служебный знак ;СИМВОЛ(10); и функция =СЦЕПИТЬ

Обязательное форматирование ячейки на перенос текста

Прежде, чем разрывать текст в ячейке Excel и переносить строку –
нужно обязательно отформатировать ячейку на перенос строк,
иначе – не будет работать :):):).

Форматирование ячейки Excel на перенос строки текста Для правильного и корректного отображения переноса строки текста в ячейке Excel – включаем «перенос по строкам» в свойствах самой ячейки. Для этого, после выделения ячейки или группы ячеек:
1. либо, «Формат ячеек / Выравнивание / Переносить по словам»
2. либо, нажать на специальный значок на вкладке «ГЛАВНАЯ»

Перенос строки в Excel, формат ячеек Перенос строки в Excel, формат ячеек Перенос строки в Excel, панель управления Перенос строки в Excel, панель управления Перенос строки в Excel, результат Перенос строки в Excel, результат

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

Простой перенос (разрыв, перевод) строки текста в ячейке Excel

Чтобы сделать простой перенос (разрыв, перевод) строки текста в ячейке таблицы Excel – нужно установить курсор в нужное место текста и нажать комбинацию клавиш Ctrl + Enter.

Строка текста будет разорвана, равно как-бы в Worde нажали «на просто Enter»

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

Перенос текста в формуле Excel

Более сложный способ переноса строк текста в ячейке Excel – установка знака переноса (перевода) строки непосредственно в формулу ячейки. Такой вариант может понадобиться, когда текст или иные знаки в ячейке выводятся при помощи формулы, в которой задействованы значения других ячеек.

Текст самой формулы, в данном случае, выглядит следующим образом:

=СЦЕПИТЬ(«ТЕКСТ1″;СИМВОЛ(10);»ТЕКСТ2»)

где, =СЦЕПИТЬ – служебные знаки Excel, означающие простое сложение без выполнения арифметических действий, а ;СИМВОЛ(10); – служебный знак Excel, означающий перевод строки. Лошади понятно, что выражения, вида «ТЕКСТ1» и «ТЕКСТ2» – это знаки разрываемого текста. Результат введения такой формулы вызлядит, примерно так:

Перенос текста в формуле Excel Перенос текста в формуле Excel

Однако если ячейка Excel берет значения из других ячеек, а сама формула содержит текст и вычисления, которые должны быть распределены по разным строкам, приведенная выше формула примет вид, который можно написать более понятным способом:

Читайте так же:
Как запустить один и тот же макрос на нескольких листах одновременно в Excel?

Где, присоединенный (сеточный) модуль имеет вид

&СЦЕПИТЬ(СИМВОЛ(10);»ТЕКСТ4″) и он присоединяется, обязательно через символ «& — амперсанд». Вместо «ТЕКСТ4» можно вписать любую формулу или адрес любой ячейки и её значение (или результат вычислений формулы) будет отображаться в ячейке Excel с новой строки. При этом, сами вычисления будут производиться внутри каждого модуля, а результат вычислений – будет выводиться отдельно, каждый раз – с новой строки. Например, конструкция вида:

Будут иметь следующее представление:

Перенос строки в Excel 2010 - 2013

Сложение текста в ячейках Excel

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

Будут иметь отображение:

Сложение текста в формуле Excel Сложение текста в формуле Excel

Повторение однотипных действий

Маленький бонус для пользователей Word и Excel от tehnopost.info, способный отвлечь пользователя от прекрасного, но рутинного занятия по переносу строк и облегчить радость компьютерного бытия:
– при форматировании текста и ячеек, в Word и Excel, используйте клавишу F4 для повторения последнего действия пользователя. Это очень удобно при выполнении большого объёма однотипных рутинных действий, типа добавлени строки, заливки, размера текста и т.д.

– после выделения участка текста или ячейки и нажатия клавиши F4 будет повторено последнее действие пользователя, выполненное им перед этим. Прим. На «копировать-вырезать-вставить» это не распространяется, а вот на «добавить строку», изменить заливку, цвет или размер текста – это запросто.

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

Как жалкое оправдание своим нечеловеческим потугам по переносу строки в тексте, я привожу тот факт, что мне, равно как и подавляющему большинству интернет-пользователей – вообще этот Excel «до осветительного прибора». Потому что, я – не есть высокопрофессиональный бухгалтер или экономист, сидящий в этом Excele с утра до вечера. . и, тем не менее – мне часто приходится работать с таблицами Excel, например – для анализа статистики в Google AdSense или РСЯ (Рекламной Сети Яндекса).

Найти и заменить в Excel

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

Кнопка поиска на ленте

С помощью этой команды можно искать ячейки, содержащие нужные вам символы. Поиск данных может осуществляться в определенном диапазоне, на всем рабочем листе или даже во всей книге. Для одной активной ячейки по умолчанию выполняется поиск во всем листе. Перед поиском значения необходимо выделить диапазон ячеек Excel.

Затем нужно набрать Главная * Правка * Найти и Выбрать * Найти (кнопка на рисунке выше). Также вы можете активировать поиск с помощью клавиш Ctrl+F на клавиатуре. Появится диалоговое окно «Найти и заменить».

Диалоговое окно Найти и Заменить

В одном поле указывается информация (комбинация символов), которую вы хотите найти. При отсутствии подстановочных знаков или джокеров (см. ниже) Excel будет искать строгое совпадение указанных символов. Есть два варианта вывода результатов поиска: вывод всех результатов сразу — кнопка Найти все; или вывод одного значения за раз — кнопка Найти далее.

Читайте так же:
Как использовать флажок, чтобы скрыть или показать рабочий лист в Excel?

После начала поиска Excel быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации. Если такая комбинация найдена, Excel выгрузит все обнаруженные ячейки, если вы нажмете кнопку Найти все.

Найти все

Если выделить значение в нижней части окна, а затем нажать Ctrl+A, все соответствующие ячейки в диапазоне поиска будут выделены.

Кнопка Найти далее выделяет ближайшую совпадающую ячейку, если вы начинаете поиск таким образом. Следующая ближайшая ячейка (соответствующая параметрам поиска) будет выделена при повторном нажатии кнопки Найти далее (или Enter с клавиатуры). После выделения последней ячейки Excel переходит к самой верхней ячейке, а затем начинает поиск заново. Большинство пользователей не знают о поиске данных в Excel.

Поиск нестрогого соответствия символов

Иногда пользователь не знает точную комбинацию искомых символов, что сильно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, аббревиатуры и т.д., что вносит дополнительную путаницу и делает поиск практически невозможным. Может возникнуть и обратная ситуация: Для данной комбинации слишком много клеток, и опять же цель поиска не достигнута (кому нужно более 100500 найденных клеток? ).

Для решения этих проблем подстановочные знаки очень хорошо подходят для информирования Excel о сомнительных ранжированиях. В подстановочных знаках могут скрываться различные символы, и Excel видит только их относительное положение в поисковом выражении. Существует два таких символа подстановки: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?». (символ «?» указывает на неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Читайте так же:
Как изменить указанное значение или цвет ячейки при установленном флажке в Excel?

Есть еще один джокер — символ «?». Одиночный неопознанный символ может быть скрыт. Используя в качестве примера 1?6, Excel найдет все ячейки, содержащие последовательность 106, 116, 126, 136 и т.д. А если вы укажете 1? то поисковая система Excel найдет ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более строгие ограничения на поиск, который учитывает количество пропущенных символов (равное количеству вопросов «?»).

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

Продвинутый поиск

Мало кто использует кнопку Параметры в диалоговом окне Найти и заменить. И не зря. В нем скрыто множество полезных функций, которые помогут вам решить проблемы поиска. Нажав кнопку Options, вы добавляете дополнительные поля, которые углубляют и расширяют условия поиска.

Параметры в поиске

С помощью дополнительных параметров поиск в Excel может буквально обрести новый цвет. Например, можно искать не только конкретные числа или текст, но и формат ячейки (залита определенным цветом, с определенными краями и т.д.).

Поиск форматов

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

Выбрать формат

Это позволяет найти, например, все слившиеся ячейки, что очень трудно сделать в противном случае.

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

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

Искать на листе

По умолчанию (если это не указано в настройках) поиск выполняется только на активном листе. Чтобы повторить поиск на другом листе, нужно начать сначала. А если этих листов много, то поиск данных может занять много времени. Однако если выбрать пункт Книга, поиск будет производиться по всем листам активной рабочей книги. Преимущество очевидно.

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

Читайте так же:
Как найти максимальное или минимальное значение в группе в Excel?

В диалоговом окне поиска есть еще два флажка Проверить регистр и Вся ячейка. По умолчанию Excel игнорирует регистр, но вы можете сделать «Иванов» или «Иванов» разными. Это также полезно, если вы ищете не ячейку с указанным фрагментом, а ячейку, содержащую весь набор искомых символов. Например, как найти ячейки, в которых есть только 0? Обычный поиск не сработает, потому что вы получите результаты как 10, так и 100. Если проверять всю ячейку целиком, все пройдет гладко.

Поиск и замена данных

Обычно поиск данных осуществляется по каким-либо причинам. Это может быть цель заменить одну комбинацию (или формат) на другую. Чтобы найти и заменить в выбранном диапазоне Excel одни значения на другие, в окне Найти и заменить нужно выбрать вкладку Заменить. Затем можно воспользоваться командой Главная → Правка → Найти и выделить → Заменить на ленте.

Заменить

Для поиска и замены в Excel также можно применять горячую клавишу Ctrl+H.

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

Диалоговое окно Заменить

Как и при простом поиске, можно изменить формат.

Заменить формат

Кнопка Заменить все позволяет одновременно заменить один символ на другой. После замены Excel отображает информационное окно с количеством произведенных замен. Кнопка Заменить все позволяет заменять по одной ячейке при каждом щелчке. Если функция «Найти и заменить» в Excel не работает, попробуйте изменить параметры поиска.

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.
Разделение строк в ячейке
А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Поиск и замена разрывов строк в ячейке

Нажмите OK. Все переносы строк заменяются пробелами.

Замена разделителей строк пробелами

Неопытные пользователи могут сэкономить бесчисленные часы работы, правильно используя функцию поиска и замены. Я настоятельно рекомендую использовать все вышеперечисленные функции. Использование подстановочных знаков «*» и «?» поможет вам уточнить поиск, если в ваших данных что-то отсутствует или отображается слишком много ячеек. или настраиваемые параметры поиска. Если вы ничего не нашли, это не значит, что этого нет.

Теперь вы знаете, как искать в столбце, строке, диапазоне, листе или даже рабочей книге в Excel.

Формула для удаления непечатных символов в Excel

Давайте рассмотрим пример. Предположим, у нас есть список сотрудников, и мы хотим ограничить поиск только теми сотрудниками, фамилии которых начинаются на определенную букву (например, «p»):

Читайте так же:
Как извлечь номер улицы из адреса в Excel?


Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):


Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:


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

Применение в функциях

Подстановочные знаки Excel можно использовать при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИН, ГПР, ВПР и других).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):


Как и в первом примере, в результате мы получили ровно 3 фамилии.

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:


Обратным примером служит аналогичная функция ПОИСК, в которой мы должно четко указать что ищем именно служебный символ:


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

Удалить символы в начале ячейки (слева)

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

Создайте дополнительную колонку, даже если вы не уверены, что делаете.

«Обрежьте» несколько символов слева от ячеек колонки.

Удалить символы в конце ячейки (справа):

Эта процедура необходима, когда у нас есть массив выражений, в котором нужно обрезать последние буквы в конце строки, и мы уверены в их количестве на 100%.

Выберите область и выберите «от начала ячейки» на панели SEMTools в разделе DELETE -> Draw -> Trim. Введите количество символов, которые вы хотите удалить, в появившемся диалоговом окне и нажмите OK.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector