Как удалить часть текста из ячейки в Excel

Обновлено: 17.06.2024

Как я могу уменьшить это число, используя формулы, чтобы сделать 3604000000157477 равным 3604157477 первых 4 первых и последних 6 цифр.

Сохраните его в столбце C:\Users\jaja\Downloads\aa.txt. Necesito eliminar todos los carácteres que están después del ultimo slash

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

Если число или символ различаются, вы можете использовать пример ниже (мне нужно было удалить последний символ):
=PART(A1;1;(LENGTH(A1)-1))

Я ХОЧУ ОБРЕЗАТЬ ДЛИНУ ТЕКСТА, НАПРИМЕР, У МЕНЯ ЕСТЬ АДРЕС СО ДЛИНОЙ НЕКОТОРОГО СТОЛБЦА 10 СИМВОЛОВ И НЕКОТОРОГО СТОЛБЦА 20 СИМВОЛОВ НЕКОТОРЫЕ 15 СИМВОЛОВ В ЭТОМ СЛУЧАЕ Я ХОЧУ ОБРЕЗАТЬ КАКОЙ ТЕКСТ СТОЛБЦА БОЛЬШЕ 15 Я ХОЧУ ОБРЕЗАТЬ ЭТОТ ТЕКСТ СТОЛБЦА ДО 15 СИМВОЛОВ, НЕ ОБРЕЗАЙТЕ ДРУГОЙ ТЕКСТ КОЛОННЫ


ПОМОГИТЕ КТО-НИБУДЬ

Я выдал ошибку функции, когда применяю формулу к ячейке. Через 2 часа я пытаюсь снова использовать (;) вместо (,) и получаю положительный результат. В результате;
=removelastx(E4,3) (не работает)
=removelastx(E4;3) (работает)

Это действительно очень помогает мне в моей повседневной работе.

Заранее спасибо.


Почему это не работает?

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

Всем привет!
Я пытаюсь удалить инициал в поле, содержащем имя человека. Пример:

Taylor Jeremy D. В этом примере я пытаюсь удалить букву D и просто оставить там фамилию и имя.

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

Есть ли какие-нибудь идеи, как получить непротиворечивый набор данных, исключающий первоначальный?

Попробуйте, это решает именно так, как вы хотите.

A1 – Тейлор Джереми D или без D, как хотите

Просто чтобы сообщить вам, что я решил вашу проблему, надеюсь, я не опоздал.

=SI((SIERREUR((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))/(TROUVE(" ";R9;((TROUVE(" "; R9;1))+1)));0))=1;ГОШ(R9;((НАХОД(" ";R9;((НАХОД(" ";R9;1))+1)))-1) );R9)

Cell R9 — это ваш Тейлор Джереми D. Попробуйте с D и без него, и вы увидите, что все работает нормально

Если текстовое имя (Taylor Jeremy D) находится в ячейке A1, вы можете использовать приведенную ниже функцию для получения результата (удалить фамилию):
=LEFT(A1,FIND(" ",A1,1) )&НАЛЕВО(ПРАВО(A1,ДЛСТР(A1)-A2),НАЙТИ(" ",ПРАВО(A1,ДЛСТР(A1)-A2))-1)

Также можно выполнить с помощью приведенной ниже строки, которая проще и не требует формул в нескольких ячейках. Следуя тому же предположению, что данные находятся в A1, а начальный символ всегда равен 1. В функцию LEN добавлена ​​функция TRIM на случай, если в данных есть невидимые конечные пробелы. Функция TRIM, добавленная в начало формулы, удалит остаточный интервал после выполнения функции LEFT.

Или, если вам не нравится ведущая функция TRIM, вы можете вычесть 2 символа из функции LEN вместо одного. Я предпочитаю маршрут TRIM, если между именем и первым инициалом более 1 пробела — необходимо учитывать несогласованный ввод данных.

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


Допустим, ячейка A1 содержит текстовую строку "ABCDEF"

Ячейка A2 содержит недопустимую часть строки, например "CD"


После удаления недопустимой текстовой строки ячейка A1 будет выглядеть так: "ABEF".

