Vba excel замена символов в строке

Обновлено: 07.01.2025

Эта инструкция познакомит Вас с тремя способами удалить возвраты каретки из ячеек в Excel. Вы также узнаете, как заменить символы переноса строки другими символами. Все предложенные решения работают в Excel 2013, 2010, 2007 и 2003.

Переносы строк могут появиться в тексте по разным причинам. Обычно возвраты каретки встречаются в рабочей книге, например, когда текст скопирован с веб-страницы, когда они уже есть в рабочей книге, полученной от клиента, или, когда мы сами добавляем их нажатием клавиш Alt+Enter.

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

Все три представленных способа довольно быстры. Выбирайте тот, который Вам больше подходит:

Компьютеры и программное обеспечение для работы с текстами разрабатывались с учётом особенностей печатных машинок. Вот почему теперь для обозначения разрыва строки используют два различных непечатаемых символа: Возврат каретки (Carriage return, CR или ASCII код 13) и Перевод строки (Line feed, LF или ASCII код 10). В Windows используются оба символа вместе, а в системах *NIX применяется только перевод строки.

Будьте внимательны: В Excel встречаются оба варианта. При импорте из файлов .txt или .csv данные обычно содержат возвраты каретки и переводы строки. Когда перенос строки вводится вручную нажатием Alt+Enter, Excel вставляет только символ перевода строки. Если же файл .csv получен от поклонника Linux, Unix или другой подобной системы, то готовьтесь к встрече только с символом перевода строки.

Удаляем возвраты каретки вручную

Плюсы: Этот способ самый быстрый.

Минусы: Никаких дополнительных плюшек 🙁

Вот так можно удалить переносы строк при помощи инструмента «Найти и заменить»:

Удаляем переносы строк при помощи формул Excel

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

В некоторых случаях переносы строк необходимо удалять, чтобы в дальнейшем использовать текст в качестве аргументов функций, не внося изменения в исходные ячейки. Результат можно использовать, например, как аргумент функции ПРОСМОТР (LOOKUP).

Минусы: Потребуется создать вспомогательный столбец и выполнить множество дополнительных шагов.

Добавьте вспомогательный столбец в конце данных. В нашем примере он будет называться 1 line.
В первой ячейке вспомогательного столбца (C2) введите формулу для удаления/замены переносов строк. Ниже приведены несколько полезных формул для различных случаев:
Эта формула подходит для использования с комбинациями возврат каретки / перенос строки, характерными для Windows и для UNIX.

Выделите все ячейки в столбце C и нажатием Ctrl+C скопируйте данные в буфер обмена.
Далее выделите ячейку B2, нажмите сочетание клавиш Shift+F10 и выберите Вставить (Insert).
Удалите вспомогательный столбец.

Удаляем переносы строк при помощи макроса VBA

Плюсы: Создаём один раз – используем снова и снова с любой рабочей книгой.

Минусы: Требуется хотя бы базовое знание VBA.

Макрос VBA из следующего примера удаляет возвраты каретки из всех ячеек на активном листе.

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

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

Синтаксис

Замените(выражение, найти, заменить ,[ начало , [ считать , [ сравнить ]])

Синтаксис функции Replace содержит следующие именованные аргументы:

Part	Описание
выражение	Обязательная часть. Строковое выражение, содержащее заменяемую подстроку.
поиск	Обязательный. Искомая подстрока.
замена	Обязательный. Подстрока замены.
начало	Необязательно. Начните позицию для подстройки выражения, которая будет искаться и возвращаться. Если элемент опущен, предполагается, что он равен 1.
count	Необязательное. Число выполняемых замен подстроки. Если значение опущено, значение по умолчанию — -1, что означает, что вы можете сделать все возможные замены.
compare	Необязательно. Представляет собой числовое значение, указывающее вид сравнения, которое выполняется при оценке подстрок. Значения см. в разделе "Параметры".

Параметры

Аргумент compare может принимать следующие значения:

Константа	Значение	Описание
vbUseCompareOption	–1	Выполняет сравнение, используя параметр оператора Option Compare.
vbBinaryCompare	0	Выполняется двоичное сравнение.
vbTextCompare	1	Выполняется текстовое сравнение.
vbDatabaseCompare	2	Только Microsoft Access. Выполняется сравнение на основе сведений из базы данных.

Возвращаемые значения

Функция Replace возвращает следующие значения:

Если	Функция "Replace" возвращает
Элемент expression имеет нулевую длину	Пустая строка ("")
Элемент expression равен Null	Ошибка.
Элемент find имеет нулевую длину	Копия expression.
Элемент replace имеет нулевую длину	Копирование выражений со всеми вхождениями удаленной находки.
начните > Len(выражение)	Строка нулевой длины. Замена строк начинается в позиции, указанной в начале.
Элемент count равен 0	Копия expression.

Примечания

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

См. также

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

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

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

Параметры

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

Обязательный. Подстрока, которую требуется найти.

Обязательный. Подстрока для замены.

Необязательный элемент. Позиция в Expression , с которой начинается подстрока, используемая для замены. Возвращаемое значение Replace представляет собой строку, начинающуюся с Start с соответствующими заменами. Если этот аргумент не указан, используется значение 1.

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

Необязательный элемент. Числовое значение, показывающее вид сравнения, используемое при оценке подстрок. См. значения в разделе "Параметры".

Возвращаемое значение

Одно из следующих значений.

If	Функция Replace возвращает
Параметр Find является пустой строкой или значением Nothing	Копия выражения Expression
Параметр Replace имеет нулевую длину	Копия выражения Expression без вхождений строки Find
Строка Expression является пустой строкой или значением Nothing , или значение Start больше длины выражения Expression	Nothing
Count равно 0	Копия выражения Expression

Исключения

Count < –1 или Start <= 0.

Примеры

В этом примере демонстрируется Replace функция.

Возвращаемое значение Replace функции — это строка, которая начинается с позиции, заданной параметром, Start и завершается в конце Expression строки с подстановкой, заданной Find Replace значениями и.

в следующей таблице перечислены функции, которые Visual Basic предоставляет в Microsoft.VisualBasic.Strings классе для поиска и работы со строками. их можно рассматривать как Visual Basic встроенных функций; то есть вам не нужно вызывать их как явные члены класса, как показано в примерах. Дополнительные методы и в некоторых случаях дополняют методы, доступны в System.String классе.

Можно использовать инструкцию Option Compare , чтобы задать, сравниваются ли строки с использованием порядка сортировки текста без учета регистра, определенного языковым стандартом системы ( Text ) или внутренними двоичными представлениями символов ( 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 ) не отображаются.

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