Заменить первый символ в ячейке Excel

Обновлено: 03.07.2024

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

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

Удаление первых символов из строки — одна из самых распространенных задач в Excel, и ее можно выполнить с помощью трех разных формул.

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

Чтобы удалить первый символ из строки, вы можете использовать либо функцию REPLACE, либо комбинацию функций RIGHT и LEN.

Здесь мы просто берем 1 символ из первой позиции и заменяем его пустой строкой ("").

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

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

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

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

Чтобы удалить начальные символы с левой стороны строки, вы также используете функции REPLACE или RIGHT и LEN, но каждый раз указываете, сколько символов вы хотите удалить:

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

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

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

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

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

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

После того, как код вставлен в книгу (подробные инструкции здесь), вы можете удалить первые n символов из данной ячейки, используя эту компактную и интуитивно понятную формулу:

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

Чтобы удалить первые два символа из A3, используйте формулу в B3:

Чтобы удалить первые три символа из A4, используйте формулу в B4:

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

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

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

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

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

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

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

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

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

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

Чтобы удалить последние 3 символа, используйте 3 вместо num_chars:

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

Чтобы удалить последние 5 символов, укажите 5 для num_chars:

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

Пользовательская функция для удаления последних n символов в Excel

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

Функция называется RemoveLastChars, и ее синтаксис вряд ли нуждается в пояснениях:

Чтобы проверить это в полевых условиях, давайте избавимся от последнего символа в A2:

Кроме того, мы удалим последние 2 символа с правой стороны строки в A3:

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

Как вы можете видеть на снимке экрана ниже, наша пользовательская функция работает блестяще!

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

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

chars_left — количество символов, которые необходимо удалить слева.
chars_right — количество символов, которые необходимо удалить справа.

Подайте приведенные выше числа в формулу:

=СРЕДН(A2, 7+1, ДЛСТР(A2) - (7+10))

…и результат не заставит себя ждать:

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

Аргумент text не вызывает вопросов — это исходная строка (в нашем случае A2).

Чтобы получить позицию первого извлекаемого символа (start_num), вы добавляете 1 к количеству символов, которые нужно удалить слева (7+1).

Чтобы определить, сколько символов нужно вернуть (num_chars), вы вычисляете общее количество удаленных символов (7 + 11) и вычитаете сумму из длины всей строки: LEN(A2) - (7+10)).

Получить результат в виде числа

Какую бы из приведенных выше формул вы ни использовали, на выходе всегда будет текст, даже если возвращаемое значение содержит только числа. Чтобы вернуть результат в виде числа, либо заверните основную формулу в функцию ЗНАЧ, либо выполните некоторую математическую операцию, которая не повлияет на результат, например. умножьте на 1 или прибавьте 0. Этот метод особенно полезен, когда вы хотите дополнительно вычислить результаты.

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

=ЗНАЧ(ЗАМЕНИТЬ(A2, 1, 1, ""))

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

=Удалить первые символы (A2, 1) + 0

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

В Excel 2013 и более поздних версиях есть еще один простой способ удалить первый и последний символы в Excel — функция мгновенного заполнения.

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

Удаление символов по позиции с помощью Ultimate Suite

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

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

Например, чтобы удалить первый символ, мы настраиваем следующую опцию:

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

Чтобы удалить первый символ в ячейке, вы можете использовать функцию ЗАМЕНИТЬ. В показанном примере формула в D5 выглядит следующим образом:

Эта формула использует функцию ЗАМЕНИТЬ для замены первого символа в ячейке пустой строкой (""). Аргументы для REPLACE настроены следующим образом:

old_text – исходное значение из столбца B.
start_num жестко закодирован как число 1
num_chars берется из столбца C
new_text вводится как пустая строка ("")

Поведение или REPLACE выполняется автоматически. С этими входными данными функция REPLACE заменяет первый символ в B5 пустой строкой и возвращает результат.

Удаление N символов

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

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

где N – количество удаляемых символов.

ВПРАВО и ВЛЕВО

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

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

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

Получение числового значения

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

Математическая операция приводит к тому, что Excel преобразует текст в числа. Это работает, только если значение, возвращаемое RIGHT, содержит только числа.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функций ЗАМЕНИТЬ и ЗАМЕНИТЬВ в Microsoft Excel.

Описание

REPLACE заменяет часть текстовой строки в зависимости от указанного вами количества символов другой текстовой строкой.

REPLACEB заменяет часть текстовой строки в зависимости от указанного вами количества байтов другой текстовой строкой.

Эти функции могут быть доступны не на всех языках.

