Советы по работе с текстом и текстовыми функциями в Excel

Когда вы думаете об Excel (наши самые важные советы Excel

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

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

Обтекание текста в ячейке

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

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

Текст помещается в ячейку, и высота строки автоматически подстраивается под размер текста.

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

Чтобы автоматически подогнать высоту строки к тексту, выберите ячейку. Затем нажмите Формат в ячейки раздел на Главная вкладка и выберите AutoFit Высота строки.

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

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

Введите разрыв строки в ячейке

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

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

Высота строки корректируется по размеру текста, если Формат установлен в AutoFit Высота строки в ячейки раздел Главная Вкладка.

Подсчет клеток, содержащих любой текст

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

,

Общей формой функции COUNTIF для подсчета любого количества текстовых символов является:

=COUNTIF(cellrange,"*")

Cellrange представляет любой диапазон ячеек, таких как B2: B9. Звездочка между кавычками — это подстановочный знак, представляющий любое соответствующее количество текстовых символов. Есть несколько моментов, которые следует помнить о том, что считать текстовыми символами:

• Логические значения TRUE и FALSE не считаются как текст.
• Числа, введенные в виде текста, учитываются в подстановочном знаке (*).
• Пустая ячейка, которая начинается с апострофа (‘), считается.

Например, чтобы подсчитать количество ячеек, содержащих текст в диапазоне ячеек A2: G9 на следующем листе, мы вводим «= COUNTIF («. Затем, чтобы ввести диапазон ячеек, мы выбираем ячейки, которые хотим включить в подсчет ,

Функция COUNTIF не чувствительна к регистру.

Затем мы вводим запятую (,) и подстановочный знак (*) в двойных кавычках.

Нажмите Войти завершить ввод функции и просмотреть результат в ячейке.

Подсчет клеток, содержащих определенный текст

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

Общей функцией для подсчета вхождений конкретной строки текстовых символов является:

=COUNTIF(cellrange,"txt")

Как и в предыдущем разделе, диапазон ячеек представляет любой диапазон ячеек, таких как B2: B9. Мы помещаем строку текстовых символов, которые мы хотим найти между двойными кавычками.

Например, чтобы подсчитать количество ячеек, содержащих «Карандаш» в диапазоне ячеек A2: G9 на следующем листе, мы вводим следующую функцию:

=COUNTIF(A2:G9,"Pencil")

Это находит все ячейки, содержащие только слово «Карандаш» без другого текста в ячейке. Поскольку функция COUNTIF не чувствительна к регистру, она найдет все ячейки, содержащие «Карандаш» или «Карандаш».

Функция COUNTIFS позволяет подсчитывать ячейки с текстом, но исключать ячейки с определенными текстовыми символами.

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

=COUNTIFS(A2:G9,"*",A2:G9,"Pencil")

Для функции COUNTIFS вы сначала указываете диапазон и текст, который хотите найти. Затем дайте ему тот же диапазон снова и текст, который вы хотите исключить. «» Используется для исключения любого текста, который следует.

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

Например, чтобы найти все ячейки, содержащие букву «j», мы используем следующую функцию:

=COUNTIF(A2:G9,"*j*")

Опять же, поскольку функция COUNTIF не чувствительна к регистру, будут подсчитаны ячейки, содержащие «j» или «J».

Преобразовать текст в числа

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

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

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

Преобразовать число в текст

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

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

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

=TEXT(B2,"0")

Вы даете функции ссылку на ячейку для конвертируемого числа, а затем формат числа, который вы хотите. Мы просто конвертируем в число без специального форматирования (например, не валюта или дата). Поэтому мы используем «0» (ноль).

Используйте функцию автозаполнения

скопировать функцию TEXT для остальных ячеек. Числа становятся текстовыми и выровнены по левому краю.

Вы можете скопировать и вставить преобразованные значения в исходный столбец. Выберите ячейки, содержащие функцию ТЕКСТ, и нажмите Ctrl + C скопировать их. Выберите первую ячейку в исходном столбце. На Главная нажмите стрелку на Вставить Кнопка и перейти к Специальная вставка> Значения.

Примеры различных форматов текста, доступных для использования в функции TEXT на сайте поддержки Microsoft.

Преобразовать текст в дату

Вы когда-нибудь получали рабочую книгу от кого-то другого, в котором они вводили даты в виде текста, чисел или в формате, не распознаваемом как даты? Вы можете конвертировать текст в даты, используя функцию DATE.

Вот общий формат функции DATE:

=DATE(year,month,day)

Для года, месяца и дня мы собираемся использовать строковые функции LEFT, MID и RIGHT для извлечения соответствующих частей текста или числа, которое мы хотим преобразовать. Мы объясним четыре примера на рисунке ниже.

Чтобы преобразовать «20171024» в ячейке C2 в дату, мы использовали функцию ВЛЕВО, чтобы извлечь первые четыре символа года (2017). Затем мы использовали функцию MID для извлечения двух символов, начиная с пятой позиции, как месяц (10). Наконец, мы использовали функцию RIGHT, чтобы извлечь последние два символа как день (24).

=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

Следующий пример, «2102018» в ячейке C3, находится в другом порядке. Мы по-прежнему используем строковые функции, но в другом порядке. Мы использовали функцию RIGHT, чтобы извлечь последние четыре символа за год (2018). В этом случае месяц — только одна цифра, поэтому мы использовали функцию ВЛЕВО, чтобы извлечь первый символ как месяц (2). Наконец, мы использовали функцию MID для извлечения двух символов, начинающихся со второй позиции как день (10).

=DATE(RIGHT(C3,4),LEFT(C3,1),MID(C3,2,2))

Даты в ячейках C4 и C5 выглядят как обычные даты, но Excel не распознает их как даты. В ячейке C4 форматом является день, месяц, год. Таким образом, мы используем функции RIGHT, MID и LEFT следующим образом:

=DATE(RIGHT(C4,4),MID(C4,4,2),LEFT(C4,2))

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

=DATE(RIGHT(C5,4),LEFT(C5,2),MID(C5,4,2))

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

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

Объединить текст из нескольких ячеек

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

Например, у нас есть рабочий лист, содержащий имена сотрудников и их контактную информацию, как показано ниже. Мы хотим отделить Имя а также Фамилия а затем объединить их в ФИО колонка. Мы также можем создать Адрес электронной почты автоматически путем объединения имени и фамилии.

Для этого мы используем функцию CONCATENATE

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

Совместить Фамилия а также Имя в один ряд в ФИО В столбце мы используем функцию CONCATENATE следующим образом:

=CONCATENATE(B2," ",A2)

Дайте функции CONCATENATE текст, который нужно объединить в том порядке, в котором вы хотите, чтобы он сложился. Таким образом, мы дали функцию Имя (B2), пробел в двойных кавычках (”“), затем Фамилия (А2).

Мы также можем создать адрес электронной почты таким же образом. Мы используем Имя (B2), Фамилия (A2), а затем остаток адреса электронной почты (@ email.com) в двойных кавычках.

=CONCATENATE(B2,A2,"@email.com")

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

Отдельный текст на несколько ячеек

У вас есть несколько ячеек со смешанным содержимым, которые вы хотите разделить? Например, если у вас есть ячейка, содержащая «14 бутербродов с индейкой», вы можете разделить ее на число (14) и текст (бутерброды с индейкой). Таким образом, вы можете использовать число в функциях и формулах.

Чтобы получить число из «14 бутербродов с индейкой», мы используем функцию LEFT.

=LEFT(B2,SEARCH(" ",B2, 1))

Сначала мы даем функции ссылку на ячейку для текста, из которого мы хотим извлечь число (B2). Затем мы используем функцию ПОИСК, чтобы найти первый пробел после первого символа в строке.

Чтобы получить текст из «14 бутербродов с индейкой», мы используем правую строковую функцию.

=RIGHT(B2,LEN(B2)-SEARCH(" ", B2, 1))

Сначала мы даем функции RIGHT ссылку на ячейку, из которой мы хотим извлечь текст (B2). Затем мы используем функции LEN и SEARCH, чтобы определить, сколько символов справа мы хотим получить. Мы вычитаем количество символов из первого пробела после первого символа в строке до конца строки из общей длины строки.

Узнайте больше о разделении текста на несколько ячеек в нашей статье о выделении текста или чисел из ячеек смешанного формата.

Подробнее о работе с текстовыми функциями в Excel

Иногда в электронной таблице, над которой вы работаете, слишком много текста. Это поможет вам упростить это.

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

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