Как удалить все что после определенного знака в эксель
Как в Excel удалить текст до определенного символа
Довольно часто в Excel нужно убрать из строки символы до определенного знака. Это может быть как один символ (пробел, точка, запятая, двоеточие и т.д.), так и символ из определенного множества (цифра, английская буква).
В зависимости от этих условий, решение может быть простым, а может потребовать использование довольно сложных формул и процедур.
В этой статье я подробно опишу известные мне варианты. Поехали!
Удалить текст до последнего символа
Наиболее просто удалить текст в диапазоне ячеек до последнего вхождения заданного символа или текста. Можно воспользоваться процедурой «Найти и заменить»:
Процедура заменит все символы до знака на пустоту, иначе говоря, удалит их. Звездочка как раз и обозначает «все символы».
Удалить текст до первого пробела — формула
Данная формула универсальна и позволит удалить не только текст до первого пробела, но и до любого другого первого вхождения символа в строке.
Однако удалить первое слово в ячейке — самая популярная задача, и пробел как раз и является символом, который отгораживает первое слово слева от остального текста.
Формулы для других символов аналогичны:
Убрать текст в ячейке до первой запятой:
А чтобы удалить текст до вхождения последовательности символов, например, слова, нужно использовать в формуле длину этой последовательности, сократив ее на 1:
Здесь из адресов удаляется текст до номера дома. Длина текста в кавычках — 3 символа (д, точка и пробел):
Удалить текст до первой цифры
Пожалуй, вы уже догадались, но я все же объясню, как работают формулы выше:
Следовательно, чтобы удалить текст до первой цифры, нужно вычислить позицию первой цифры в строке. Поскольку цифр не одна, а 10, простая формула уже не подойдет. Но можно сделать это вот такой формулой массива:
А подставив это выражение в исходную формулу, мы сможем решить исходную задачу — получить текст после первой цифры включительно, удалив текст до нее.
Что за числа 48 и 57, можно прочитать подробнее тут:
Обратите внимание на фигурные скобки! Их не нужно вводить, они появятся сами при вводе формулы сочетанием клавиш Ctrl+Shift+Enter.
Формула выдаст ошибку, если цифр в строке нет.
Каждый из перечисленных способов хорош по-своему, но всем им недостает одного важного фактора — скорости исполнения.
В каждом из случаев нужно вводить много символов и производить много действий.
Теперь удалить текст до первого или последнего вхождения конкретного символа или подстроки, включая и не включая сам текст, можно в считанные мгновения!
При этом можно как менять данные на месте, так и автоматически выводить в соседний столбец — за это отвечает глобальный чекбокс.
Хотите так же быстро обращаться с текстовыми данными в Excel и испытать на практике еще более 500 похожих процедур?
Как удалить все что после определенного знака в эксель
Например, у вас есть список текстовых строк, как показано на скриншоте ниже, и теперь вам нужно заменить все символы после определенной буквы «b» другой текстовой строкой в Excel. Есть идеи быстро решить эту проблему в Excel? В этой статье представлены два простых решения.
Этот метод представит функцию «Найти и заменить», чтобы легко заменить все символы после или перед указанным в Excel. Пожалуйста, сделайте следующее:
1. Выберите список, в котором вы замените все символы после / перед определенными символами, и нажмите Ctrl + H одновременно клавиши, чтобы открыть диалоговое окно «Найти и заменить».
Внимание: Вы также можете открыть диалоговое окно «Найти и заменить», нажав Главная > Найти и выбрать > Заменять.
2. В диалоговом окне «Найти и заменить» сделайте следующее ：
(1) В Найти то, что коробка, пожалуйста, введите b* ;
(2) В Заменить введите текстовую строку, которой вы будете заменять.
(3) Щелкните значок Заменить все кнопку. 
3. Теперь появится диалоговое окно, в котором будет показано, сколько замен было произведено. Просто нажмите на OK кнопку, чтобы закрыть его. Затем закройте диалоговое окно «Найти и заменить». 
И теперь в нашем примере вы увидите все символы после замены b на указанный текст в выбранных ячейках. Смотрите скриншот: 
Удалить символы с начала / справа / с конца в ячейках
Kutools для Excel Удалить по позиции Функция упрощает удаление определенного количества символов из начала / конца / указанной позиции текстовой строки в ячейках. Полнофункциональная бесплатная 30-дневная пробная версия! 
Нет необходимости заменять все символы после / до обычного символа (например, буквы, числа и т. Д.) Или заменять все после / до специальных символов (например, *,? И т. Д.), Kutools for Excel может помочь вам легко получить его сделано в Excel.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
1. Выберите список текстовых строк, которые вы замените все до / после определенного символа, и нажмите Kutools > Текст > Разделить клетки. 
2. В открывшемся диалоговом окне «Разделить ячейки» установите флажок Разделить на столбцы вариант, проверьте Другое вариант и введите конкретный символ (в нашем примере мы вводим звездочку *) в поле ниже и щелкните значок Ok кнопка. Смотрите скриншот:
Во втором диалоговом окне «Разделить ячейки» укажите первую ячейку целевого диапазона и нажмите кнопку OK кнопку. 
И тогда вы увидите, что список текстовых строк разделен на две колонки. Смотрите скриншот:
3. Выберите первый столбец из двух разделенных столбцов и нажмите Kutools > Текст > Добавить текст. 
4. В открывшемся диалоговом окне «Добавить текст» введите текст, которым вы будете заменять, в поле Текст поле, отметьте После последнего символа и нажмите Ok кнопка. Смотрите скриншот:
И тогда вы увидите, что указанный текст заменяет все символы после определенного символа (в нашем случае звездочка). Смотрите скриншот: 
Внимание: Чтобы заменить все перед указанным символом, выберите второй столбец обоих разделенных столбцов на шаге 3 и установите флажок Перед первым персонажем вариант на шаге 4. См. снимок экрана: 
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Как удалить текст до или после определенного символа в Excel
При работе с текстовыми данными в Excel вам может потребоваться удалить текст до или после определенного символа или текстовой строки.
Например, если у вас есть данные об именах и обозначениях людей, вы можете удалить обозначение после запятой и оставить только имя (или наоборот, если вы сохраните обозначение и удалите имя).
Иногда это можно сделать с помощью простой формулы или быстрого поиска и замены, а иногда требуются более сложные формулы или обходные пути.
В этом уроке я покажу вам, как удалить текст до или после определенного символа в Excel (на разных примерах).
Итак, давайте начнем с нескольких простых примеров.
Удаление текста после символа с помощью функции поиска и замены
Если вы хотите быстро удалить весь текст после определенной текстовой строки (или перед текстовой строкой), вы можете сделать это с помощью «Найти и заменить» и подстановочных знаков.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите удалить обозначение после символа запятой и оставить текст перед запятой.
Ниже приведены шаги для этого:
Вышеупомянутые шаги позволят найти запятую в наборе данных и удалить весь текст после запятой (включая запятую).
Поскольку это заменяет текст из выбранных ячеек, рекомендуется скопировать текст в другой столбец, а затем выполнить эту операцию поиска и замены или создать резервную копию ваших данных, чтобы у вас были исходные данные без изменений.
Как это работает?
* (знак звездочки) — это подстановочный знак, который может представлять любое количество символов.
Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.
Это связано с тем, что знак звездочки (*) считается соответствием всей текстовой строке, следующей за запятой.
Поэтому, когда вы нажимаете кнопку «Заменить все», она заменяет запятую и весь последующий текст.
Если вы хотите удалить все символы перед запятой, измените запись в поле поиска, поставив перед запятой знак звездочки (* вместо *)
Удалить текст с помощью формул
Если вам нужно больше контроля над поиском и заменой текста до или после определенного символа, лучше использовать встроенные текстовые формулы в Excel.
Предположим, у вас есть приведенный ниже набор данных, из которого вы хотите удалить весь текст после запятой.
Ниже приведена формула для этого:
В приведенной выше формуле используется функция FIND (НАЙТИ), чтобы найти положение запятой в ячейке.
Этот номер позиции затем используется функцией LEFT для извлечения всех символов перед запятой. Поскольку мне не нужна запятая как часть результата, я вычел 1 из результирующего значения формулы поиска.
Это был простой сценарий.
Возьмем немного сложный.
Предположим, у меня есть этот набор данных ниже, из которого я хочу удалить весь текст после второй запятой.
Вот формула, которая сделает это:
Поскольку в этом наборе данных есть несколько запятых, я не могу использовать функцию FIND, чтобы получить позицию первой запятой и извлечь все, что находится слева от нее.
Мне нужно как-то узнать положение второй запятой, а затем извлечь все, что находится слева от второй запятой.
Для этого я использовал функцию SUBSTITUTE, чтобы заменить вторую запятую восклицательным знаком. Теперь это дает мне уникальный персонаж в камере. Теперь я могу использовать положение восклицательного знака, чтобы извлечь все, что находится слева от второй запятой.
Эта позиция восклицательного знака используется в функции LEFT для извлечения всего, что находится до второй запятой.
Все идет нормально!
Но что, если в наборе данных непоследовательное количество запятых.
Например, в приведенном ниже наборе данных в некоторых ячейках есть две запятые, а в некоторых — три запятые, и мне нужно извлечь весь текст до последней запятой.
В этом случае мне нужно как-то определить позицию последнего вхождения запятой, а затем извлечь все, что находится слева от нее.
Ниже приведена формула, которая сделает это
В приведенной выше формуле функция LEN используется для определения общей длины текста в ячейке, а также длины текста без запятой.
Когда я вычитаю эти два значения, получается общее количество запятых в ячейке.
Таким образом, это дало бы мне 3 для ячейки A2 и 2 для ячейки A4.
Это значение затем используется в формуле SUBSTITUTE для замены последней запятой восклицательным знаком. И затем вы можете использовать левую функцию для извлечения всего, что находится слева от восклицательного знака (где раньше была последняя запятая)
Как вы можете видеть в примерах, использование комбинации текстовых формул позволяет обрабатывать множество различных ситуаций.
Кроме того, поскольку результат связан с исходными данными, при изменении исходных данных результат будет автоматически обновляться.
Удаление текста с помощью заливки Flash
Flash Fill — это инструмент, который был представлен в Excel 2013 и доступен во всех последующих версиях.
Он работает, выявляя закономерности, когда вы вручную вводите данные, а затем экстраполируете их, чтобы получить данные для всего столбца.
Поэтому, если вы хотите удалить текст до или после определенного символа, вам просто нужно показать flash fairy, как будет выглядеть результат (введя его вручную пару раз), и flash fill автоматически распознает узор и даст вам результаты.
Позвольте показать вам это на примере.
Ниже у меня есть набор данных, из которого я хочу удалить весь текст после запятой.
Вот шаги, чтобы сделать это с помощью Flash Fill:
Вышеупомянутые шаги дадут вам результат, как показано ниже:
Вы также можете использовать сочетание клавиш Control + E для Flash Fill после выбора ячеек в столбце результатов (столбец B в нашем примере)
Flash Fill — замечательный инструмент, и в большинстве случаев он может распознать узор и дать вам правильный результат. но в некоторых случаях он может не распознать шаблон правильно и может дать неверные результаты.
Так что не забудьте еще раз проверить результаты Flash Fill.
И точно так же, как мы удалили весь текст после определенного символа с помощью флэш-заливки, вы можете использовать те же шаги, чтобы удалить текст перед определенным символом. просто покажите вручную в соседнем столбце flash fill, как результат должен выглядеть как мой Интернет, и он сделает все остальное.
Удалить текст с помощью VBA (пользовательская функция)
Вся концепция удаления текста до или после определенного символа зависит от нахождения позиции этого символа.
Как показано выше, найти последнее вхождение этого символа хорошим означает использовать смесь формул.
Если это то, что вам нужно делать довольно часто, вы можете упростить этот процесс, создав настраиваемую функцию с помощью VBA (называемую функциями, определяемыми пользователем).
После создания вы можете повторно использовать эту функцию снова и снова. Это также намного проще и проще в использовании (поскольку большая часть тяжелой работы выполняется кодом VBA в серверной части).
Ниже кода VBA, который можно использовать для создания настраиваемой функции в Excel:
Function LastPosition(rCell As Range, rChar As String)
‘This function gives the last position of the specified character
‘This code has been developed by Sumit Bansal (https://trumpexcel.com)
Dim rLen As Integer
rLen = Len(rCell)
If Mid(rCell, i — 1, 1) = rChar Then
LastPosition = i — 1
Exit Function
End If
Вам необходимо поместить код VBA в обычный модуль редактора VB или в личную книгу макросов. Когда он у вас есть, вы можете использовать его как любую другую обычную функцию рабочего листа в книге.
Эта функция принимает 2 аргумента:
Предположим, теперь у вас есть приведенный ниже набор данных, и вы хотите удалить весь текст после последней запятой и иметь только текст до последней запятой.
Ниже приведена формула, которая сделает это:
В приведенной выше формуле я указал, чтобы найти позицию последней запятой. Если вы хотите найти положение какого-либо другого символа или текстовой строки, вы должны использовать это как второй аргумент в функции.
Как видите, это намного короче и проще в использовании по сравнению с формулой с длинным текстом, которую мы использовали в предыдущем разделе.
Если вы поместите код VBA в модуль в книге, вы сможете использовать эту настраиваемую функцию только в этой конкретной книге. Если вы хотите использовать это во всех книгах в вашей системе, вам необходимо скопировать и вставить этот код в личную книгу макросов.
Вот некоторые из писем, которые вы можете использовать для быстрого удаления текста до или после определенного символа в Excel.
Если это простая одноразовая вещь, вы можете сделать это с помощью функции поиска и замены. что, если это немного сложнее, тогда вам нужно использовать комбинацию встроенных формул Excel или даже создать свою собственную формулу с помощью VBA.
Как удалить текст до или после определенного символа в Excel
При работе с листами Microsoft Excel вам может потребоваться удалить первые несколько символов, последние несколько символов или и то, и другое из текста. Удаление первых нескольких символов из столбца текстов полезно, когда вам нужно удалить заголовки (например, доктор, лейтенант). Точно так же удаление последних нескольких символов может быть полезно при удалении телефонных номеров после имен. В этой статье пробелы считаются символами.
Удалить текст до или после определенного символа в Excel
Этот пост покажет вам, как удалить первые или последние несколько символов или определенные символы позиции из текста в Microsoft Excel. Мы рассмотрим следующие темы:
Удалите первые несколько символов из столбца текстов
Синтаксис для удаления первых нескольких символов из столбца текстов:
Где — это расположение первой ячейки в столбце с полными текстами. — это количество символов, которое вы собираетесь удалить с левой стороны текста.
Например. Если у нас есть столбец с полным текстом от ячейки A3 до A7 и нам нужен текст после удаления первых 2 символов в столбце C, формула будет следующей:
Запишите эту формулу в ячейку C3. Нажмите Enter, и он отобразит текст в ячейке A3 без первых двух символов в ячейке C3. Щелкните в любом месте за пределами ячейки C3, а затем вернитесь в ячейку C3, чтобы выделить параметр «Заливка». Теперь перетащите формулу в ячейку C7. Это даст тексты без первых двух символов в столбце C для исходных текстов в столбцах A.
Удалите последние несколько символов из столбца текстов
Синтаксис для удаления последних нескольких символов из столбца текстов:
В этой формуле — это расположение первой ячейки в столбце с полными текстами. — это количество символов, которое вы собираетесь удалить с правой стороны текста.
Например. Давайте рассмотрим случай, когда у нас есть столбец с полными текстами от ячейки A3 до A7, и нам нужен текст после удаления последних 9 символов в столбце D; формула будет такой:
Теперь запишите эту формулу в ячейку D3. Нажмите Enter, и он отобразит текст в ячейке A3 без последних 9 символов в ячейке D3. Щелкните в любом месте за пределами ячейки D3, а затем вернитесь в ячейку D3, чтобы выделить параметр «Заливка». Теперь перетащите формулу в ячейку D7. Это даст тексты без последних 9 символов в столбце D для исходных текстов в столбцах A.
Удалите как первые несколько, так и последние несколько символов из столбца текстов
Если вы собираетесь удалить как первые несколько, так и последние несколько символов из столбца текстов, синтаксис формулы будет следующим:
Например. Если у нас есть столбец с полными текстами в столбце A от ячейки A3 до A7 и нам нужны тексты без первых 2 символов и последних 9 символов в столбце E от ячейки E3 до E7, формула будет иметь следующий вид:
Запишите эту формулу в ячейку E3. Нажмите Enter, и он отобразит текст в ячейке A3 без первых 2 и последних 9 символов в ячейке E3. Щелкните в любом месте за пределами ячейки E3, а затем вернитесь в ячейку E3, чтобы выделить параметр «Заливка». Теперь перетащите формулу в ячейку E7. Это даст тексты без первых 2 и последних 9 символов в столбце E для исходных текстов в столбцах A.
Я надеюсь, что этот пост поможет вам удалить первые или последние несколько символов или определенные символы позиции из текста в Microsoft Excel.
.
Как удалить символы После знака в Excel
Возможность удаления знаков после символа или текста в Excel — это важная функция при работе с таблицами, позволяющая очистить нужные элементы от лишней информации.
Как удалить знаки после символа в Excel
Для этого потребуется использовать формулу:
Также можно комбинировать этот способ с очисткой данных «До».
Удаление знака после запятой в Эксель
Выполнить эту задачу можно при помощи конструкции:
После пробела в Excel
Сделать это можно через формулу:
Подписывайтесь на наш канал в Яндекс.Дзен
Подписывайтесь на наши социальные сети
Вам также будет интересно
Комментарии 2
А если после запятой надо оставить только (например) три знака в числовом формате, то надо так: =ПСТР(А3;1;ПОИСК(«,»;А3)+3)
При этом, (опять же например)
исходное значение: 23,558668; готовый результат: 23,558
Добрый день, спасибо за обратную связь, да думаю данная форума будет полезна многим. Также если речь идет о цифрах, как в примере можно воспользоваться округлением.