Как в excel обрезать текст по количеству символов
Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:
- лишние пробелы перед, после или между словами (для красоты!)
- ненужные символы ("г." перед названием города)
- невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
- апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)
Давайте рассмотрим способы избавления от такого "мусора".
Замена
"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".
Вариантов решения два:
- Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
- Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Функция ПОДСТАВИТЬ
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:
- Текст в котором производим замену
- Старый текст – тот, который заменяем
- Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.
Похожее:
-
Суммирование значений по модулю (абсолютных величин чисел).Для примера представим, что у нас есть.Функция, меняющая первую букву на заглавную. Подробное.
Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 21 комментарий
готовая формула только подставить строки и кол-во символов)
спасибо!
Нужна помощь. Как удалить в каждой строчке все символы ПОСЛЕ указанного знака/знаков?
Вот это гемор, и не получается, указываю диапазон ячейки. Укорачивает но в переносит текст в другую ячейку. В гугл таблицах, два раза мышкой щелкнуть.
Здравствуйте!
Есть колонка в Екселе с текстовыми значениями типа 00001, 00001А, 00624/1, 00682А, 01082А, 00240-1 и т.п. Требуется во всех значениях убрать впереди все нули. Но только впереди. Не подскажете, как это сделать? Спасибо.
Здравствуйте!
Есть колонка в Excel с текстовыми значениями типа 38/18, 35/35, 32/25 и т.п. Требуется во всех значениях убрать впереди цифры и дробь. Точно также, только убрать сзади. Не подскажете, как это сделать? Спасибо.
Добрый день! Подскажите как текст в одной ячейке: Иванов Иван Иванович разбить на три ячейки: 1. Иванов 2. Иван 3. Иванович
Добрый день, подскажите, пожалуйста, как сократить количество текста в ячейках столбца на 15% с сохранением логического смысла?
Здравствуйте! У меня в столбике, в каждой строке, к примеру, по 15 символов (к примеру, 20.01.2020 17.45) а нужно сократить до 10 (чтобы осталась только дата) , но не в соседний столбец перенеся, как подразумевает функция ЛЕВСИМВ, а просто сократить в том же, не подскажете, как это сделать?
Здравствуйте!
Есть столбец в Екселе со значениями типа 000010531, 000401325, 002000045, 000104573, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.
В некоторых случаях существует список строк, который включает в себя несколько длинных строк, и для аккуратности вы можете усечь строки до фиксированной длины, как показано ниже. Есть ли у вас какие-нибудь хитрости, чтобы быстро решить эту проблему в Excel?
Обрезать строки с помощью Kutools for Excel
Обрезать строки с помощью формулы
Например, здесь я просто усекаю строки до шести цифр слева, и приведенная ниже формула может оказать услугу.
Выберите пустую ячейку рядом со строкой, которую вы хотите усечь, и введите эту формулу = ВЛЕВО (A1,6) (A1 - это строка, которую вы используете, а 6 означает усечение строки до шести символов), затем перетащите дескриптор заполнения над ячейками, которым также нужна эта формула. Смотрите скриншот:
Чаевые: Если вы хотите обрезать строку справа или посередине, вы можете применить формулу ниже ：
Усечь до 4 цифр с правой стороны: = ПРАВЫЙ (A1,4)
Усечь четвертый символ до трех цифр: = MID (A1,4,3)
Легко и быстро удаляйте символы по одной позиции из каждой ячейки в Excel
Обрезать строки с текстом до столбцов
В Excel вы также можете применить функцию Text to Columns для разделения строки на столбцы на основе фиксированной ширины.
1. Выберите строки, которые хотите обрезать, и нажмите Данные > Текст в столбцы. Смотрите скриншот:
2. в шаг 1 мастера преобразования текста в столбцы, проверить фиксированная ширина вариант. Смотрите скриншот:
3. Нажмите Следующая> , чтобы перейти к шаг 2 мастера, и перейти к Предварительный просмотр данных раздел, щелкните место строки, которую вы хотите разделить, чтобы создать линию со стрелкой. Смотрите скриншот:
4. Нажмите Следующая> , чтобы перейти к шаг 3 мастераи укажите ячейку в качестве места назначения для разделения. Смотрите скриншот:
5. Нажмите Завершить. И строки были разделены на фиксированную ширину, как вам нужно.
Обрезать строки с помощью Kutools for Excel
Собственно, если у вас есть Kutools for Excel - удобный инструмент, можно применять его Удалить по позиции утилита для быстрого удаления символов, которые вам не нужны для усечения строки.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите строки, которые нужно обрезать, щелкните Kutools > Текст > Удалить по позиции. Смотрите скриншот:
2. В диалоговом окне запроса введите количество символов, которые вы хотите удалить, в Номера текстовое поле и выберите вариант из Позиция раздел, который определяет позицию, из которой вы удаляете. Смотрите скриншот:
3. Нажмите Ok or Применить, строки были обрезаны по мере необходимости.
В этом руководстве демонстрируются различные быстрые приемы удаления первых или последних N символов из ячейки или строки в Excel.
 | 1. Удалите первые N символов с помощью формулы / UDF 2. Удалите последние N символов с помощью формулы / UDF. 3. Пакетное удаление N символов слева, справа или посередине без формул. 4. Загрузите образец файла. |
