Возможно, при работе с Microsoft Excel вы пытались сразу удалить определенное слово из выбранного диапазона. Фактически, Excel предоставляет функцию «Найти и заменить», чтобы легко решить эту проблему.
Массовое удаление определенного слова с помощью функции поиска и замены
Массовое удаление определенного слова с помощью функции поиска и замены
Функция «Найти и заменить» помогает быстро массово удалить определенное слово в диапазоне. Пожалуйста, сделайте следующее.
1. Выберите диапазон с конкретным словом, которое нужно массово удалить, нажмите Ctrl + F , чтобы открыть Найти и заменить диалоговое окно.
2. В дебюте Найти и заменить диалога, нажмите Замените на вкладке введите конкретное слово, которое вы удалите в Найти то, что коробка, держи Заменить пустое поле, а затем щелкните значок Заменить все кнопка. Смотрите скриншот:
3. Затем Microsoft Excel Появится диалоговое окно, в котором сообщается, сколько произведенных замен. Пожалуйста, нажмите на OK кнопку, а затем закройте Найти и заменить диалоговое окно.
После этого вы увидите, что слова «версия» сразу удаляются из выбранного диапазона.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (3)
Оценок пока нет. Оцените первым!
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующие основные формулы для удаления определенного текста из ячеек в Excel:
Способ 1: удалить один конкретный текст
=SUBSTITUTE( A1 ,"text1","")
Эта конкретная формула удаляет «text1» из ячейки A1 .
Способ 2: удалить несколько определенных текстов
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1 ,"text1",""),"text2",""),"text3","")
Эта конкретная формула удаляет «текст1», «текст2» и «текст3» из ячейки A1 .
В следующих примерах показано, как использовать каждый метод на практике.
Пример 1: удалить один конкретный текст
Предположим, у нас есть следующий столбец в Excel, в котором показаны позиции 15 разных баскетболистов:
Мы можем использовать следующую формулу, чтобы удалить «r» из каждого имени позиции:
=SUBSTITUTE( A2 ,"r","")
Мы можем ввести эту формулу в ячейку B2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце B:
Обратите внимание, что буква «r» была удалена из всех названий позиций.
Пример 2. Удаление нескольких определенных текстов
Предположим, у нас есть следующий столбец в Excel, который показывает имя пользователя для 10 разных людей на каком-то веб-сайте:
Предположим, мы хотим удалить следующий конкретный текст из каждого имени пользователя:
- тире ( — )
- восклицательные знаки ( ! )
- число 5 ( 5 )
Для этого мы можем использовать следующую формулу:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A2 ,"-",""),"!",""),"5","")
Мы можем ввести эту формулу в ячейку B2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце B:
Обратите внимание, что все «-», «!» и «5» были удалены из каждого имени пользователя.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Excel: как проверить, содержит ли ячейка частичный текст
Excel: как проверить, содержит ли ячейка текст из списка
Excel: как рассчитать среднее значение, если ячейка содержит текст
Excel: как подсчитать частоту текста
- Удалить первое слово из ячейки: составляем формулу
- Находим позицию первого пробела
- Заменяем фрагмент до первого пробела на пустоту
- Удалить первое слово в ячейках в 2 клика
Иногда при работе с Excel возникает необходимость удалить первое слово или же несколько слов в начале фраз в ячейке. Для решения задачи вам потребуется объединить несколько функций программы.
Задача удаления первого слова является частным случаем более общей (см. статью): «Как в Excel удалить текст до определенного символа».
Удалить первое слово из ячейки: составляем формулу
Технически, первое слово — это все символы в ячейке до первого пробела или до конца строки, если слово в ячейке всего одно. Как его удалить?
Находим позицию первого пробела
Есть 2 варианта формул, оба требуют сначала найти позицию первого символа пробела в строке, это делается с помощью функции ПОИСК или функции НАЙТИ. Обе ищут позицию в ячейке первого вхождения значения, указанного в первом аргументе, просматривая ячейку (второй аргумент) слева направо.
А что, если слово в строке одно, и пробела там нет? Обе функции вернут ошибку. Но её можно обойти, производя поиск не в самой ячейке, а в тексте ячейки с присоединенным к нему справа пробелом (&” “). Смотрите пример:
Может быть и другая неприятная ситуация — в начале ячейке может быть лишний пробел, что может нам испортить решение задачи. Если есть такая вероятность, рекомендуется сначала удалить лишние пробелы.
Заменяем фрагмент до первого пробела на пустоту
Теперь, когда нам известна позиция первого символа идущего после слова, ею можно воспользоваться, чтобы заменить слово на пустоту, что эквивалентно его удалению. Пустота обозначается кавычками, между которыми ничего нет (“”).
Нам потребуется функция «ЗАМЕНИТЬ». Ей мы указываем ячейку, в которой производится замена, начиная с какой позиции, сколько символов, и на что заменим этот промежуток. Обратите внимание – функция не выдает ошибку в последней строке, несмотря на то, что в ячейке A8 меньше символов, чем число, которое мы ей указываем в B4.
Чтобы не создавать два столбца, можно совместить формулы в одну составную. Просто заменим в формулах третьего столбца ссылки на формулы второго столбца самими этими формулами. Так будет выглядеть итоговая составная формула удаления первого слова в ячейке для первой строки с данными:
=ЗАМЕНИТЬ(A2;1;ПОИСК(" ";A2&" ");"")
А ниже процесс, как происходит её создание:
Удалить первое слово в ячейках в 2 клика
Как видно, формулы выше не очень просты, и требуется время, даже чтобы просто их написать. Помимо этого, формулы требуют создания дополнительного столбца (или двух). Надстройка !SEMTools поможет упростить и ускорить решение задачи. Ниже показан пример удаления первых слов в ячейках, прямо на месте:
В примере ниже удаляем уже 2 слова в начале всех ячеек столбца:
Аналогично решается задача для трёх и более слов — можно использовать пункт «Первые N», чтобы указать количество удаляемых слов самостоятельно.
Хотите так же быстро удалять слова в Excel?
!SEMTools поможет автоматизировать процессы и решит эту и другие задачи буквально за пару кликов!
Как в экселе стереть часть текста из каждой строки одной колонки?
Смотрите такжечтобы убрать двойные часть текста изЕсли же нужно слово есть не устроилаУбрались пробелы -
: Слишком много всяких: Спасибо! Хороший вариант. в том, чтобы
«членов», не знаю, — но с
: Все отлично и формулу: скобки, то можно, Спасибо, Вы самый
=(ЛЕВСИМВ (A1;ДЛСТР (A1-4)))*1Например, есть колонка с пробелы
1-го. Нужно из именно формулами получить в каждой ячейке.Алексей777 перед «шт» их отступлений от логичного Нужен вариант, чтобы найти слова Х что делать, если
учетом наличия или в Вашем решении,=ПСТР(A2;1;НАЙТИ(«(«;A2)-1)
так: быстрый(ая)!
Золотая рыбка ценами, формата «ххххххkrestsoft
текстов ячеек 1
новый список вMcrosoft Excel: Здраствуйте, должно быть три:
написания:
вычисления проходили в и Y в их будет 20-30… отсутствия пробелов,-
— Тему можно—=СЖПРОБЕЛЫ(ЛЕВСИМВ(A2;ПОИСК(«(«;A2)-1))Eugene Kornev: Выделяешь правой мышкой
руб» из каждой:
столбца вычесть текст другом месте -Microsoftt WordУ меня кЗАМЕНИТЬ(A3;ПОИСК(«шт»;A3&»ПППшт»)-3;5;)- дата с одной ячейке. Т.к.
Как удалить заданную часть текста из ячейки? (Формулы/Formulas)
тексте S иНеужто все вот
и это все закрывать !89743Владимир: Сразу хочу сказать, весь столбик -формат ячейки нужно убрать
Спасибо! Очень оперативно получилось из ячеек 2 то приложите файлMicrosoft Project
Вам следующий вопрос.Vanya7819 точкой или с
ячеек с текстом удалить. Как Х так прописывать ?
удаляется.СПАСИБО ВСЕМ Иvikttur: Тестируйте.. что правила читал,
excelworld.ru
Удаление части текста из ячеек диапазона
ячеек-Число — числовой «руб» чтобы колонкаАlехМ столбца. с примером вашихExchangeУ меня есть: Большое СПАСИБО за
запятой; будет тоже много.
найти и удалить — попробую…vikttur ДАЖЕ ТЕМ, КТО: Владимир, это жеwebley три часа искал- Vlad осталась только с
: krestsoft, На другомПример того, что данных и получаемым
Microsoft Windows Server Экселевский файл в ответы!!!- после запятой Этим способом нужно я понял. А
Guest: 1. Для небольшого НЕ УЧАСТВОВАЛ, НО микроскопом по гвоздям,: подрезаются индексы домов
на форуме, ответа: Ampersand написал (а) цифрами.
форуме отпишитесь. Там
должно получиться - результатом. CAL котором используются тэги
CHEVRYACHOK
перед «прих.» пробел будет по каждой
как найти и: vikttur’у перечня удаляемых подойдет ЗДЕСЬ БЫВАЛ !
даже иногда (д.55А) в строках 8
не нашел, -прошу хорошую формулу, ноAmpersand ответ даже раньше во вложении.Если нужно именноRemote Desctop
на изображения, но: Доброе утро! Подскажите есть или нет; ячейке с текстом удалить из текстаПростите, копирнул в способ, показанный вwww по пальцам :) и 9
не обвинять в вот эта немного: есть очень хорошая был.Казанский «заменить данные вВ результате должно
они в тексте можно ли удалить
- цифры перед эти вычисления проводить X и Y
пользователи Вас, - файле.
: текст по столбцам
Eugene Kornev, почему
Eugene Kornev
лени. лучше и короче формулаkrestsoft: krestsoft, для 2
ячейках по месту» получится без слова не нужны. Они часть текста в
«шт.» одно- или отдельно. Надеюсь, не одновременно? Это возможно? вижу пустое поле,2. формула массива,
— в качестве отвергаете формулу webley?: АААгромное СПАААСИБО !
Диапазон выгружается из
=СИМВОЛ (136)&—ЛЕВСИМВ (A1;ДЛСТР=СИМВОЛ (36)&ЗАМЕНИТЬ (A1;(ДЛСТР
: Ок, мощно ваше строки, и автозаполнить — то формулами Microsoft: выглядят следующим образом:
ячейке? В моем двухзначные. запутанно написал) Буду благодарен любым хотел ответить именно которая будет тормозить разделителя ( Потому что неСейчас пойду напишу 1с. (A1-3))
(A1)-3);3;»») сообщение 666 по по столбцу =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;B2;»»)) здесь никак, толькоExcel
Маска для удаления случае это -Надо начала данныеAlexM
советам. Вам, а вышла расчеты («как яЕвгений Корнев
в файле? служебку, чтобы нашихТребуется удалить все,Екатерина ткаченкоAndrey aksenov счёту стало )
vikttur обрабатывать макросом…Word , вмести с КОЭФ. К ПОЗИЦИИ: чуть причесать, а: Можно функцией пользователя.За ранее спасибо! незадача… уже говорил, диапазон: Прошу у СообществаМожно и без «умников» выпороли, т.к. что стоит после
: Выделить колонку, зажатой: там есть функцияКазанский: ФункцияsensorriaProject этими значениями.
и все что
так, только почти200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function YDirect(iText As String,P.S. Прикрепляю файлИсправлюсь.
большой») прощения, но возникла
СЖПРОБЕЛЫ, но в месяц не могли собственно адреса, т.е. левой кнопкой мыши, — «найти и
: И зачем тогдаПОДСТАВИТЬ: Спасибо, получилось. АExchangeЯчеек 1500 шт,
ниже него в
то, что хотелось. Words As Range) примера.vikttur3. Макрос. одна проблема, которую
некоторых случаях останется придумать, как это после цифры № затем нажать правую заменить» надо было по
справится. как пробел удалить?Windows Server CAL в части из
каждой ячейке.Владимир As String
ShAM: На 20-30 удаляемыхwebley я не учел пробел в конце сделать в 1с. дома, учитывая то, кнопку мыши иHelloworld
всему рунету егоSanja С помощью замены
Remote Desctop них содержится информация,
Пример в файле.: ..Потом отдельно формулуFor Each iWords: Не вижу. — лучше макрос.: если вы выложите
ранне.
строки. (ШУТКА) что в некоторых выбрать в списке
: Запихни «руб» в публиковать?: UDF. Я понял, не получается. ЯИскала, гуглила, но ссылки на картинки, Заранее спасибо!
напишите, с последней In Wordsydirect Ждите спецов. несколько записей, которыеКак решить ту
planetaexcel.ru
Удаление части текста из ячейки (Формулы/Formulas)
Удаляемое в отдельныйЕсть еще одна местах имеется пробел, «Очистить содержимое» другую колонку.krestsoft что в ячейке выделяю столбец нужный, так решения и которые необходимо удалить._Boroda_ запятой поработайте.iText = Replace(iText,: Ой:)Eugene Kornev некорректно обрабатываются указанными же первую задачу столбец и сортируйте задача с этим а иногда -krestsoftЕсли у тебя
: Привычка
может быть любое ставлю пробел, заменить
не нашла, естьКак это можно
: Формулой где-нибудь сбоку—
iWords, «»)ShAM
: Вот попробовал формулу формулами, решение будет с удалением части на здоровье: же диапазоном : нет.: Вопрос крайне тривиальный. иксель 2007й, тоЮрий М слово из второго на «пусто» оставляю информация об удаление
сделать?200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЛЕВСИМВ(D5;ПОИСК(«КОЭФ. К ПОЗИЦИИ:»;D5)-2)
48500Next: Может, так? из Вашего файла
найдено гораздо быстрее текта, не используя=ПСТР(A2;ПОИСК(«(«;A2);20) как сделать, чтобыПосле обработки пробелаЕсть 2 столбца, это делается так:: krestsoft, размещаете тему столбца, а не и ничего не дубликатов, есть удалениеСпасибо.или выделяем столбец
viktturYDirect = WorksheetFunction.Trim(iText)
ydirect на другом диапазонеЕвгений Корнев
в качестве критерияВладимир
сортировать ячейки по в конце текста
в ячейках 1-го
Выделяешь столбец, затем
на нескольких формах
только то, которое получается. первого, центрального илиКазанский D, жмем Контрл+h,: =ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;ПОИСК(«шт»;A3&» шт»)-3;5;);ПОИСК(«прих»;A3);10;);»*»;);»,»;)End Function
excelworld.ru
Как формулой удалить часть текста в ячейках?
: Спасибо за идею!:) — смю в: Вот пример таких символ «(» -: 20821 тем частям текста,
не должно быть.
текст, в ячейках вкладка Данные ->
- в ячейке справаPelena последнего слова в
: Ctrl+H, найти: заменить в «Найти» пишемДля показанных данных
ydirect Да, результат нужен прицепе, что получилось
адресов, которые с как я ужеEugene Kornev
которые потом будемУдаляемые части текста 2-го Текст по столбцам
информируйте Function СЛОВАМИНУС(iTxt, iTbl: А Вы не ячейке, а вот
на: пусто.
«?КОЭФ. К ПОЗИЦИИ*»
отрабатывает корректно.:
такой. Одно условие: из однокоренных слов.
потерей текста в говорил, диапазон большой,: Eugene Kornev, почему
удалять — это могут быть вобязательно
-> Далее. Выбираешьоб этом. As Range) arr просто пробел с удаление конкретного ненужного
Алексей777 без кавычек (именноДля полного совпаденияAlexM
список слов неизвестен,Eugene Kornev скобках неправильно влияют
и, к сожалению,
отвергаете формулу webley? надо для другого количестве нескольких десятков,
planetaexcel.ru
Удаление части текста в ячейке (Макросы/Sub)
есть часть текста разделитель пробел, жмешьvikttur = iTbl.Value For клавиатуры поставьте, а слова из ячеек: Казанский вы правы, так, со знаком с авторским вариантом, Это именно то,
как и размер: Сразу хочу сказать,
на дальнейший отчет. в нем встречаются Потому что не
отчета. — в примере из 1-го. далее и готово: От плохих привычек I = 1 скопируйте тот, что одного столбца я даже стыдно теперь вопроса и звездочкой),
можно добавить СЖПРОБЕЛЫ(). что нужно! Огромное текста в ячейке. что диапазон будетПоэтому-то я думаю, адреса, в которых в файле?И тему можно
сократил количество дляНужно из текстов
excelworld.ru
Как удалить часть текста в ячейке по маске
Да, ну или нужно избавляться.
To UBound(arr) СЛОВАМИНУС у Вас в
не нашла. Подскажите, что такой вопрос в «Заменить на»Единственная разница с Вам спасибо!!! Вы Получается, тут диапазон около 10 тыс.
что надо как-то присутствует инфорация вТак я не
закрывать. Всем еще удобства. ячеек 1 столбца просто «найти иЦитатаПубликуя один и
= Trim(IIf(СЛОВАМИНУС <> тексте. Пробелы бывают
пожалуйста, как это
задал. ничего — Заменить авторским вариантом -
гений! нужно брать? Или ячеек в 1 через список удаляемых скобках, которую нельзя
отвергаю, а даже
planetaexcel.ru
удаление конкретного слова из ячеек (Формулы)
раз спасибо. ПокаТакже надо учитывать,
вычесть текст из заменить» тот же вопрос Empty, Replace(СЛОВАМИНУС, arr(I, разные) можно сделать.Спасибо большое все убираются все запятые.
Vanya7819
для всех слов
столбец
текстов.
удалять. наоборот, — это
что-то ищешь, еще
что удаляемая часть ячеек 2 столбца.No name
в разных форумах
1), «»), Replace(iTxt,
sensorria
Заранее благодарю.
sensorria
CHEVRYACHOK
openid.mail.ru/mail/vlad3622340: Как формулой удалить: использовать «ПОДСТАВИТЬ»? СлишкомydirectwebleyКак удалить используя решение очень красиво, чего-нибудь хорошего найдешь, текста может частичноПример того, что: и на дружественных arr(I, 1), «»))): Вы гений. Супер,
Nic70y
: Подскажите, пожалуйста.:
: Не только все «, прих. С большая формула выходит:): Всем привет! Очень: да, тогда наверное некий список того,
просто задержка прохода о чем и совпадать с адресом должно получиться -Если у тебя сайтах вы заставляете Next End Function все получилось ))))
: Ctrl+hЗадача следующая, в_Boroda_ запятые уираются, но датой + количествоAlexM
нужна подсказка, не только список что следует удалить? информации — пока не думал… : во вложении. «хххххх руб» без сразу нескольких людейVideoAlex
krestsoftAndreTM каждой из ячеек, Спасибо, Заменить - и «+XL» в шт.» и звёздочки: А такая идея? могу никак додумать.
vikttur — В моем протестируешь, посмакуешь, -
excelworld.ru
Как удалить заданную часть текста из ячейки?
ВладимирМясной переулок -
Nic70y точки тогда так: параллельно думать над: вба: Вопрос крайне тривиальный.: Выделяете столбец, Ctrl+H, записаны предложения/значения и то что надо, последней ячейке. «*» ?
Такая формула лучше Есть ячейка с: Во 2 и
примере я делаю а тут еще: { и удаляемый текст
:=(ЛЕВСИМВ (A1;ДЛСТР (A1-3)))*1 вашей задачей ихотя надо даже
Есть 2 столбца, в «найти» пишете мне нужно удалять формула — тамviktturПример прикрепил. чем в файле текстом S, в 3 — я список из, например люди любезно набежалиподрезаются индексы домов «(мясной)».=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;B2;))Если у тебяобесцениваете усилия new_function = Replace(Replace(Umensh, в ячейках 1-го
нужное слово, «заменить одно слово повторяющееся
где нет этого: Все нормально, этоБольшое Спасибо за200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(ЕСЛИ(A2<>»»;A2&» «;C1);ЕСЛИ(B2<>»»;B2&» «;»»);)
других двух ячейках недоумок, а Ваша
: — вот и в строках 8
Спасибоkrestsoft «хххххх руб. «тех, кто даст vichet, «»), «
текст, в ячейках на» оставляете пустым, из каждой ячейки, текста показывает #знач.
движок форумный мне ответ!Иначе пробелы накапливаются. есть слова Х формула работает, пока
»_(мясной)» запаздываешь…
и 9{/post}{/quote}webley: с точкой, тогда ответ вторым-третьим и «, » «)
2-го обязательно есть «Заменить всё»… слово Microsoft. Данное
но Замена вполне подножки ставит :)openid.mail.ru/mail/vlad3622340ydirect и Y. Задача добавил до 6»(мясной) и пр.Eugene KornevТочно. Тогда простенькую: Если ориентироваться наNic70y так:
planetaexcel.ru
т.д.
На чтение 5 мин Просмотров 8.4к. Опубликовано 12.05.2022
Часто бывает такое, что нужно удалить один или несколько символов из начала каждой ячейки.
Допустим у нас есть первый столбик, в котором лежат значения буква+цифры, нам нужно обрезать их таким образом, чтобы получились только цифры. Как на картинке ниже:
На самом деле, есть несколько вариантов, как можно это сделать.
Итак, давайте начнем!
Содержание
- С помощью функции ЗАМЕНА
- Копируем из ячейки все, кроме первого символа
- С помощью функции «Текст по столбцам»
- С помощью автозаполнения
- С помощью Visual Basic
С помощью функции ЗАМЕНА
Самый распространенный способ — использовать функцию ЗАМЕНА. Она довольно простая и в то же время полезная.
Для указанного выше примера формула функции принимает такой вид:
=ЗАМЕНА(A2;1;1;"")Что она делает?
Просто заменяет первый символ в строке на пустое место.
С помощью неё можно удалить любое количество символов с начала строки, например удалить два символа можно так:
=ЗАМЕНА(A1;1;2;"")Копируем из ячейки все, кроме первого символа
Можно сделать и так, в этом случае формула будет немного сложней, но результата мы добьемся.
Разница в том, что в первом случае мы удаляли первый символ из строки, а в этом случае — мы просто разделяем строку и переносим нужную её часть в другую ячейку.
Допустим, у нас тот же столбик, который нужно обработать:
В этой ситуации формула принимает такой вид:
=ПРАВСИМВ(A1;ДЛСТР(A1)-1)
Функция ДЛСТР получает длину всей строки, а далее из длины убавляется 1 (потому что 1 символ мы хотим «пропустить») и все это отдается в обработку функции ПРАВСИМВ.
С помощью функции «Текст по столбцам»
Прошлые методы простые, но неудобны тем, что для них нужен отдельный столбец, так сказать, «технический» (для расчетов).
Если, например, вам нужно получить результат в тех же ячейках, которые мы обрабатываем, грубо говоря, перезаписать их, то вам поможет этот метод.
Допустим, у нас тот же столбик:
Пошаговая инструкция:
- Выделите ячейки и щелкните на «Данные»;
- Далее «Текст по столбцам»;
- В открывшемся окошке:
- В первом шаге используйте вторую опцию (как на картинке);
- Во втором шаге установите стрелку на том делении, сколько символов вы хотите «отрезать»;
- И в последнем шаге, выберите «пропустить столбец»;
- Подтвердите.
Таким образом, первый символ был удален.
Как это работает?
Довольно просто: вы указали функции сколько символов нужно «отрезать» от начала строки, а также вы указали пропустить столбец. Это значит что данные, после обработки, будут записаны в тот же столбик, где они изначально были.
С помощью автозаполнения
Эта функция — одна из самых удобных функций в Excel. Она автоматически пытается угадать значения для следующих ячеек, если вы заполнили первую (одну или две).
Давайте посмотрим как это работает.
Допустим, у нас тот же столбик:
Воспользуемся этой функцией
Пошаговая инструкция:
- В следующем столбике, впишите значение, которое должно быть после преобразования (в нашем случае 271);
- А также, по аналогии, укажите значение для второй ячейки. На самом деле, функция автозаполнения уже поняла логику и предлагает нам заполнить всю оставшуюся часть;
- Теперь выделите обе ячейки с обработанными данными;
- Наведите курсор на правую нижнюю часть выделения и потяните её вниз;
- Готово!
Просто не правда ли?
Это удобная функция, но с ней нужно быть осторожным. Если она неправильно поймет логику вашего заполнения — могут быть ошибки.
С помощью Visual Basic
Ну и как обычно, напоследок, рассмотрим как можно сделать это с помощью Visual Basic.
Я дам вам код функции для такого рода задачи, вы сможете модифицировать его.
Также, как и в случае использования функции «Текст по столбцам» в этом методе нам не нужен дополнительный столбик для расчетов.
Обратите внимание, что если строк, которые нужно обработать у вас очень много, выполнение этого кода может замедлить работу компьютера на какое-то время.
Этот код удаляет первый символ из каждой ячейки:
For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cellКак его использовать?
Пошаговая инструкция:
- Выделите диапазон ячеек, в которых нужно удалить первый символ
- Откройте просмотр кода (правой кнопкой на имя листа -> «Просмотр кода»);
- Далее щелкните на «View» -> «Immediate Window» (или CTRL + G);
- Поместите в окно наш код;
- Обязательно наведите мышь в конец кода;
- И просто подтвердите (нажав ENTER);
Готово, первый символ удален.
Что же делает этот код?
For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cell Итак, здесь используется цикл, который копирует и перезаписывает каждую ячейку отдельно.
В общем то и все! Мы рассмотрели несколько удобных и практичных методов удаления первого символа из каждой ячейки.
Я надеюсь эта статья была полезна для вас! Потому что даже если какой-то способ вам не по душе, вы всегда можете использовать несколько других.
|
Доброе время суток1 Подскажите, пожалуйста, как убрать определенное слово из ячеек столбца, при учете, что слово в различных местах словосочетаний каждой ячейки? Пример: Автомобиль белый, не битый, не в розыске. Слово «Автомобиль» нужно убрать, а все остальное оставить! |
|
|
Юрий М  Модератор Сообщений: 60585 Контакты см. в профиле |
Ctrl+H В верхнем поле пишем автомобиль, нижнее оставляем пустым — заменить все. |
|
Отлично! Спасибо! Работает! Но… Текст — это ссылка (=ВПР($B8;База!$1:$65536;D$2;0). Возможно ли с нее выдернуть ненужное слово? |
|
|
Юрий М Модератор Сообщений: 60585 Контакты см. в профиле |
Вариант: сначала заменить результат формулы на значение, затем известным уже Вам способом. |
|
Владимир Пользователь Сообщений: 8196 |
#5 16.04.2013 13:30:13 пишите:
«..Сладку ягоду рвали вместе, горьку ягоду я одна.» |
||
|
Спасибо ОГРОМНОЕ!!! Я профильтровал исходный файл по всем ненужным словам, и формула все выдала. Вариант : =подставить(ВПР($B8;База!$1:$65536;D$2;0);»автомобиль»;»»), не сработал, но наверно и не нужно, т.к. помимо слова автомобиль необходимо убирать зеленый, красный, битый и т.д. Вы просто ГУРУ Excel!!! Спасибо, ребята, за ответы, скорость и ваши знания!!! |
|
|
vikttur Пользователь Сообщений: 47199 |
#7 16.04.2013 14:05:02
И останутся от данных
антенны да педали |
||||
|
Игорь Кущ  Пользователь Сообщений: 1 |
Добрый день, а как сделать удаления «Автомобиль», если в тексте еще есть «Автомобильный» например. Чтобы остался «Автомобильный» а не «ный» |
|
vikttur Пользователь Сообщений: 47199 |
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(» «&текст&» «;» Aвтомобиль «;)) |
|
IKor  Пользователь Сообщений: 1167 |
#11 05.11.2020 13:05:53 Игорь Кущ, попробуйте комбинацию из советов Юрия и Виктора:
В верхнем поле пишем «автомобиль » — с пробелом после слова, а в нижнем — просто пробел: » « А когда соберетесь заменять все варианты слова «автомобиль» в разных падежах, то сначала обратите внимание на статью в Приемах: Массовая замена текста формулами |
||
Как удалить заданное число символов в середине строки?
С помощью каких формул это можно сделать?
Чтобы поменять содержимое ячейки нужен макрос,а формулы не меняют содержание других ячеек, формулы только извлекают и обрабатывают информацию из ячеек, входящих в параметры формул.
Чтобы получить содержимое ячейки, содержащей текст ячейки с удаленным первым или последним символом используют функцию выделения подстроки:ПСТР(текст;начальный символ;количество символов)
для определения количества символов используют функцию длины строки ДЛСТР(текст)
если текст «абвгдежзиклмн» находится в ячейке A1 то формулы будут такими:
- строка с удаленным первым символом ячейки =ПСТР(A1;2;ДЛСТР(A1)-1) (результат бвгдежзиклмн)
- строка с удаленным последним символом ячейки =ПСТР(A1;1;ДЛСТР(A1)-1) (результат абвгдежзиклм)
- строка с удаленными первым и последним символами ячейки =ПСТР(A1;2;ДЛСТР(A1)-2) (результат бвгдежзиклм)
- часть строки в 5 символов , начиная с 3-го символа =ПСТР(A1;3;5) (результат вгдеж)
если нужно объединить несколько частей строки или частей разных строк, то подстроки можно соединять знаком &
например: вырезаем 3 символа, начиная с символа 3 и 4 символа, начиная с символа 7:
=ПСТР(A1;3;3)&ПСТР(A1;7;4) (результат вгджзик)
если нужно в результат включить строку, то она тоже добавляется символом &
=ПСТР(A1;3;3)&». «&ПСТР(A1;7;4) (результат вгд. жзик)
Если возникает необходимость удалить символы в ячейке или диапазоне ячеек Excel, то следует использовать специальные формулы.
Рассмотрим различные ситуации.
Удаление первого символа в ячейке
Для этого будем использовать формулу, состоящую из двух функций:
ПРАВСИМВ — возвращает заданное число символов справа.
ДЛСТР — длина строки.
ПРАВСИМВ имеет 2 аргумента:
1) Ссылка на ячейку, из которой нужно вернуть символы. Например, B3.
2) Количество символов. В нашем случае это будет вся строка за исключением первого символа. Поэтому пишем ДЛСТР(B3)-1.
Формула выглядит так:
ПРАВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).
Удаление последнего символа в ячейке
Если в Excel нужно удалить не первый, а последний символ в ячейке, то нужно использовать функцию ЛЕВСИМВ.
Эта функция работает также, как и ПРАВСИМВ — только возвращает не правые символы, а левые.
Формула для удаления последнего символа будет такой:
ЛЕВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).
Удаление первого и последнего символа в ячейке
Если требуется одновременно удалить первый и последний символ, то можно воспользоваться несколькими способами.
Во-первых, можно последовательно применить функции ПРАВСИМВ И ЛЕВСИМВ (с промежуточной ячейкой или без).
Также существует функция ПСТР, возвращающая заданное число символов в ячейке. Аргументы:
1) Ссылка на ячейку.
2) Начальная позиция. В нашем случае она равна 2, так как первый символ нас не интересует.
3) Количество знаков, которые нужно извлечь. Пишем ДЛСТР(B3)-2, так как нужно вернуть все символы из ячейки, кроме первого и последнего.
Формула будет такой:
Удаление заданного числа символов в ячейке справа или слева
Иногда бывает, что нужно удалить не 1 крайний символ в ячейке, а несколько.
В этом случае можно использовать всё те же функции, только в аргументах ЛЕВСИМВ и ПРАВСИМВ пишем не ДЛСТР(B3)-1, а ДЛСТР(B3)-n.
n — число символов, которые нужно удалить.
1) ПРАВСИМВ(B3;ДЛСТР(B3)-3). Удаляем 3 первых символа.
2) ЛЕВСИМВ(B3;ДЛСТР(B3)-5). Удаляем 5 символов с конца.
Удаление символов в середине ячейки
Для этого в Excel существует функция ЗАМЕНИТЬ. Она имеет 4 аргумента:
1) Ссылка на ячейку.
2) Начальная позиция. Например, если нам нужно удалять данные с 3 символа, то пишем «3».
3) Число знаков. Сюда пишем число удаляемых символов. Например, «3».
4) Новый текст. Так как нам нужно именно убрать данные символы, то пишем «» (две кавычки без пробела).
Таким образом, формула будет выглядеть так:
Она удаляет с 3 по 5 символ в ячейке.
При работе в Эксель иногда бывает, что нужно убрать определённое содержимое из всех ячеек на листе.
И если требуется убрать фактически одно и то же, то проще не использовать формулы, а сделать это с помощью «Найти и заменить».
Требуется убрать «12» со всех ячеек.
1) Набираем комбинацию клавиш Ctrl + F, чтобы появилось окно «Найти и заменить».
2) Переходим на вкладку «Заменить».
В поле «Найти» пишем текст, который нам не нужен в ячейках. То есть 12.
А в поле «Заменить» ничего не пишем.
3) Нажимаем на кнопку «Заменить всё» — текст в ячейках станет такой, который был нам нужен.
Теперь о стандартных функциях Эксель, которые можно использовать для удаления данных из ячейки.
Точнее — это будет не удаление, а просто копирование данных из ячейки в обрезанном виде. Если после применения формулы потребуется, чтобы новые данные встали вместо исходных, то при их копировании нужно использовать опцию «Вставить только значения».
ЛЕВСИМВ и ПРАВСИМВ — возвращает заданное число символов слева и справа, соответственно.
ДЛСТР — длина строки, функция будет выполнять вспомогательную роль.
ПСТР — формирует подстроку из исходной строки текста на указанную длину и с определённой позиции.
НАЙТИ и ПОИСК — возвращает позицию первого вхождения какого-либо символа, слова, словосочетания. Они, как и ДЛСТР, могут использоваться как вспомогательные функции при удалении части данных из ячейки.
Нужно, чтобы в ячейке остались только данные о серии паспорта.
Будем использовать такую формулу:
Так как серия паспорта записана слева, то нужна функция ЛЕВСИМВ.
ДЛСТР(B2)-НАЙТИ(«номер»;B2) — здесь мы нашли с какой позиции начинаются ненужные нам символы и отняли эту цифру из длины строки.
А 1 вычитается из-за того, что перед словом «номер» стоит пробел. В принципе, можно было не вычитать 1, а просто написать в формуле пробел перед номером: ДЛСТР(B2)-НАЙТИ(» номер»;B2).
Теперь выделяем ячейки с полученными данными и копируем их взамен старых.
Только при вставке не забываем выбрать «Вставить только значения».
А ячейки с уже неработающими формулами, разумеется, можно очистить.
Писала уже в одном из вопросов на БВ о работе со специальными формулами, их много, с их помощью много чего можно сделать, в данном вопросе обсуждается частный случай, рассмотрим его.
Посмотрим, как удалить первый символ в ячейке. Нам нужна формула из двух функций:
Пусть у нас будет вот такая таблица:
Мы видим, что во втором столбце у нас уже выполнено задание — удален первый символ. Формула использована вот такая: ПРАВСИМВ(B3;ДЛСТР(B3 )-1). То есть что мы с вами делаем: мы длину строки делаем меньше на один символ, при этом количество символов оставшееся программа считает справа, то есть лишним у нас оказывается левый, то есть первый символ, он и удаляется программой.
Несложно догадаться, что для удаления последнего символа нужно использовать немного другую формулу: ЛЕВСИМВ(B3;ДЛСТР(B3)-1) (применительно к этой же таблице. Для удаления первого и последнего символа
Таким же образом можно удалить несколько, а не один символ, справа или слева, просто пишем в формуле не (B3)-1, а, например, (B3)-3, и тогда удалится 3 символа справа или слева в соответствии с полной формулой.
Чтобы удалить символы в середине ячейки, используем другую формулу, применительно все к той же таблице, если мы хотим удалить три центральных символа, эта формула будет выглядеть так: ЗАМЕНИТЬ(B3;3;3;»»), где В3, это ячейка с первоначальным текстом с полным набором символов, первая 3 — первый символ, который надо удалить, вторая 3 — количество символов, которые надо удалить, затем идут кавычки, так как это формула замены, то в них можно было бы указать символы, которые мы хотим поставить вместо удаленных, но мы хотим просто удалить три символа, поэтому в кавычках у нас ничего нет. Вот что получилось в итоге:
Как обрезать символы в Excel
Обзор способов, позволяющих быстро обрезать значения ячеек и удалить лишний текст. Обрезка текста формулами слева и справа, быстрая обрезка строк надстройкой VBA, удаление лишнего текста до определенного символа, разбиение текста по столбцам.
Как обрезать символы при помощи формул?
Как оставить заданное количество символов в начале строки, остальные удалить?
Ввести в ячейку стандартную текстовую функцию Excel «ЛЕВСИМВ», которая возвращает заданное количество знаков с начала строки.
Как удалить заданное количество знаков в начале строки, остальные оставить?
Ввести в ячейку комбинацию стандартных функций Excel «ПРАВСИМВ» и «ДЛСТР», которая определяет длину строки, то есть количество символов в значении указанной ячейки.
Как оставить заданное количество символов в конце строки, остальные удалить?
Ввести в ячейку стандартную текстовую функцию Excel «ПРАВСИМВ», которая возвращает заданное количество знаков с конца строки.
Как удалить заданное количество знаков в конце строки, остальные оставить?
Ввести в ячейку комбинацию стандартных функций Excel «ЛЕВСИМВ» и «ДЛСТР»
Как видно из приведенных примеров, оставить текстовые строки заданной длины достаточно просто при помощи формулы в одно действие. Чтобы обрезать определенное количество знаков с начала или с конца строки, необходимо вносить в ячейки достаточно громоздкие формулы, что достаточно неудобно.
Как обрезать символы без ввода формул?
Надстройка для быстрой обрезки текста
Все описанные выше манипуляции с текстовыми значениями можно производить и без использования формул, что гораздо быстрее. Для этого нужно лишь вызвать диалоговое окно надстройки и задать в нем диапазон ячеек, подходящую функцию и количество знаков, которые необходимо удалить либо оставить.
1. обрезать заданное количество символов слева;
2. обрезать заданное количество символов справа;
3. обрезать значения ячеек до первого попавшегося заданного символа (сочетания символов);
4. обрезать значения ячеек до последнего попавшегося символа (сочетания символов).
В надстройку зашиты функции, аналогичные тем, которые описаны выше, что сокращает время на:
— выделение диапазона ячеек.
В программе предусмотрена возможность вместо выделения диапазона ячеек выделять целиком строки/столбцы. Программа автоматически сокращает выделенный диапазон до границы используемого диапазона, при этом на листе не должно быть отфильтрованных строк.
Разбиение текста по столбцам
Для обрезания значений ячеек можно использовать «Текст по столбцам». Это стандартное средство Excel не удаляет и не обрезает символы, а распределяет содержимое ячеек по разным столбцам при помощи мастера текстов. Кнопка вызова диалогового окна находится на вкладке «Данные» в группе кнопок «Работа с данными». Этот инструмент можно использовать в двух режимах работы, разделять исходные данные при помощи разделителей либо при помощи настраиваемых полей, когда можно вручную указать границы раздела значений.
Шаг1. Выбор формата исходных данных.
Шаг2. Установка нужной ширины полей.
Количество полей при этом не ограничено.
Шаг 3. Просмотр и получение результатов.
В результате таких манипуляций исходные значения нарезаются в соответствии с установленными полями и разносятся по различным столбцам.
Как убрать часть текста из ячейки?
Подскажите, пожалуйста, как в ячейке убрать эту часть:
Поиск-замена не срабатывает, точнее вообще не находит этого ни в одной ячейке, а у меня их около 3 000 шт. Может есть какие-то макросы, функции, не знаю хоть что то..
вводится в окне поиска/замены.
Для переноса текста в другую строку (в одной ячейке) существует специальный символ переноса строки — СИМВОЛ(10) . Инструмент НАЙТИ/ЗАМЕНИТЬ видит вместо символа переноса строка СИМВОЛ(32) — пробел. Естественно, фрагмента с пробелам он не находит.
Чтобы инструмент справился с таким текстом, нужны дополнительные движения по преобразованию текста. Гораздо проще удалить фрагмент формулой.
Против текстов в свободном столбце записать удаляемые фрагменты. Если удаляемая часть одна для всех текстов, не нужно для нее выделять столбец, можно записать в одну ячейку.
Еще в один столбец внести формулу и протянуть ее на нужное количество строк.
В столбце А тексты, в столбце В (или только в ячейке B1) — удаляемый фрагмент.
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Вы можете очистить ячейки, чтобы удалить содержимое ячеек (формулы и данные), форматы (включая числовые форматы, условные форматы и границы), а также все вложенные комментарии. Очищенные ячейки остаются пустыми или неформатированные ячейки на листе.
-
Вы можете выбрать ячейки, строки или столбцы, которые нужно очистить.
Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
-
На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Очистить
и сделайте следующее:-
Чтобы очистить все содержимое, форматы и приметки, содержащиеся в выбранных ячейках, нажмите кнопку Очистить все.
-
Чтобы очистить только форматы, примененные к выбранным ячейкам, нажмите кнопку Очистить форматы.
-
Чтобы очистить только содержимое выбранных ячеек, оставив на месте любые форматы и комментарии, нажмите кнопку Очистить содержимое.
-
Чтобы очистить примечания или заметки, прикрепленные к выбранным ячейкам, нажмите кнопку Очистить примечания и заметки.
-
Чтобы очистить все гиперссылки, прикрепленные к выбранным ячейкам, выберите очистить гиперссылки.
-
и сделайте следующее:Примечания:
-
Если щелкнуть ячейку и нажать кнопку DELETE или BACKSPACE, содержимое ячейки будет очищено без удаления форматов и приметок к ячейкам.
-
Если очистить ячейку с помощью окну Очистить все или Очистить содержимое,ячейка больше не содержит значения, а формула, которая ссылается на эту ячейку, получает значение 0 (ноль).
-
Если вы хотите удалить ячейки с таблицы и сдвинуть окружающие ячейки, чтобы заполнить место, вы можете выбрать ячейки и удалить их. На вкладке Главная в группе Ячейки щелкните стрелку рядом с кнопкой Удалитьи выберите удалить ячейки.
Нужна дополнительная помощь?
Убрать лишнее из ячейки в Microsoft Excel можно разными способами, тут все будет зависеть в первую очередь от количества самих ячеек из которых нужно удалить лишнею часть текста. Если их не много то тут все просто, выделяем ячейку, выделяем не нужную часть текста и удаляем её. А вот если ячеек скажем 50, 100 или даже 1000, редактирование каждой займет очень много времени, но есть один способ который позволит из всех ячеек сразу убрать лишний текст.
Как убрать часть текста из нескольких ячеек
И так, как же можно убрать часть текст сразу из нескольких ячеек, допустим есть вот такой список, в каждой ячейки есть имя и телефон. Нам необходимо из всех ячеек убрать номера телефонов оставив только ФИО.
Конечно если ячеек не много как в моем случае проще и быстрей будет отредактировать каждую по отдельности.
Но если их будет больше 10 то тут уже лучше воспользоваться автозаменой. И так выделяем все ячейки из которых хотим убрать лишнее и нажимаем сочетание клавиш «Ctrl+H». В открывшемся окне в поле «Найти» вводим, сначала ставим один пробел потом слово «тел» и знак «*», поле «Заменить на» оставляем пустым и жмем «Заменить все».
В итоге во всех выделенных ячейках после слова «Тел» все будет заменено.
Немного поясню, сначала в поле «Найти» ставим один пробел далее пишем слово с которого хотим начать замену, звездочка говорит о том что после указанного слова все будет заменено.
Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.