Как удалить текст из ячейки в Excel и оставить только цифры
Обновлено: 21.11.2024
<р>. кто действительно увлечен изучением 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 без каких-либо негативных последствий для нового столбца.
Представьте себе: вы получаете необработанные данные для анализа и обнаруживаете, что числа смешаны с текстом в одном столбце. В большинстве случаев, безусловно, будет удобнее разместить их в отдельных столбцах для более подробного изучения.
Если вы работаете с однородными данными, вы, вероятно, могли бы использовать функции LEFT, RIGHT и MID для извлечения одинакового количества символов из одной и той же позиции. Но это идеальный сценарий для лабораторных испытаний. В реальной жизни вы, скорее всего, будете иметь дело с непохожими данными, где числа стоят перед текстом, после текста или между текстом. В приведенных ниже примерах представлены решения именно для этого случая.
Как удалить текст и сохранить числа в ячейках Excel
Решение работает в Excel 365, Excel 2021 и Excel 2019
В Microsoft Excel 2019 появилось несколько новых функций, недоступных в более ранних версиях, и мы собираемся использовать одну из таких функций, а именно TEXTJOIN, для удаления текстовых символов из ячейки, содержащей числа.
Общая формула:
В Excel 365 и 2021 этот вариант также будет работать:
На первый взгляд формулы могут показаться немного пугающими, но они работают :)
Например, чтобы удалить текст из чисел в A2, введите одну из приведенных ниже формул в B2, а затем скопируйте ее в нужное количество ячеек.
В Excel 365 – 2019:
=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2))), 1) *1, ""))
В Excel 2019 его необходимо ввести как формулу массива с помощью Ctrl + Shift + Enter . В динамическом массиве Excel это работает как обычная формула, завершаемая клавишей Enter.
В Excel 365 и 2021:
=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(A2)), 1) *1, ""))
В результате из ячейки удаляются все текстовые символы, а числа сохраняются:
Как работает эта формула:
Чтобы лучше понять логику, давайте начнем исследовать формулу изнутри:
Вы используете ROW(INDIRECT("1:"&LEN(string))) или SEQUENCE(LEN(string)) для создания последовательности чисел, соответствующих общему количеству символов в исходной строке, а затем передаете эти порядковые номера в MID функционируют как начальные номера. В B2 эта часть формулы выглядит следующим образом:
Функция MID извлекает каждый символ из A2, начиная с самого первого, и возвращает их в виде массива:
Функция ЕСЛИОШИБКА обрабатывает эти ошибки и заменяет их пустыми строками:
Этот окончательный массив передается функции TEXTJOIN, которая объединяет непустые значения в массиве (аргумент ignore_empty имеет значение TRUE), используя пустую строку ("") в качестве разделителя:< /p>
Совет. Для Excel 2016–2007 решение тоже существует, но формула гораздо сложнее. Вы можете найти его в этом руководстве: Как извлечь числа в Excel.
Пользовательская функция для удаления текста из чисел
Решение работает для всех версий Excel
Если вы используете старую версию Excel или считаете приведенные выше формулы слишком сложными для запоминания, ничто не мешает вам создать собственную функцию с более простым синтаксисом и удобным для пользователя именем, например RemoveText. . Пользовательскую функцию (UDF) можно записать двумя способами:
Код VBA 1:
Здесь мы просматриваем каждый символ в исходной строке один за другим и проверяем, является ли он числовым или нет. Если это число, к результирующей строке добавляется символ.
Код 2 VBA:
Код создает объект для обработки регулярного выражения. Используя RegExp, мы удаляем из исходной строки все символы, кроме цифр 0–9.
На небольших листах оба кода работают одинаково хорошо. На больших листах, где функция вызывается сотни или тысячи раз, код 2, использующий VBScript.RegExp, будет работать быстрее.
Какой бы подход вы ни выбрали, с точки зрения конечного пользователя функция удаления текста и сохранения чисел проста:
Например, чтобы удалить нечисловые символы из ячейки A2, используйте формулу в ячейке B2:
Просто скопируйте его вниз по столбцу, и вы получите следующий результат:
Примечание. И собственные формулы, и пользовательская функция выводят числовую строку. Чтобы превратить его в число, умножьте результат на 1, или добавьте ноль, или заверните формулу в функцию ЗНАЧЕНИЕ. Например:
Как удалить числа из текстовой строки в Excel
Решение работает в Excel 365, Excel 2021 и Excel 2019
Формулы для удаления чисел из буквенно-цифровой строки очень похожи на те, что обсуждались в предыдущем примере.
Для Excel 365 – 2019:
TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) * 1), MID(ячейка, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ячейка))), 1), ""))
В Excel 2019 не забудьте сделать формулу массива, нажав одновременно клавиши Ctrl + Shift + Enter.
Для Excel 365 и 2021:
TEXTJOIN("", TRUE, IF(ISERROR(MID(cell, SEQUENCE(LEN(cell 1) *1), MID(cell< /em>, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(ячейка)), 1), ""))
Например, чтобы удалить числа из строки в A2, используйте следующую формулу:
=TEXTJOIN("", ИСТИНА, ЕСЛИ(ЕОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))), 1) *1), СРЕД(A2, СТРОКА(ДВССЫЛ("1 :"&ДЛСТР(A2))), 1), ""))
=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "" ))
В результате из ячейки удаляются все числа, а текстовые символы сохраняются:
Как показано на снимке экрана выше, формула удаляет числовые символы из любой позиции в строке: в начале, в конце и в середине. Однако есть одно предостережение: если строка начинается с числа, за которым следует пробел, этот пробел сохраняется, что создает проблему начальных пробелов (как в B2).
Чтобы избавиться от лишних пробелов перед текстом, заключите формулу в функцию TRIM следующим образом:
=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))
Теперь ваши результаты просто идеальны!
Как работает эта формула:
По сути, формула работает так же, как описано в предыдущем примере. Отличие в том, что из итогового массива, переданного функции TEXTJOIN, вам нужно удалить числа, а не текст. Для этого мы используем комбинацию функций ЕСЛИ и ЕОШИБКА.
Функция ЕОШИБКА перехватывает ошибки и передает результирующий массив логических значений в ЕСЛИ:
Когда функция ЕСЛИ принимает значение ИСТИНА (ошибка), она вставляет соответствующий текстовый символ в обрабатываемый массив с помощью другой функции MID. Когда функция ЕСЛИ встречает ЛОЖЬ (число), она заменяет его пустой строкой:
Этот окончательный массив передается в TEXTJOIN, поэтому он объединяет текстовые символы и выводит результат.
Пользовательская функция для удаления чисел из текста
Решение работает для всех версий Excel
Помня о том, что надежная формула должна быть простой, я приведу код определяемой пользователем функции (UDF), чтобы убрать любые числовые символы.
Код VBA 1:
Код 2 VBA:
Как и в случае с функцией RemoveText, второй код лучше использовать на больших листах для оптимизации производительности.
После добавления кода в книгу вы можете удалить все числовые символы из ячейки с помощью этой пользовательской функции:
В нашем случае формула в ячейке B2 выглядит так:
Чтобы обрезать начальные пробелы, если они есть, вложите пользовательскую функцию в TRIM, как в нативную формулу:
Разделить числа и текст на отдельные столбцы
В ситуации, когда вы хотите разделить текст и числа на два столбца, было бы неплохо выполнить эту работу с помощью одной формулы, согласны? Для этого мы просто объединяем код функций RemoveText и RemoveNumbers в одну функцию с именем SplitTextNumbers или просто Split или как вам больше нравится :)
Код VBA 1:
Код 2 VBA:
Наша новая пользовательская функция требует два аргумента:
Где is_remove_text — логическое значение, указывающее, какие символы следует удалить:
- TRUE или 1 – удалить текст и оставить числа.
- FALSE или 0 — удалить числа и оставить текст
Для нашего примера набора данных формулы имеют следующий вид:
Чтобы удалить нечисловые символы:
Чтобы удалить числовые символы:
=SplitTextNumbers(A2, FALSE)
Совет. Чтобы избежать потенциальной проблемы с начальными пробелами, я рекомендую всегда заключать формулу, удаляющую числа, в функцию TRIM:
Специальный инструмент для удаления чисел или текста
Для тех, кто не любит излишне усложнять, я покажу наш собственный способ удаления текста или чисел в Excel.
Предполагая, что наш Ultimate Suite добавлен на вашу ленту Excel, вы делаете следующее:
Совет. Если результаты содержат начальные пробелы, инструмент «Обрезать пробелы» мгновенно их удалит.
Вот как удалить текст или числовые символы из строки в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
У меня есть несколько ячеек с данными, которые содержат как буквы, так и цифры (4H, 8V, 4FH и т. д.). Я хочу удалить только текстовые символы из этих ячеек и добавить оставшиеся числовые значения вместе в серию. ячеек с этим типом данных.
Если ваш ответ связан с использованием макроса или VB, предоставьте ссылку на то, как использовать формулу. Я никогда не использовал макрос или VB.
Факты об Excel
Нет! при использовании точного совпадения таблица ВПР может располагаться в любом порядке. Самые продаваемые товары в верхней части на самом деле самые лучшие.
Альфа-лягушка
MrExcel MVP
Ваши числа всегда состоят из одной цифры и стоят в начале текста, как следует из ваших примеров. Если да.
Если ваши данные находятся в столбце A, поместите их в B1 и скопируйте вниз по столбцу B для каждого элемента данных.
hardeep.kanwar
Известный участник
Я немного новичок в Excel, но.
У меня есть несколько ячеек с данными, которые содержат как буквы, так и цифры (4H, 8V, 4FH и т. д.). Я хочу удалить только текстовые символы из этих ячеек и добавить оставшиеся числовые значения вместе в серию. ячеек с этим типом данных.
Если ваш ответ связан с использованием макроса или VB, предоставьте ссылку на то, как использовать формулу. Я никогда не использовал макрос или VB.
hardeep.kanwar
Известный участник
Я немного новичок в Excel, но.
У меня есть несколько ячеек с данными, которые содержат как буквы, так и цифры (4H, 8V, 4FH и т. д.). Я хочу удалить только текстовые символы из этих ячеек и добавить оставшиеся числовые значения вместе в серию. ячеек с этим типом данных.
Если ваш ответ связан с использованием макроса или VB, предоставьте ссылку на то, как использовать формулу. Я никогда не использовал макрос или VB.
ПэддиД
MrExcel MVP
".) Я хочу удалить только текстовые символы из этих ячеек и добавить оставшиеся числовые значения вместе в ряд ячеек с этим типом данных."
Не могли бы вы привести пример.
FWIW, и в качестве примера того, что можно сделать, если у вас есть диапазон строк в a1:a5 формы nC (например, 1A, 2B и т. д.), то:
<р>. суммирует числа.леон6782
Новый участник
Хорошо, вот типичный пример. У меня есть ряд ячеек со следующим содержимым 4S , 4FH , 10H, 2V, 7P.
Я ищу формулу, которая удалит буквы из этих ячеек, а затем вернет сумму только чисел в ячейках.
Функция LEFT не работает, потому что иногда мне нужна только первая цифра, а иногда это две левые цифры.
Я знаю, что функция CLEAN удаляет все непечатаемые символы, а функция TRIM удаляет все пробелы. Разве нет функции, которая удаляет все буквы от A до Z?
Как работают эти надстройки? Если я куплю надстройку и использую ее в создаваемой мной электронной таблице, будет ли эта же электронная таблица правильно открываться на чужом компьютере, который не приобрел такую же надстройку?
При работе с Excel мы можем извлекать только те части текстовой строки, которые нам нужны, с помощью функций ЗАМЕНИТЬ, ПРАВИЛЬНО или ДЛСТР. Это пошаговое руководство поможет пользователям Excel любого уровня удалить символы с левой стороны ячейки двумя разными способами.
Рисунок 1. Конечный результат: удаление символов с левой стороны ячейки
Формула 1: =ЗАМЕНИТЬ(C4,1,D4,"")
Формула 2: = ПРАВИЛЬНО(C4,LEN(C4)-D4)
Синтаксис функции REPLACE
Функция REPLACE заменяет определенную часть текстовой строки другой текстовой строкой в зависимости от заданного количества символов
= ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст)
- old_text — любой текст, который мы хотим изменить или модифицировать
- start_num — начальная позиция символа в old_text, которую мы хотим заменить на new_text
- num_chars — количество символов в old_text, которые мы хотим заменить на new_text
- новый_текст — текст, который заменит символы в старом_тексте.
Синтаксис функции RIGHT
RIGHT извлекает подстроку из правой части текстовой строки
= ПРАВО ( текст ,[ число_знаков ])
- text – текстовая строка, содержащая символы или подстроку, которую мы хотим извлечь
- num_chars – Необязательно ; определяет количество символов, которые мы хотим извлечь
Синтаксис функции ДЛСТР
LEN возвращает длину текстовой строки в количестве символов
= ДЛСТР ( текст )
- текст – текст, длину которого мы хотим определить. Пробелы считаются символами.
Настройка наших данных
Наша таблица содержит список идентификаторов персонала (столбец B), код (столбец C) и num_chars (столбец D). Num_chars содержит количество символов, которые мы хотим удалить из левой части кода. Результирующая текстовая строка будет записана в столбце E в разделе «Результат».
Рисунок 2. Пример данных для удаления символов с левой стороны ячейки
Удалить символы слева с помощью REPLACE
Нам нужны только определенные части кода в столбце C. Чтобы удалить символы с левой стороны, мы используем функцию REPLACE. Давайте выполним следующие шаги:
Шаг 1 . Выберите ячейку E4
Шаг 2 . Введите формулу: =ЗАМЕНИТЬ(C4,1,D4,"")
Шаг 3. Нажмите клавишу ВВОД
Шаг 4. Скопируйте формулу из ячейки E4 в ячейки E5:E7, нажав значок «+» в правом нижнем углу ячейки E4 и перетащив ее вниз
Old_text — это код в столбце C, а start_num — 1, а num_chars — D4 или 7. Наша формула заменяет нежелательные символы пустой строкой «» , начиная с первого символа и заканчивая седьмым символом.
В результате первые семь символов удаляются, оставляя подстроку «ACC01» в ячейке E4.
Рисунок 3. Ввод формулы с помощью REPLACE
При копировании формулы в последующие ячейки столбец E заполняется нужными подстроками, как показано ниже.
Рисунок 4. Вывод: удаление символов с левой стороны ячейки
Удалить символы с левой стороны, используя RIGHT и LEN
Существует альтернативное решение для удаления символов с левой стороны с помощью функций ПРАВО и ДЛСТР. Давайте выполним следующие шаги:
Шаг 1 . Выберите ячейку E4
Шаг 2 . Введите формулу: =ПРАВО(C4,ДЛСТР(C4)-D4)
Шаг 3. Нажмите клавишу ВВОД
Шаг 4. Скопируйте формулу из ячейки E4 в ячейки E5:E7, нажав значок «+» в правом нижнем углу ячейки E4 и перетащив ее вниз
Наша формула извлекает подстроку из правой части текста в C4. Количество символов определяется функцией LEN LEN(C4)-D4 . Длина C4 — 12 символов, а D4 — 7. Следовательно, количество символов, которые мы хотим извлечь из правой части, равно (12 — 7) или 5.
В результате первые семь символов удаляются, оставляя пять символов справа от C4, то есть «ACC01». Результаты такие же, как и в предыдущем методе с использованием REPLACE.
Рисунок 5. Ввод формулы с помощью ПРАВИЛЬНОЙ и ДЛИННОЙ СТРЕЛКИ
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
Я хочу удалить символ из столбца; чтение справа удалить символы, включая первый - например: 3HAVANA-BU-10/12 должен вернуть 3HAVANA-BU
Я работаю со столбцом текстов переменной длины и хочу удалить первые три символа слева, а остальные оставить. Я не могу использовать команду ПРАВИЛЬНО, потому что количество символов, которое я хочу сохранить, непостоянно.
Данные в столбце C содержат непечатаемые символы до и после данных, содержащихся в каждой ячейке. В ячейке G2 введите формулу для удаления всех непечатаемых символов
Читайте также: