Как изменить окончание слова в Excel

Обновлено: 24.11.2024

У Сэма большое количество адресов на листе. В этих адресах он должен убедиться, что все направления по компасу (СВ, ЮВ, СЗ и ЮЗ) указаны прописными буквами. Было бы очень полезно, если бы Сэм мог выяснить, как изменить любое из этих направлений нижнего регистра (или смешанного регистра), которые появляются только в конце ячейки, с их аналогами в верхнем регистре. Он не может просто искать пробел, за которым следует "ne", так как это изменит Newton на NEwton, поэтому он задается вопросом, как он может убедиться, что замена происходит только тогда, когда буквы появляются в конце ячейки.

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

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

Эта формула проверяет последние три символа, чтобы увидеть, есть ли пробел после ne, se, nw или sw. Если это так, то последние два символа делаются прописными. Формулу можно сократить, если подойти к ней иначе:

Вы можете сократить его еще больше, используя массив направлений по компасу в формуле:

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

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

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

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

Если вы хотите выделить ячейки, заканчивающиеся определенным текстом, вы можете использовать простую формулу, основанную на функции СЧЁТЕСЛИ. Например, если вы хотите выделить состояния в диапазоне B4:G12, которые заканчиваются на «ota», вы можете использовать:

Примечание. При условном форматировании важно, чтобы формула вводилась относительно "активной ячейки" в выделенном фрагменте, в данном случае предполагается, что это B4.

При использовании формулы для применения условного форматирования формула оценивается относительно активной ячейки в выделении во время создания правила. В этом случае правило оценивается для каждой ячейки в B4:G12, и ссылка на B4 изменится на адрес каждой оцениваемой ячейки, поскольку это относительный адрес.

Сама формула использует функцию СЧЁТЕСЛИ для "подсчёта" ячеек, заканчивающихся на "ota", с использованием шаблона "*ota", который использует подстановочный знак (*) для соответствия любой последовательности символов, за которой следует "ota". С практической точки зрения мы каждый раз подсчитываем только 1 ячейку, а это значит, что мы получим либо 1, либо ноль, что идеально подходит для условного форматирования.

Простое и более гибкое правило с использованием именованных диапазонов

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

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

С учетом регистра

СЧЁТЕСЛИ нечувствительны к регистру, поэтому, если вам нужно также проверить регистр, вы можете использовать более сложную формулу, которая использует функцию ПРАВИЛЬНО вместе с ТОЧНО:

В этом случае RIGHT извлекает текст справа от каждой ячейки и только количество символов в искомой подстроке, предоставленное LEN. Наконец, EXACT сравнивает извлеченный текст с искомым текстом (подстрокой). EXACT является регистрозависимым, поэтому возвращает значение TRUE только в том случае, если все символы точно совпадают.

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

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

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

Шаг 1

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

Шаг 2

Мы напишем формулу Excel для извлечения слов, оканчивающихся на «.».

В приведенной ниже формуле используется комбинация различных текстовых функций Excel, таких как ДЛСТР, НАЙТИ и ПОДСТАВИТЬ, для сканирования исходных данных и возврата выходных данных.

=ЕСЛИОШИБКА(СРЕДН(A2,НАЙТИ(СИМВОЛ(160),ПОДСТАВИТЬ(ЛЕВАЯ(A2,НАЙТИ(“.”,A2))”, “,СИМВОЛ(160),ДЛСТР(ЛЕВА(A2,НАЙТИ(“ .,A2)))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВАЯ(A2,НАЙТИ(“.”,A2))”, “,”))))+1,НАЙТИ(“.,A2)-НАЙТИ(СИМВОЛ (160), ПОДСТАВИТЬ (ЛЕВАЯ (A2, НАЙТИ («.», A2))», «, СИМВОЛ (160), ДЛСТР (ЛЕВАЯ (A2, НАЙТИ («.», A2)))-ДЛСТР (ПОДСТАВИТЬ (ЛЕВАЯ (A2, НАЙТИ («.», A2))», «»,»)))))»,»)

LEN – определяет длину аргумента.

SUBSTITUTE — заменяет определенный символ новым символом.

НАЙТИ — указывает положение определенного символа в исходных данных.

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

Шаг 3

Вы можете видеть, что формула дает слово "точка".

Если вы не хотите, чтобы этот конкретный символ («.» в этом примере) был включен в вывод, используйте формулу ЗАМЕНЫ, чтобы заменить этот конкретный символ («.» в этом примере) пробелом, как показано на ниже формула

=ЕСЛИОШИБКА(ПОДСТАВИТЬ(СРЕДНИЙ(A2,НАЙТИ(СИМВОЛ(160),ПОДСТАВИТЬ(ЛЕВЫЙ(A2,НАЙТИ(“.”,A2))”, “,СИМВОЛ(160),ДЛСТР(ЛЕВЫЙ(A2,НАЙТИ (".",A2)))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВАЯ(A2,НАЙТИ(".",A2))", ","))))+1,НАЙТИ(".,A2)-НАЙТИ (СИМВОЛ(160),ПОДСТАВИТЬ(ЛЕВЫЙ(A2,НАЙТИ("..,A2))", ",СИМВОЛ(160),ДЛСТР(ЛЕВЫЙ(A2,НАЙТИ(".,A2)))-ДЛСТР(ПОДСТАВИТЬ (НАЛЕВО(A2,НАЙТИ(“.,A2))”, “”,”)))))”,.””,”)”,”,”)