1. Удалите первые N символов из формул.
В этой части я представляю три различных способа быстрого удаления первых N символов из строки.
1.1 Удалить первые N символов с помощью функции RIGHT / REPLACE
>> Объедините функции ВПРАВО и ДЛСТР, чтобы удалить первые N символов
Синтаксис и аргументы формулы
Формула: = ПРАВО (строка_ячейки; ДЛИН (ячейка_строки) -n_символ)
Ссылка: string_cell: ячейка, которую вы используете для удаления символов
n_character: количество символов, которые вы хотите удалить
Пример: удалить первые 2 символа из строки в ячейке A2, скопировать и вставить формулу
= ПРАВО (A2; LEN (A2) -2)
пресс Enter ключ
>> REPLACE для удаления первых N символов
Синтаксис и аргументы формулы
Формула: = REPLACE (Old_text, Strat_n, Num_Chars, New_text)
Ссылка: Old_text: ячейка, которую вы используете для замены символов
Old_text: ячейка, которую вы используете для замены символов
Start_n: позиция символа в old_text, которую вы хотите заменить на new_text
Num_Chars: количество символов в старом тексте, которое вы хотите заменить новым_текстом
New_text: текст, который заменит old_text
Пример: замените первые два символа ничем в ячейке A6, скопируйте и вставьте эту формулу:
= ЗАМЕНИТЬ (A6,1,2; "")
пресс Enter ключ
1.2 Удалить первые N символов с помощью пользовательской функции
1. Нажмите Alt + F11 ключи, чтобы включить окно Microsoft Visual Basic для приложений, затем нажмите Вставить > Модуль для создания модуля.
2. Скопируйте приведенный ниже код и вставьте его в Модуль скрипты.
Код VBA: удалить первые n символов
3. Сохраните код и вернитесь к используемому листу. Например, удалите первые 3 символа из строки в ячейке A11, скопируйте и вставьте эту формулу
= RemoveFirstC (A11,3)
пресс Enter ключ
Этот инструмент удаления символов повышает вашу эффективность на 90%, оставляя много времени, чтобы наслаждаться жизнью
▲ Попрощайтесь с изменением и запоминанием формул, дайте вам полноценный отдых для мозга.
▲ Кроме этого инструмента, есть 228 дополнительных инструментов (включая более 30 инструментов для редактирования текста) еще в Kutools for Excel, который может решить ваши 80% головоломки Excel.
▲ Станьте экспертом по Excel за 5 минут, получите признание и продвижение по службе.
▲ 110000+ высокоэффективных сотрудников и выбор 300+ всемирно известных компаний.
30-дневная бесплатная пробная версия, кредитная карта не требуется Читать далее Загрузить сейчас
2. Удалите последние N символов из формул.
В этой части есть два способа удалить последние N символов из строки в Excel.
2.1 Удалить последние N символов с помощью функции LEFT
Синтаксис и аргументы формулы
Формула: = ЛЕВЫЙ (ячейка_строки, ДЛСТР (ячейка_строки) -Число_знаков)
Ссылка: string_cell: ячейка, которую вы используете для удаления символов
n_character: количество символов, которые вы хотите удалить
Пример: удалить последние 2 символа из строки в ячейке A2, скопировать и вставить формулу
= ЛЕВЫЙ (A2; LEN (A2) -2)
пресс Enter ключ
2.2 Удалить последние N символов с помощью пользовательской функции
1. Нажмите Alt + F11 ключи, чтобы включить Microsoft Visual Basic для окна приложений, нажмите Вставить > Модуль для создания модуля.
2. Скопируйте приведенный ниже код и вставьте его в сценарий модуля.
Код VBA: удалить последние n символов
3. Сохраните код и вернитесь к используемому листу. Например, удалите последние 3 символа из строки в ячейке A5, введите эту формулу
= RemoveLastC (A5,3)
пресс Enter ключ
Вы хотите повысить зарплату и много времени проводить с семьей?
Вкладка Office повышает эффективность работы Microsoft Office на 50% прямо сейчас
Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.
По сравнению с известными браузерами инструмент с вкладками в Office Tab более мощный и эффективный.
Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.
Если вы обычно работаете с несколькими документами, вкладка Office поможет вам сэкономить время.
30-дневная бесплатная пробная версия , кредитная карта не требуется.
3. Удалите N символов слева, справа или посередине без формул.
Если вы не хотите тратить время на запоминание формул для решения задачи удаления N символов слева или справа от строки, Удалить по позиции полезности Kutools for Excel справится с этой задачей одним щелчком мыши без каких-либо формул.
Теперь заставьте вас работать быстрее и лучше других, с легкостью получите продвижение по службе
35 инструментов для редактирования текста для Excel Повысьте продуктивность работы с текстом на 90% и оставьте много времени, чтобы наслаждаться жизнью
Эти инструменты - лишь одна часть Kutools for Excel, есть еще 194 инструмента, которые могут решить 80% головоломок Excel за вас.
После бесплатной установки Kutools for Excel, сделайте следующее:
Выберите ячейки, из которых вы хотите удалить символы, применив утилиту, нажав Kutools > Текст > Удалить по позиции.
3.1 Удалить первые / последние N символов без формулы
>> Удалить первые N символов
1) В Номера текстовое поле, введите количество символов, которые вы хотите удалить из строки, в этом примере я сначала удалю 3 символы из строк.
2) Проверить Слева вариант в Позиция .
Результат отображается как:
>> Удалить последние N символов
1) В Номера текстовое поле, введите количество символов, которые вы хотите удалить из строки, в этом примере я удалю последний 3 символы из строк.
2) Проверить Справа вариант в Позиция .
Результат отображается как:
3.2 Удалить N символов из средней определенной позиции без формулы
Но иногда вам может потребоваться удалить определенное количество символов из середины строки.
Пример: Удалить 3 символа, начиная с третьего символа из строки.
1) В Число текст, введите количество символов, которое вы хотите удалить из строк, здесь я удалю 3 символы.
2) Проверить Указывать вариант, затем введите номер, с которого вы хотите удалить начало строки, рядом с текстовым полем в Позиция раздел, здесь я удалю символы из третьего символа.
Результат отображается как:
Удалять символы по позиции без формул
Наконечник: Если вы хотите удалить все числовые, буквенные, нечисловые, не-буквенные или определенные символы из ячеек, вы можете применить Kutools for Excel's Удалить символы утилита.
Читайте также: