Как удалить символ во всех ячейках в excel

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

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

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

На самом деле, есть несколько вариантов, как можно это сделать.

Итак, давайте начнем!

С помощью функции ЗАМЕНА

Самый распространенный способ — использовать функцию ЗАМЕНА. Она довольно простая и в то же время полезная.

Для указанного выше примера формула функции принимает такой вид:

Просто заменяет первый символ в строке на пустое место.

С помощью неё можно удалить любое количество символов с начала строки, например удалить два символа можно так:

Копируем из ячейки все, кроме первого символа

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

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

Допустим, у нас тот же столбик, который нужно обработать:

В этой ситуации формула принимает такой вид:

Функция ДЛСТР получает длину всей строки, а далее из длины убавляется 1 (потому что 1 символ мы хотим «пропустить») и все это отдается в обработку функции ПРАВСИМВ.

С помощью функции «Текст по столбцам»

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

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

Допустим, у нас тот же столбик:

• Выделите ячейки и щелкните на «Данные»;

• Далее «Текст по столбцам»;

• В открывшемся окошке:

• В первом шаге используйте вторую опцию (как на картинке);

• Во втором шаге установите стрелку на том делении, сколько символов вы хотите «отрезать»;

• И в последнем шаге, выберите «пропустить столбец»;

• Подтвердите.

Таким образом, первый символ был удален.

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

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

С помощью автозаполнения

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

Давайте посмотрим как это работает.

Допустим, у нас тот же столбик:

Воспользуемся этой функцией

• В следующем столбике, впишите значение, которое должно быть после преобразования (в нашем случае 271);

• А также, по аналогии, укажите значение для второй ячейки. На самом деле, функция автозаполнения уже поняла логику и предлагает нам заполнить всю оставшуюся часть;

• Теперь выделите обе ячейки с обработанными данными;

• Наведите курсор на правую нижнюю часть выделения и потяните её вниз;

• Готово!

Просто не правда ли?

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

С помощью Visual Basic

Ну и как обычно, напоследок, рассмотрим как можно сделать это с помощью Visual Basic.

Я дам вам код функции для такого рода задачи, вы сможете модифицировать его.

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

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

Этот код удаляет первый символ из каждой ячейки:

Как его использовать?

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

• Откройте просмотр кода (правой кнопкой на имя листа -> «Просмотр кода»);

• Далее щелкните на «View» -> «Immediate Window» (или CTRL + G);

• Поместите в окно наш код;

• Обязательно наведите мышь в конец кода;

• И просто подтвердите (нажав ENTER);

Готово, первый символ удален.

Что же делает этот код?

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

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

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

Как удалить символ во всех ячейках в excel

Предположим, у вас есть диапазон данных, и вы хотите удалить определенный символ из каждой строки, например символ «-» как показано на скриншоте ниже, в Excel есть ли у вас хорошая идея решить эту задачу? Теперь я расскажу вам о некоторых хитростях, позволяющих удалить определенный символ из строк ячеек в Excel.

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

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

В Excel вы можете сначала узнать все символы «-», а затем ничего не заменить.

1. Выберите нужный диапазон или весь лист и нажмите Ctrl + H для отображения Найти и заменить диалоговое окно, затем введите — в Найти то, что текстовое поле и ничего не оставляйте в Заменить текстовое окно. Смотрите скриншот:

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

2. Нажмите Заменить все. Затем появится диалоговое окно с напоминанием о количестве замен, и просто нажмите OK закрыть его.

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

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

После установки Kutools for Excel, пожалуйста, сделайте следующее: (Бесплатная загрузка Kutools for Excel Сейчас!)

1. Выберите ячейки, в которых нужно удалить определенные символы, а затем щелкните Kutools > Текст > Удалить символы. Смотрите скриншот:

2. Затем в Удалить символы диалог, проверьте На заказ только вариант, затем введите символы, которые вы хотите удалить из строк (с учетом регистра), вы можете увидеть результат в предварительный просмотр панель. Смотрите скриншот:

3. Нажмите Ok. Затем все символы «-» удаляются из каждой строки выделения.

Наконечник: Если вы хотите удалить из строк несколько символов, таких как «-» и «K», просто введите символы, которые вы хотите удалить. «-K» в На заказ текстовое окно. Смотрите скриншот:

Как обрезать символы в Excel

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

Как обрезать символы при помощи формул?

Как оставить заданное количество символов в начале строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ЛЕВСИМВ», которая возвращает заданное количество знаков с начала строки.

Как удалить заданное количество знаков в начале строки, остальные оставить?

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

Как оставить заданное количество символов в конце строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ПРАВСИМВ», которая возвращает заданное количество знаков с конца строки.

Как удалить заданное количество знаков в конце строки, остальные оставить?

Ввести в ячейку комбинацию стандартных функций Excel «ЛЕВСИМВ» и «ДЛСТР»

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

Как обрезать символы без ввода формул?

Надстройка для быстрой обрезки текста

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

1. обрезать заданное количество символов слева;

2. обрезать заданное количество символов справа;

3. обрезать значения ячеек до первого попавшегося заданного символа (сочетания символов);

4. обрезать значения ячеек до последнего попавшегося символа (сочетания символов).

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

— выделение диапазона ячеек.

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

Разбиение текста по столбцам

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

Шаг1. Выбор формата исходных данных.

Шаг2. Установка нужной ширины полей.

Количество полей при этом не ограничено.

Шаг 3. Просмотр и получение результатов.

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

Как удалить первый символ из строки в Excel (быстро и легко)

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

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

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