REPLACE предназначен для использования с языками, использующими набор однобайтовых символов (SBCS), тогда как REPLACEB предназначен для использования с языками, использующими двухбайтовый набор символов (DBCS). Настройка языка по умолчанию на вашем компьютере влияет на возвращаемое значение следующим образом:

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

REPLACEB считает каждый двухбайтовый символ равным 2, если вы включили редактирование языка, поддерживающего DBCS, а затем установили его в качестве языка по умолчанию. В противном случае REPLACEB считает каждый символ равным 1.

Языки, поддерживающие DBCS, включают японский, китайский (упрощенный), китайский (традиционный) и корейский.

Синтаксис

ЗАМЕНИТЬ(старый_текст, начальный_номер, число_знаков, новый_текст)

ЗАМЕНИТЬB(старый_текст, начальный_номер, число_байтов, новый_текст)

Синтаксис функций REPLACE и REPLACEB имеет следующие аргументы:

Старый_текст Обязательно. Текст, в котором вы хотите заменить некоторые символы.

Обязательный параметр Start_num. Позиция символа в old_text, которую вы хотите заменить на new_text.

Необходимо Num_chars. Количество символов в old_text, которые вы хотите заменить REPLACE на new_text.

Необходимо Num_bytes. Количество байтов в old_text, которые вы хотите заменить REPLACEB на new_text.

Обязательный параметр New_text. Текст, который заменит символы в old_text.

Пример

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

<р>. кто действительно увлечен изучением Excel в эти дни.

Знаете ли вы, как удалить первый символ из ячейки в Excel?

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

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

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

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

1. Объедините RIGHT и LEN, чтобы удалить первый символ из значения

Использование комбинации RIGHT и LEN является наиболее подходящим способом удаления первого символа из ячейки или текстовой строки.

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

В приведенном ниже примере у нас есть список номеров рулонов, где у нас есть буква «H» в качестве префикса.

Здесь нам нужно удалить «H» и вернуть оставшуюся часть строки, и нам может помочь комбинация RIGHT и LEN.

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

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

но как работает эта формула

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

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

Как вы знаете, вам нужно указать количество символов, которые вы хотите извлечь, используя функцию RIGHT, и для этого мы использовали число, возвращаемое LEN…

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

Если вы хотите удалить более одного символа, просто замените 1 в нужной функции на 2, 3, 4 для любых символов, которые вы хотите удалить.

2. Использование функции REPLACE для удаления первого символа из ячейки

А если вы хотите использовать одну формулу, вы можете использовать функцию REPLACE.

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

Он просто заменяет первый символ пустым символом, используя приведенную ниже формулу.

= ЗАМЕНИТЬ (A2,1,1,"")

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

С помощью функции REPLACE вы можете заменить символ или несколько символов, используя начальную позицию символа (символов).

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

Поскольку мы указали начальную позицию 1 и только 1 символ для замены, мы пропустили указание символа для замены.

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

Нет такой разницы между этим методом и методом, который мы использовали выше.

Если вы хотите удалить более одного символа, просто замените 1 (символы для замены) на 2, 3, 4 для любых символов, которые вы хотите удалить.

3. Объедините MID и LEN, чтобы получить значения после удаления первого символа

Третья формула, которую мы можем использовать, представляет собой комбинацию MID и LEN. sЭта комбинация работает точно так же, как два метода, которые мы использовали выше.

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

как рассчитывается эта формула

В этой формуле ДЛСТР возвращает общее количество символов в строке.

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

Поэтому, когда LEN возвращает 4 в качестве общего количества символов, мы вычитаем из него 1.

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

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

4. Удалить первый символ с текстом в столбце из ячейки

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

Используя TEXT to COLUMN, вы можете разделить первый символ и остальные символы на два разных столбца, а не удалять их.

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

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

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

5. Применение мгновенной заливки для удаления первого символа с помощью сочетания клавиш

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

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

Прежде всего, в ячейку A1 введите первый текст, пропустив первый символ исходного текста.
После этого нажмите Enter и перейдите ко второй ячейке в столбце.
Теперь нажмите сочетание клавиш Control + E, чтобы применить флэш-заливку.

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

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

Простыми словами, когда вы вводили текст в первую ячейку, вы пропускали первый символ из текста…

… и флэш-заливка по тому же шаблону возвращает текст в остальных ячейках без первого символа

6. UDF для удаления первых (n) символов из ячейки

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

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

Сначала откройте базовый визуальный редактор из своей книги с помощью клавиш Alt + F11.
После этого вставьте модуль и вставьте в него приведенный выше код.
Теперь вернитесь к своему рабочему листу и вставьте следующую формулу.

Открытая функция removeFirstC(rng As String, cnt As Long)

removeFirstC = Right(rng, Len(rng) - cnt)

как работает этот UDF

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

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

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

Пример файла

Не всегда первый персонаж будет настоящим персонажем. Иногда это может быть нежелательное пространство. В этом случае вы можете удалить его с помощью функции TRIM.

TRIM удаляет ненужные символы в начале и в конце текстовой строки.

=ОТРЕЗАТЬ(" Пунит")

В конце

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

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

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

Если вы спросите меня, мне больше всего нравятся методы 1 и 5. Но здесь вы должны сказать мне одну вещь.

Какой метод вам нравится больше всего?

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

Вы должны читать это как:

Об авторе

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

27 мыслей

Оставить комментарий Отменить ответ

Спасибо, первое решение было идеальным, очень умным.

Спасибо автору этой статьи — она помогла довольно быстро!

Спасибо! Первое решение было идеальным и работало как шарм.

Спасибо за помощь

это здорово. СПАСИБО

Пришел научиться отрубать префикс ячейки, и комбинация право/длина сработала прекрасно. Спасибо!!

Ты звезда! Это экономит массу работы и кликов! Все методы отличные! Большое спасибо!

мы все звезды.

Гений!! Спасибо, вы сэкономили мне столько времени, тратя этот первый символ, чтобы иметь возможность запускать Vlookup в электронной таблице.

Отличная статья! Очень помог мне в моем проекте! Большое спасибо

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

Текст в столбцы > Фиксированная ширина > Установите разрыв на 2 > Выберите «Не импортировать столбец (пропустить) для первого столбца > Выберите текст для второго столбца и место назначения, равное первой из выбранных ячеек ($C$249 в этом случае) > Finish
и я получаю указанные выше ячейки измененными на:
0TEZP2
0TEZP3
0TEZP4
0TEZP5
0TEZP6
0TEZP7< /p>

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

Уважаемый Puneet,
Чтобы удалить более одного символа (два, три, четыре и т. д.) из строки/ячейки, тогда использование UDF является идеальным способом

Возможно, если количество удаляемых символов вводится с помощью поля ввода (cnt в приведенной ниже формуле берется из поля ввода)

removeFirstC = Right(rng, Len(rng) – cnt)

С уважением,
Арсил Хаджар

На мой взгляд, комбинация right с len намного лучше.
Текст в столбец будет моим вторым вариантом

Обычно я использую right(..), хотя иногда заканчиваю тем, что пишу промежуточную версию. Я всегда избегаю написания каких-либо VBA, насколько это возможно, поэтому UDF — не тот способ, которым я бы это сделал.

Привет, Пунит,
Комиссией был задан вопрос о подсчете ячеек с датой января из заданного списка дат. Как я могу сделать это в одной ячейке (используя массив/ или любым другим способом
Date =month() =countif()
12-01-2018 1 3
12-12-2018 12
23-01-2018 1
12-03-3018 3
31-03-3019 3
23-01-2017 1

Для меня проще всего использовать метод номер 3, но с некоторым упрощением. Если цель состоит в том, чтобы просто удалить первый символ, вам не нужно возиться с «LEN()». Третий параметр должен быть «достаточно длинным», чтобы обрабатывать все строки (скажем, 50 или 100).
Тогда
MID(A2,2,100)
работает отлично и достаточно легко пишется даже неопытными пользователями.

С другой стороны, вы должны помнить, что Flash Fill может быть более сложным в использовании. Конечно, с подготовленными вами тестовыми примерами Flash Fill работает блестяще.
Но если первая строка — «AAA1», а вторая — «A001»?
При использовании Flash Fill вы должны сначала изучить Excel, чтобы разделить текст на более мелкие части. Если вы определите первый случай как разделение на «A» и «AA1», то Flash Fill во втором случае разделит запись на «A» и 1, потому что она автоматически преобразует «001» в число. Поэтому, используя Flash Fill, вы должны сначала либо отформатировать ячейки как текст, либо — во второй строке — указать, как поступать с «A001» (поскольку правило для «AAA1» не работает).
Поэтому я предпочитаю способ №3 🙂

Мне нравятся некоторые строковые формулы, преобразование текста в столбцы, хотя Excel продолжает пытаться разделить текст еще долго после того, как вы удалили вспомогательные столбцы и т. д. Самый лучший, самый простой и быстрый способ разделить текст (или даже числа) — мгновенное заполнение. В вашем примере вставьте новый столбец C, в ячейке C2 введите 001 и нажмите Enter. Теперь просто нажмите CTRL+E, и Excel заполнит оставшуюся часть столбца той же частью текстовой строки каждой соседней ячейки в столбце B. При желании вы можете удалить столбец B без каких-либо негативных последствий для нового столбца.

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