Как это сделать? Заранее спасибо!

Эй, вы можете сделать это с помощью комбинации функций:

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

удалить все символы, кроме символов после определенного символа. =ВПРАВО(G2,ДЛСТР(G2)-НАЙТИ("[",G2,4)). Я делал это для состояний, поэтому мне пришлось преобразовать состояние в уникальный символ, чтобы уменьшить его до 1 символа, а не «NM».

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

Пример: AS SPLSH (CW) 100 мл (112)

убрать последний (сумма чисел)

Подскажите, пожалуйста, как получить конкретные данные в ячейке
Пример
Столбец A
IP-bs-спальня
Gh-ff-kitchen
IP-gh -главная дверь
.
.
Теперь я хочу, чтобы в столбце B было только местоположение, это спальня, кухня, поэтому я хочу удалить все остальное, кроме местоположения

Вы можете использовать функцию "Найти и заменить" (Ctrl + H). Найдите что: "*-" и Замените на: оставьте это поле пустым. Затем нажмите заменить.

Привет, я хочу удалить все после первого дефиса (-) и оставить только буквы.
Как мне это сделать?
пример 123-ABC-BDER-AKD

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

Я хочу защитить/снять защиту паролем с «листа 1» из 300 книг в одной папке.
Например: в защищенном паролем листе 1 можно редактировать только определенные ячейки. Вам нужно снять защиту паролем, чтобы редактировать все ячейки.

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

После некоторого редактирования мы должны защитить лист и сохранить его.
Это происходит для 300 рабочих книг на листе1.
И так каждый раз

[b]Открытие книги -> Просмотр->Снять защиту листа->Введите пароль.[/b]
После изменений
[b]Просмотр->Защитить лист->Введите пароль->Сохранить< бр />[/b]

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

Я хочу удалить выбранные символы из моей книги Excel, как мне это сделать? есть ли какие-нибудь формулы для этого?

очень ценим ваш немедленный совет.

Здравствуйте.
Я хотел бы удалить последние символы с помощью поиска и замены, но с помощью Excel Visual Basic (с командами).
Я хотел бы создать файл, который загружает существующий файл и создает новый с отредактированным столбцом.

Любая помощь будет оценена по достоинству.

просто найдите и замените. Вы можете легко получить доступ к функции, используя CTRL+F.
Вам нужно искать "*" и заменять на ""
в основном везде, где Excel находит *, он заменяет его пробелом.

Спасибо за очень простой и понятный подход. Оно работает. Да благословит вас Господь, мой дорогой Анонимный Товарищ!
Мне потребовалось 2 дня мучительных поисков в рунете и немало мозговых клеток, чтобы найти функции Excel на русском языке с пояснительными примечаниями, но все было напрасно только потому, что формулы отказывают работать!! Похоже, что доступность и полное нежелание делиться общими практиками с моими российскими коллегами, которые только делают вид, что знают о продуктах Майкрософт херню все, до сих пор является большой проблемой!!
Нет, серьезно, я вам должен!
Спасибо!

В этом руководстве мы рассмотрим наиболее распространенные случаи удаления символов в Excel. Хотите удалить определенный текст из нескольких ячеек? Или, может быть, удалить первый или последний символ в строке? Или, возможно, удалить только определенное вхождение данного символа? Какой бы ни была ваша задача, вы найдете для нее не одно решение!

Как удалить определенный символ в Excel

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

Удалить символ из нескольких ячеек с помощью функции «Найти и заменить»

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

  1. Выберите диапазон ячеек, из которых вы хотите удалить определенный символ.
  2. Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
  3. В поле Найти введите символ.
  4. Оставьте поле Заменитьна пустым.
  5. Нажмите Заменить все.

Символ удален из всех выбранных ячеек .

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

  • Этот метод удаляет символы непосредственно из ваших исходных данных. Если результат отличается от ожидаемого, нажмите Ctrl + Z, чтобы отменить изменение и вернуть исходные данные.
  • Если вы имеете дело с буквенными символами, где регистр букв имеет значение, нажмите Параметры, чтобы развернуть диалоговое окно Найти и заменить, а затем установите флажок Учитывать регистр< /em> для выполнения поиска с учетом регистра.

Удалить определенный символ из строки с помощью формулы

Чтобы удалить определенный символ из любой позиции строки, используйте эту общую формулу ПОДСТАВКИ:

В нашем случае формула принимает следующий вид:

Удаление определенного символа из строки

Введите приведенную выше формулу в ячейку B2, скопируйте ее в ячейку B6, и вы получите следующий результат:

Обратите внимание, что ПОДСТАВИТЬ всегда возвращает текстовую строку, даже если результат содержит только числа, как в ячейках B2 и B3 (обратите внимание на выравнивание по левому краю, типичное для текстовых значений).

Если вы хотите, чтобы результатом было число, оберните приведенную выше формулу в функцию VALUE следующим образом:

Или вы можете выполнить некоторую математическую операцию, которая не изменит исходное значение, например добавить 0 или умножить на 1:

Удалить сразу несколько символов

Чтобы удалить несколько символов с помощью одной формулы, просто вставьте функции ПОДСТАВКИ одну в другую.

  • Функция ЗАМЕНИТЬ чувствительна к регистру, имейте это в виду при работе с буквами.
  • Если вы хотите, чтобы результаты были значениями, независимыми от исходных строк, используйте параметр Специальная вставка — значения, чтобы заменить формулы их значениями.
  • В ситуации, когда нужно удалить много разных символов, намного удобнее использовать пользовательскую функцию RemoveChars, определенную в LAMBDA.

Как удалить определенный текст из ячейки Excel

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

Удалить текст из нескольких ячеек

Чтобы удалить определенный текст из каждой ячейки в выбранном диапазоне, нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить, а затем:

Часть текста удалена из выделенного клетки». ширина=

При нажатии кнопки Заменить все все замены будут выполнены за один раз:

Удалить определенный текст из ячейки с помощью формулы

Чтобы удалить часть текстовой строки, вы снова используете функцию ПОДСТАВИТЬ в ее основной форме:

Например, чтобы удалить подстроку "mailto:" из ячейки A2, используйте следующую формулу:

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

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

Как удалить N-й экземпляр определенного символа

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

Чтобы убрать первую косую черту в A2, используйте следующую формулу:

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

Формула для удаления экземпляра определенный символ

=ПОДСТАВИТЬ(A2, "/", "", 2)

Как удалить первый символ

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

В переводе на человеческий язык формула гласит: в указанной ячейке взять 1 символ (num_chars) с 1-й позиции (start_num) и заменить его пустой строкой ("" ).

Здесь мы вычитаем 1 символ из общей длины строки, которая вычисляется функцией ДЛСТР. Разница передается RIGHT для извлечения этого количества символов с конца.

Например, чтобы удалить первый символ из A2, формулы выглядят следующим образом:

=ВПРАВО(A2, ДЛСТР(A2) - 1)

Удаление первого символа из строка

На снимке экрана ниже показана формула REPLACE. Формула RIGHT LEN даст точно такие же результаты.

Чтобы удалить любые n символов в начале строки, см. статью Удаление символов слева в Excel.

Как удалить последний символ

Чтобы удалить последний символ из конца строки, используйте следующую формулу:

Логика аналогична формуле RIGHT LEN из предыдущего примера:

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

Например, вы можете удалить последний символ из A2, используя следующую формулу:

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

=ЛЕВО(A2, ДЛСТР(A2) - 1)

Чтобы удалить любые n символов с конца строки, см. статью Удаление символов справа в Excel.

Удалить текст после определенного символа

Чтобы удалить все после заданного символа, используйте общую формулу:

Логика довольно проста: функция ПОИСК вычисляет позицию указанного символа и передает ее функции ВЛЕВО, которая возвращает соответствующее количество символов с самого начала. Чтобы не выводить сам разделитель, мы вычитаем 1 из результата ПОИСКА.

Например, чтобы удалить текст после двоеточия (:), используйте формулу в B2:

Формула для удаления текста после определенного символа

=LEFT(A2, ПОИСК(":", A2) -1)

Как удалить пробелы до и после текста в Excel

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

Формула удаления лишних пробелов из ячеек проста:

Где A2 – исходная текстовая строка.

Удаление пробелов перед и после текста

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

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

Чтобы избавиться от них, преобразуйте неразрывные пробелы в обычные с помощью ЗАМЕНЫ:

Где 160 — это кодовый номер неразрывного пробела ( ).

Кроме того, используйте функцию CLEAN для удаления непечатаемых символов:

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

Дополнительную информацию см. в разделе Как удалить пробелы в Excel.

Удаление символов в Excel с помощью Flash Fill

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

Допустим, у вас есть имя и адрес электронной почты в одной ячейке, разделенные запятой. Вы хотите удалить все после запятой (включая саму запятую). Чтобы это сделать, выполните следующие действия:

  1. Вставьте пустой столбец справа от исходных данных.
  2. В первой ячейке вновь добавленного столбца введите значение, которое хотите сохранить (в нашем случае имя).
  3. Начните вводить значение в следующей ячейке. Как только Excel определит шаблон, он покажет предварительный просмотр данных, которые необходимо заполнить в ячейках ниже по тому же шаблону.
  4. Нажмите клавишу Enter, чтобы принять предварительный просмотр.

Удаление части текста с быстрой заливкой

Готово!

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

Специальные инструменты для удаления символов или текста в Excel

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

На вкладке Данные Ablebits в группе Текст есть три варианта удаления символов из ячеек Excel:

  • Определенные символы и подстроки
  • Символы в определенной позиции
  • Повторяющиеся символы

Специальные инструменты для удаления символов и текст в Excel

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

  1. Нажмите Удалить >Удалить символы.
  2. Выберите наиболее подходящий вариант.
  3. Установите или снимите флажок С учетом регистра.
  4. Нажмите Удалить.

Ниже приведены несколько примеров, которые охватывают наиболее типичные сценарии.

Удалить определенный символ

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

Удалить сразу несколько символов из нескольких ячеек

Например, мы удаляем все вхождения заглавных букв A и B из диапазона A2:A4:

Удалить предопределенный набор символов

Чтобы удалить определенный набор символов, выберите Удалить наборы символов, а затем выберите один из следующих вариантов:

Удалить предопределенный набор символов.

  • Непечатаемые символы: удаляется любой из первых 32 символов в 7-битном наборе ASCII (кодовые значения от 0 до 31), включая символ табуляции, разрыв строки и т. д.< /li>
  • Текстовые символы – удаляет текст и сохраняет числа.
  • Цифровые символы – удаляет числа из буквенно-цифровых строк.
  • Символы и знаки препинания – удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т. д.

Удалить часть текста

Чтобы удалить часть строки, выберите параметр Удалить подстроку.

Удаление части строки

Например, чтобы извлечь имена пользователей из адресов Gmail, мы удаляем подстроку "@gmail.com":

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

 Удалить текст путем сопоставления в Excel

Чтобы найти и удалить определенный текст в Excel, мы можем использовать функцию ПОДСТАВИТЬ.

Допустим, в тексте есть знаки дефиса («-»), и мы хотим их удалить. Мы можем сделать это с помощью функции ПОДСТАВИТЬ, используя следующую формулу:

 Удалить текст в соответствии с примером

В этом примере мы предоставили ссылку на ячейку (B3), содержимое, которое мы хотим удалить из текста ("-"), а для замещающего текста - пустую строку ("").

Функция ПОДСТАВИТЬ заменяет все дефисы "-" ничем.

Мы можем удалить любой другой символ или текст из основной текстовой строки, заменив дефис sing ("-") в формуле этим символом или текстом.

Удалить более одного персонажа

Мы также можем удалить более одного символа из текста, вложив функции ПОДСТАВКИ.

Допустим, у нас есть скобки и дефисы в тексте, и мы хотим удалить их оба. Мы вложим функции ПОДСТАВКИ три раза, чтобы получить желаемый результат, например:

Удалить несколько символов

Сначала Excel удалит из текста символ «(», затем перейдет к внешней вложенной функции ПОДСТАВИТЬ и удалит «)», а затем перейдет к следующей внешней функции ПОДСТАВИТЬ и удалит знак дефиса («- ").

Удалить текст в Google Таблицах

Формула для удаления текста путем сопоставления содержимого работает точно так же в Google Таблицах, как и в Excel:

Удалить текст путем сопоставления в Google Таблицах

Практический лист Excel

практика Excel рабочий лист» ширина = «323» высота = «266» /><br /></p>
<p>Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!</p>
<p>При работе с текстовыми данными в Excel может возникнуть необходимость удалить текст до или после определенного символа или текстовой строки.</p>
<p>Например, если у вас есть данные об именах и обозначениях людей, вы можете удалить обозначение после запятой и оставить только имя (или наоборот, если вы сохраняете обозначение и удаляете имя).</ р>
<p><img class=

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

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

Давайте начнем с нескольких простых примеров.

Это руководство охватывает:

Удалить текст после символа с помощью поиска и замены

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

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

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

Ниже приведены шаги для этого:

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

Набор данных, в котором удален текст после запятой

Поскольку это заменяет текст из выбранных ячеек, рекомендуется скопировать текст в другой столбец, а затем выполнить эту операцию поиска и замены или создать резервную копию ваших данных, чтобы исходные данные остались нетронутыми< /p>

Как это работает?

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

Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.

Это связано с тем, что знак звездочки (*) соответствует всей текстовой строке, следующей за запятой.

Поэтому, когда вы нажимаете кнопку "Заменить все", запятая и весь последующий текст заменяются.

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

Если вы хотите удалить все символы перед запятой, измените запись в поле поиска, указав звездочку перед запятой (* вместо ,*)

Удалить текст с помощью формул

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

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

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

Ниже приведена формула для этого:

Формула для удаления запятой после запятой

Приведенная выше формула использует функцию НАЙТИ, чтобы найти позицию запятой в ячейке.

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

Это был простой сценарий.

Возьмем более сложный вариант.

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

Набор данных с двумя запятыми

Вот формула, которая это сделает:

Формула для удаления текста после второй запятой

Поскольку в этом наборе данных несколько запятых, я не могу использовать функцию НАЙТИ, чтобы получить позицию первой запятой и извлечь все, что находится слева от нее.

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

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

Эта позиция восклицательного знака используется в функции LEFT для извлечения всего до второй запятой.

Но что, если в наборе данных имеется непостоянное количество запятых.

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

Набор данных с несколькими запятыми

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

Ниже приведена формула, которая это сделает

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

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

Когда я вычитаю эти два значения, я получаю общее количество запятых в ячейке.

Итак, это дало бы мне 3 для ячейки A2 и 2 для ячейки A4.

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

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

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

Удалить текст с помощью быстрой заливки

Мгновенная заливка – это инструмент, впервые появившийся в Excel 2013 и доступный во всех последующих версиях.

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

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

Позвольте мне показать вам это на примере.

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

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

Вот как это сделать с помощью Flash Fill:

Вышеуказанные шаги дадут вам результат, показанный ниже:

Flash Fill result

Вы также можете использовать сочетание клавиш "Быстрая заливка" Control + E после выбора ячеек в столбце результатов (столбец B в нашем примере)

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

Поэтому убедитесь, что вы перепроверили результаты Flash Fill

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

Удалить текст с помощью VBA (пользовательская функция)

Вся концепция удаления текста до или после определенного символа зависит от нахождения положения этого символа.

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

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

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

Ниже приведен код VBA, который можно использовать для создания пользовательской функции в Excel:

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

Эта функция принимает 2 аргумента:

  1. Ссылка на ячейку, для которой вы хотите найти последнее вхождение определенного символа или текстовой строки.
  2. Символ или текстовая строка, положение которой необходимо найти

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

Ниже приведена формула, которая это сделает:

Пользовательская функция VBA для удаления текста после определенного символа

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

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

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

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

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

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