В Excel есть несколько способов сделать это …

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

Так что давайте не будем терять время и перейдем к делу.

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

Замена первого символа пробелом с помощью формулы

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

И вы можете легко сделать это с помощью функции REPLACE.

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

Приведенная ниже формула сделает это:

Вышеупомянутая формула просто начинается с начала и заменяет 1 символ с начала пробелом («»).

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

Извлечь из ячейки все, кроме первых символов (с помощью формулы)

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

Это можно сделать с помощью функции RIGHT (которая извлекает заданное количество символов справа от текстовой строки).

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

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

Приведенная выше формула использует функцию LEN, чтобы сначала узнать общее количество символов в ячейке. Затем он использует функцию ВПРАВО, чтобы получить все символы в результате формулы, кроме первого.

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

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

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

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

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

Ниже приведены шаги, чтобы сделать это с помощью текста в столбцы в Excel:

1. Выберите ячейки, из которых вы хотите удалить первый символ в Excel
2. Перейдите на вкладку "Данные".
3. В инструментах «Данные» нажмите «Текст в столбцы».
4. В мастере преобразования текста в столбец внесите следующие изменения:
   • Шаг 1 из 3: Выберите фиксированную ширину (по умолчанию выбрано с разделителями, поэтому вам нужно изменить это) и нажмите Далее.
   • Шаг 2 из 3. В окне предварительного просмотра данных поместите курсор после первого символа и щелкните правой кнопкой мыши. Это вставит строку, как показано ниже. Эта строка сообщает Text to Column о разделении текста на отдельные ячейки.
   • Шаг 3 из 3. Выберите «Не импортировать столбец (пропустить)» и оставьте целевую ячейку без изменений (это будет та же ячейка, в которой хранятся данные).
5. Щелкните Готово.

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

Позвольте мне быстро объяснить, как это работает:

Когда вы помещаете курсор и щелкаете после первого символа на шаге 2, Text to Columns было сказано разделить данные, используя эту строку. Таким образом, символы перед строкой разделяются как одна часть, а остальные как другая часть.

Но на шаге 3, поскольку мы выбрали первый столбец (который был слева от строки, которую мы вставили на шаге 2), а затем выбрали «Не импортировать столбец», он просто пропустил первый символ и предоставил нам оставшуюся часть в та же ячейка.

Хотя я использовал этот метод для удаления первого символа в Excel, вы можете использовать его для удаления второго, третьего или n-го числа символов от начала конца. Вы также можете использовать этот метод для извлечения 2/3 / n символов из начала или середины текстовой строки.

Используйте Flash Fill, чтобы мгновенно удалить первый символ

Мне нравится функция Flash Fill в Excel, поскольку она позволяет легко выполнять некоторые операции с текстовыми данными.

И то, что мы здесь пытаемся сделать, Flash Fill — идеальный инструмент.

Примечание. Мгновенная заливка доступна только в Excel 2013 и более поздних версиях. Если вы используете Excel 2010 или более ранние версии, вы не сможете использовать этот метод.

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

Вот как это сделать:

1. В ячейке рядом с первой ячейкой набора данных введите желаемый результат. В этом случае, поскольку у меня есть M70, и я хочу удалить первый символ, я вручную введу значение 271.
2. Во второй ячейке введите ожидаемый результат, который в этом примере будет 360. [Пока вы печатаете, некоторые значения могут отображаться серым цветом. Это значения, угаданные Flash Fill на основе шаблона. Если они верны, прекратите печатать и просто нажмите клавишу ввода, и вы получите результат. Если эти значения не отображаются или не исчезают, переходите к следующему шагу]
3. Выделите обе ячейки, поместите курсор в нижнюю правую часть выделения (на маленький зеленый квадрат) и дважды щелкните (или, удерживая левую клавишу мыши, перетащите до конца набора данных).
4. Внизу полученных данных вы увидите небольшой значок «Параметры автозаполнения». Нажмите здесь.
5. Нажмите на Flash Fill.

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

Есть несколько действительно интересных вещей, которые вы можете сделать с помощью этой Flash Fill, и я расскажу о них в этом видео ниже:

Используйте простой макрос VBA в непосредственном окне

Непосредственное окно — это место в редакторе Excel VB Editor, которое позволяет быстро запустить код макроса (без хлопот, связанных с вставкой модуля или сохранением макроса).

Все, что вам нужно сделать, это использовать код, скопировать и вставить его в непосредственном окне и нажать клавишу ввода.

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

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

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

Вот шаги, чтобы использовать этот код из непосредственного окна:

1. Выберите ячейки / диапазон, из которых вы хотите удалить первый символ из текстовой строки
2. Щелкните правой кнопкой мыши имя вкладки листа (тот же лист, где у вас есть эти данные)
3. Щелкните Просмотреть код. Это откроет бэкэнд VB Editor
4. В окне редактора VB выберите в меню пункт «Просмотр» и затем нажмите «Немедленное окно». Это приведет к немедленному отображению окна. Этот шаг не нужен, если непосредственное окно уже видно.
5. Скопируйте и вставьте указанную выше строку кода в ближайшее окно.
6. Поместите курсор в конец строки
7. Нажмите клавишу Enter

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

Краткое объяснение строки кода VBA

В приведенной выше строке кода используется цикл For Next, который проходит по каждой выделенной ячейке. Затем он использует функции RIGHT и LEN для извлечения всех символов, кроме первого.

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

Например, если вы не хотите использовать дополнительный столбец и хотите получать результаты в тех же ячейках, вам лучше использовать метод Text to Columns или метод VBA Immediate Window.