Теперь вы можете видеть, что «.» Был удален.

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

Формула Excel для удаления первого или последнего слова из ячейки.

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

Разделы:

Удалить первое слово из ячейки

Предполагается, что текст находится в ячейке A1, при необходимости измените таблицу.

Символы без пробелов

Если в ваших словах используются тире или какой-либо другой разделитель, просто замените " " любым разделителем. Если используется дефис, измените его на "-" в формуле.

Пояснение

Функция ПРАВИЛЬНО получает текст справа от ячейки. Это необходимо, потому что мы извлекаем весь текст, кроме первого слова; это означает, что нам нужно получить все, что находится справа от первого слова в ячейке.

Функции ДЛСТР и НАЙТИ используются, чтобы сообщить функции ПРАВИЛЬНО, сколько символов справа от ячейки мы хотим получить. Функция ПРАВИЛЬНО извлекает только определенное количество символов справа от ячейки.

Чтобы получить все, кроме первого слова, нам нужно найти пробел, который отделяет первое слово от остального текста.Используйте функцию НАЙТИ, ища пробел " ", чтобы получить количество символов слева от ячейки до первого пробела. В приведенном выше примере это возвращает 5. Это количество символов, которые мы хотим удалить из ячейки.

Чтобы получить число, которое мы хотим сохранить, нам нужно использовать функцию ДЛСТР для подсчета всех символов в ячейке. Результат функции ДЛСТР равен 12, поскольку в ячейке 12 символов.

В приведенной выше формуле мы вычитаем результат НАЙТИ из результата ДЛСТР, который равен 12-5 или 7.

Это указывает функции ПРАВИЛЬНО получить 7 символов справа от ячейки, что эффективно удаляет первое слово из ячейки.

Удалить последнее слово из ячейки

Предполагается, что текст находится в ячейке A1, при необходимости измените таблицу.

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

Символы без пробелов

Пояснение

В этой формуле используются четыре функции: НАЛЕВО(), ДЛСТР(), НАЙТИ(), ПОДСТАВИТЬ().

Это может немного сбить с толку, но вам не нужно читать этот раздел, чтобы использовать формулу.

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

Для этого нам нужно сообщить функции ВЛЕВО, сколько символов слева от ячейки мы хотим в новой ячейке, и это число может варьироваться. Сложная часть формулы — это то, что дает нам это число.

Эта часть формулы ПОДСТАВИТЬ(A1," ","") удаляет все пробелы в ячейке, а затем подсчитывает количество оставшихся символов с помощью функции ДЛСТР ДЛСТР(ПОДСТАВИТЬ(A1," ","")). Из приведенного выше примера результат равен 10.

Затем подсчитывается количество символов в ячейке, когда в ней остаются пробелы: LEN(A1). Результат равен 12.

Затем формула вычитает два вышеуказанных числа, чтобы получить 2 в этом примере. Это число представляет собой общее количество пробелов в ячейке, и оно сообщает другой функции ПОДСТАВИТЬ, какой пробел следует заменить; мы хотим заменить последний пробел, чтобы его было легче найти.

Используя только что полученное число, снова используется функция ПОДСТАВИТЬ, чтобы заменить последний пробел в ячейке на ~, а затем используется функция НАЙТИ, чтобы найти это ~, и затем формула знает, сколько символов слева от ячейка последний пробел.

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

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

Если вы все еще немного запутались, ознакомьтесь с нашим руководством по функции ПОДСТАВИТЬ в Excel

Более универсальные формулы — предотвращение ошибок

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

Чтобы избежать ошибок, мы можем использовать простую функцию ЕСЛИОШИБКА().

Формула 1:

Формула 2:

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

Прочитайте наш учебник, чтобы узнать больше о функции ЕСЛИОШИБКА в Excel и подавлении ошибок в целом.

Примечания

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

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

Извлечение последнего слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта UDF (определяемая пользователем функция) извлекает последнее слово или символы из ячейки в Excel.

Получить первое слово из ячейки в Excel
Учебник: Как использовать формулу для получения первого слова из ячейки в Excel. Это работает для одного c.

Извлечение первого слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта бесплатная функция Excel UDF (определяемая пользователем функция) возвращает первое слово из ячейки в Exce.

Удалить все проверки данных из ячейки в Excel
Макрос: удалите все проверки данных из ячейки в Excel с помощью этого бесплатного макроса Excel. Это здорово.

Удалить все пустые строки или пустые ячейки из диапазона в Excel
Учебник: Как быстро удалить все пустые ячейки или строки из диапазона в Excel. Это позволяет вам .

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

Читайте также: