Vba excel удалить последний символ в строке
Эта функция извлекает левую часть строки с заданным количеством символов.
Синтаксис функции Left:
Left(строка, длина)
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается левая часть;
- длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.
Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.
Функция Mid
Эта функция извлекает часть строки с заданным количеством символов, начиная с указанного символа (по номеру).
Синтаксис функции Mid:
Mid(строка, начало, [длина])
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается часть строки;
- начало — обязательный аргумент: числовое выражение, указывающее положение символа в строке, с которого начинается извлекаемая часть;
- длина — необязательный аргумент: числовое выражение, указывающее количество вырезаемых символов.
Если аргумент «начало» больше, чем количество символов в строке, функция Mid возвращает пустую строку. Если аргумент «длина» опущен или его значение превышает количество символов в строке, начиная с начального, возвращаются все символы от начальной позиции до конца строки.
Функция Right
Эта функция извлекает правую часть строки с заданным количеством символов.
Синтаксис функции Right:
Right(строка, длина)
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается правая часть;
- длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.
Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.
Пример
В этом примере будем использовать все три представленные выше функции для извлечения из ФИО его составных частей. Для этого запишем в ячейку «A1» строку «Иванов Сидор Петрович», из которой вырежем отдельные компоненты и запишем их в ячейки «A2:A4».
Зачастую текст, который достается нам для работы в ячейках листа 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 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
в следующей таблице перечислены функции, которые Visual Basic предоставляет в Microsoft.VisualBasic.Strings классе для поиска и работы со строками. их можно рассматривать как Visual Basic встроенных функций, то есть нет необходимости вызывать их как явные члены класса, как показано в примерах. Дополнительные методы и в некоторых случаях дополняют методы, доступны в System.String классе.
Можно использовать инструкцию Option Compare , чтобы задать, сравниваются ли строки с использованием порядка сортировки текста без учета регистра, определенного языковым стандартом системы ( ) или внутренними двоичными представлениями символов ( Binary ). Метод сравнения текста по умолчанию — Binary .
Пример: Укасе
В данном примере функция UCase используется для возврата строки в верхнем регистре.
Пример: LTrim
В данном примере функция LTrim используется, чтобы убрать пробелы в начале, а функция RTrim — чтобы убрать пробелы в конце строковой переменной. Функция Trim в примере используется для удаления обоих типов пробелов.
Пример: mid
В этом примере функция используется Mid для возврата указанного числа символов из строки.
Пример: len
В данном примере Len используется для возврата числа знаков в строке.
Пример: InStr
В данном примере функция InStr используется для возврата позиции первого вхождения одной строки в другую.
Пример: Format
В данном примере показаны различные способы использования функции Format для форматирования значений с применением как форматов String , так и определенных пользователем форматов. Фактическое отображение системой разделителя даты ( / ), разделителя времени ( : и индикаторов AM/PM ( t и tt ) зависит от региональных параметров, применяемых кодом. При отображении времени и даты в среде разработки используется короткий формат времени и даты региональных установок кода.
Для языков, использующих 24-часовой формат, индикаторы AM/PM ( t и tt ) не отображаются.
В этом руководстве демонстрируются различные быстрые приемы удаления первых или последних 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, сделайте следующее:
Выберите ячейки, из которых вы хотите удалить символы, применив утилиту, нажав Кутулс > Текст > Удалить по позиции.
3.1 Удалить первые / последние N символов без формулы
>> Удалить первые N символов
1) В Номера текстовое поле, введите количество символов, которые вы хотите удалить из строки, в этом примере я сначала удалю 3 символы из строк.
2) Проверить Слева вариант в Позиция .
Результат отображается как:
>> Удалить последние N символов
1) В Номера текстовое поле, введите количество символов, которые вы хотите удалить из строки, в этом примере я удалю последний 3 символы из строк.
2) Проверить Справа вариант в Позиция .
Результат отображается как:
3.2 Удалить N символов из средней определенной позиции без формулы
Но иногда вам может потребоваться удалить определенное количество символов из середины строки.
Пример: Удалить 3 символа, начиная с третьего символа из строки.
1) В Число текст, введите количество символов, которое вы хотите удалить из строк, здесь я удалю 3 символы.
2) Проверить Указывать вариант, затем введите номер, с которого вы хотите удалить начало строки, рядом с текстовым полем в Позиция раздел, здесь я удалю символы из третьего символа.
Результат отображается как:
Удалять символы по позиции без формул
Наконечник: Если вы хотите удалить все числовые, буквенные, нечисловые, не-буквенные или определенные символы из ячеек, вы можете применить Kutools for Excel's Удалить символы утилита.
How To Make You Stand Out From Crowd, Easily Work And Leisurely Enjoy Life?
Kutools for Excel will averagely improve your 80% efficiency every day, make you have much time to accompany with family and have fun in life.
300 advanced tools solve 80% Excel problems, you no longer struggle to search solutions, more easily complete work.
Easily handle 1500 Excel scenarios, save at least 1 hour for you every day.
Become an Excel expert in 5 minutes, gain peoples’ recognition and opportunity for promotion.
One-click utilities handle batch processing, let you avoid thousands of mouse-clicks, say good-bye to mouse hand.
Get rid of rote memorizing formulas and complex VBA, give a rest to your brain.
110000+ high efficiency officers and 300+ world renowned companies’ choice.
Only take $39 but much worth than $4000 training of other people
30 days free trial without any limitation, full money back in 30-day without any reasons.
Как удалить не буквенно-цифровые символы в Excel?
- Удалите не буквенно-цифровые символы с помощью кода VBA
- Удалите не буквенно-цифровые символы с помощью функции, определяемой пользователем
- Удалите не буквенно-цифровые символы с помощью Kutools for Excel
Удалите не буквенно-цифровые символы с помощью кода VBA
С помощью следующего короткого кода VBA вы также можете удалить не буквенно-цифровые символы. Сделать это можно так:
1. Нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, а затем вставьте ниже код VBA в модуль:
VBA: удалить все не буквенно-цифровые символы
3, нажмите F5 ключ или щелкните Run кнопку для выполнения кода.
4. Затем выберите диапазон, из которого вы хотите удалить все не буквенно-цифровые символы во всплывающем диалоговом окне, и щелкните значок OK кнопку.
Теперь все не буквенно-цифровые символы были удалены из выбранного диапазона. Смотрите скриншот:
2 щелчка мышью, чтобы удалить все не буквенно-цифровые символы с помощью замечательного инструмента
Обычно мы помещаем курсор в ячейку, а затем вручную удаляем не буквенно-цифровые символы. Если вы знакомы с VBA или функциями, определяемыми пользователем, вы можете закодировать их и удалить все не буквенно-цифровые символы сразу. Но здесь я порекомендую замечательный инструмент, функцию удаления символов из Kutools for Excel, который может удалять все виды символов всего за 2 клика! Полнофункциональная бесплатная 30-дневная пробная версия!
Удалите не буквенно-цифровые символы с помощью функции, определяемой пользователем
Вы также можете определить настраиваемую функцию для удаления всех не буквенно-цифровых символов из ячеек в Excel.
1, нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, а затем вставьте приведенный ниже код в модуль:
Функция, определяемая пользователем: удаление всех не буквенно-цифровых символов
3. Теперь вернитесь на рабочий лист, выберите пустую ячейку и введите формулу = DeleteNonAlphaNumeric (B3), а затем перетащите загрузку дескриптора автозаполнения, чтобы применить эту формулу к другим ячейкам.
Удалите не буквенно-цифровые символы с помощью Kutools for Excel
Для двух вышеуказанных методов вам необходимо знать коды, но, как новички в Excel, большинство из нас не может использовать код, поэтому здесь я дам вам простой способ сделать это.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Если вы установили Kutools for Excel, выполните следующие действия:
1. Выберите диапазон, из которого нужно удалить не буквенно-цифровые символы, и нажмите Кутулс > Текст > Удалить символы.
2, Затем Удалить символы появится диалоговое окно, только отметьте Не буквенно-цифровые и нажмите Ok кнопку.
Теперь все не буквенно-цифровые символы были удалены из текстовых строк.
Статьи по теме:
Удалить буквенные символы из ячеек
Предположим, вам нужно удалить все альфа-символы из диапазона в Excel, например, одними данными в ячейке может быть «lucase 13654698745», а вы хотите, чтобы это было «13654698745», как вы можете быстро удалить все альфа-символы из ячеек?
Удалить нечисловые символы из ячеек
Предположим, у вас есть рабочий лист с большим количеством таких данных ABCD4578124YUIOH, и вы хотите удалить только нечисловые символы ABCDYUIOH, но сохранить числовые символы в ячейках. Конечно, вы можете удалить эти символы один за другим, но здесь вы можете быстро избавиться от нечисловых символов из ячеек.
Удалить непечатаемые символы из ячеек
Если вы работаете с файлами, которые поступают из источника, отличного от Excel, или вставляете информацию из другого приложения в ячейки листа, иногда в вашем листе могут появиться некоторые непечатаемые или непечатаемые символы. Чтобы ваш рабочий лист был чистым и красивым, вам нужно удалить непечатаемые символы.
Читайте также: