Vba excel замена кавычек
Replace – это функция, которая возвращает строку, полученную в результате замены одной подстроки в исходном строковом выражении другой подстрокой указанное количество раз.
Если замену подстроки необходимо осуществить в диапазоне ячеек, функцию Replace следует применить к значению каждой ячейки заданного диапазона. Проще замену в диапазоне ячеек произвести с помощью метода Range.Replace.
Синтаксис и параметры
Replace(expression, find, replace, [start], [count], [compare])
- expression – исходное строковое выражение, содержащее подстроку, которую необходимо заменить;
- find – искомая подстрока, подлежащая замене;
- replace – подстрока, заменяющая искомую подстроку;
- start – порядковый номер символа исходной строки, с которого необходимо начать поиск, часть строки до этого номера обрезается, по умолчанию равен 1 (необязательный параметр);
- count – количество замен подстроки, по умолчанию выполняется замена всех обнаруженных вхождений (необязательный параметр);
- compare – числовое значение, указывающее вид сравнения (необязательный параметр).
Сокращенный синтаксис функции Replace с необязательными параметрами по умолчанию:
Replace(expression, find, replace)
Параметр compare
Константа | Значение | Описание |
---|---|---|
vbUseCompareOption | -1 | используется параметр, заданный оператором Option Compare |
vbBinaryCompare | 0 | выполняется двоичное сравнение |
vbTextCompare | 1 | применяется текстовое сравнение |
По умолчанию используется двоичное (бинарное) сравнение. При таком сравнении буквенные символы в нижнем и верхнем регистрах различаются. Если необходимо провести замену подстроки независимо от регистра букв, используйте значение параметра compare – vbTextCompare (1).
Зачастую текст, который достается нам для работы в ячейках листа 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 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Доброго всем дня!
Нет ли у кого решения, как заменить в файле экселевские кавычки (" ") на типографские (« »).
На просторах ИНЕТА нашла несколько макросов, но они почему-то не работают у меня.
Нашла еще формулу массива
Но она работает только если текст выглядит к примеру:
1. ООО "Спутник".
2. Наш комбинат "Заря" расположен на краю города.
В случае если, кавычки повторяются в тексте несколько раз (ООО "Прометей" филиал ОАО "Заря") формула не работает.
Варианты использования автозамены, и введения кавычек при помощи кода (Alt 0171 (0187)) не подходят, так как информация собирается из разных файлов (разных пользователей), и замену кавычек нужно применить в Сводной таблице.
Доброго всем дня!
Нет ли у кого решения, как заменить в файле экселевские кавычки (" ") на типографские (« »).
На просторах ИНЕТА нашла несколько макросов, но они почему-то не работают у меня.
Нашла еще формулу массива
Но она работает только если текст выглядит к примеру:
1. ООО "Спутник".
2. Наш комбинат "Заря" расположен на краю города.
В случае если, кавычки повторяются в тексте несколько раз (ООО "Прометей" филиал ОАО "Заря") формула не работает.
Варианты использования автозамены, и введения кавычек при помощи кода (Alt 0171 (0187)) не подходят, так как информация собирается из разных файлов (разных пользователей), и замену кавычек нужно применить в Сводной таблице. Лорик
Но она работает только если текст выглядит к примеру:
1. ООО "Спутник".
2. Наш комбинат "Заря" расположен на краю города.
В случае если, кавычки повторяются в тексте несколько раз (ООО "Прометей" филиал ОАО "Заря") формула не работает.
Варианты использования автозамены, и введения кавычек при помощи кода (Alt 0171 (0187)) не подходят, так как информация собирается из разных файлов (разных пользователей), и замену кавычек нужно применить в Сводной таблице. Автор - Лорик
Дата добавления - 28.03.2015 в 16:21
= Мир MS Excel/Удаление кавычек - Мир MS Excel
Войти через uID
Войти через uID
Не пинайте сильно я только учусь, скажите плиз как удалить кавычки.
Есть код который формирует txt файл (см.ниже):[vba]
[/vba]Как удалить кавычки с тэгов:
1." " - удалить кавычки в начале и в конце и по одной кавычке ""windows-1251""
2." ППО РОГА И КОПЫТА""ОАО " - удалить кавычки в начале и в конце
Не пинайте сильно я только учусь, скажите плиз как удалить кавычки.
Есть код который формирует txt файл (см.ниже):[vba]
[/vba]Как удалить кавычки с тэгов:
1." " - удалить кавычки в начале и в конце и по одной кавычке ""windows-1251""
2." ППО РОГА И КОПЫТА""ОАО " - удалить кавычки в начале и в конце cobra77777
Не пинайте сильно я только учусь, скажите плиз как удалить кавычки.
Есть код который формирует txt файл (см.ниже):[vba]
[/vba]Как удалить кавычки с тэгов:
1." " - удалить кавычки в начале и в конце и по одной кавычке ""windows-1251""
2." ППО РОГА И КОПЫТА""ОАО " - удалить кавычки в начале и в конце Автор - cobra77777
Дата добавления - 05.11.2013 в 21:36
Sub Proc()
Dim i As Integer
Dim nmf As String
Dim myPath As String
Dim dra As String
i = 1
nmf = ActiveWorkbook.Name
Do While Worksheets("Data").Cells(i, 1) <> ""
myPath = ThisWorkbook.Path & "\"
Worksheets(2).Cells(3, 1) = Left(Worksheets(2).Cells(3, 1), 9) & Worksheets("data").Cells(i, 8) & Right(Worksheets(2).Cells(3, 1), 10)
Worksheets(2).Cells(5, 1) = Left(Worksheets(2).Cells(5, 1), 9) & Worksheets("data").Cells(i, 6) & Right(Worksheets(2).Cells(5, 1), 10)
Worksheets(2).Cells(6, 1) = Left(Worksheets(2).Cells(6, 1), 18) & Worksheets("data").Cells(i, 3) & Right(Worksheets(2).Cells(6, 1), 19)
dra = myPath & Worksheets("data").Cells(i, 1)
Worksheets(2).Activate
ActiveWorkbook.SaveAs Filename:=dra, FileFormat:=xlTextWindows, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
i = i + 1
Loop
Sub Proc()
Dim i As Integer
Dim nmf As String
Dim myPath As String
Dim dra As String
i = 1
nmf = ActiveWorkbook.Name
Do While Worksheets("Data").Cells(i, 1) <> ""
myPath = ThisWorkbook.Path & "\"
Worksheets(2).Cells(3, 1) = Left(Worksheets(2).Cells(3, 1), 9) & Worksheets("data").Cells(i, 8) & Right(Worksheets(2).Cells(3, 1), 10)
Worksheets(2).Cells(5, 1) = Left(Worksheets(2).Cells(5, 1), 9) & Worksheets("data").Cells(i, 6) & Right(Worksheets(2).Cells(5, 1), 10)
Worksheets(2).Cells(6, 1) = Left(Worksheets(2).Cells(6, 1), 18) & Worksheets("data").Cells(i, 3) & Right(Worksheets(2).Cells(6, 1), 19)
dra = myPath & Worksheets("data").Cells(i, 1)
Worksheets(2).Activate
ActiveWorkbook.SaveAs Filename:=dra, FileFormat:=xlTextWindows, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
i = i + 1
Loop
Sub Proc()
Dim i As Integer
Dim nmf As String
Dim myPath As String
Dim dra As String
i = 1
nmf = ActiveWorkbook.Name
Do While Worksheets("Data").Cells(i, 1) <> ""
myPath = ThisWorkbook.Path & "\"
Worksheets(2).Cells(3, 1) = Left(Worksheets(2).Cells(3, 1), 9) & Worksheets("data").Cells(i, 8) & Right(Worksheets(2).Cells(3, 1), 10)
Worksheets(2).Cells(5, 1) = Left(Worksheets(2).Cells(5, 1), 9) & Worksheets("data").Cells(i, 6) & Right(Worksheets(2).Cells(5, 1), 10)
Worksheets(2).Cells(6, 1) = Left(Worksheets(2).Cells(6, 1), 18) & Worksheets("data").Cells(i, 3) & Right(Worksheets(2).Cells(6, 1), 19)
dra = myPath & Worksheets("data").Cells(i, 1)
Worksheets(2).Activate
ActiveWorkbook.SaveAs Filename:=dra, FileFormat:=xlTextWindows, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
i = i + 1
Loop
Ну, теперь осталось от Вас ещё и файл-пример получить и "золотой ключик у нас в руках".
(Вы что, предполагаете, что для того, чтобы понять что у Вас не так работает, мы сами будем листы книги заполнять "рогами и копытами" в нужных местах?)
Ну, теперь осталось от Вас ещё и файл-пример получить и "золотой ключик у нас в руках".
(Вы что, предполагаете, что для того, чтобы понять что у Вас не так работает, мы сами будем листы книги заполнять "рогами и копытами" в нужных местах?) Alex_ST
Ну, теперь осталось от Вас ещё и файл-пример получить и "золотой ключик у нас в руках".
(Вы что, предполагаете, что для того, чтобы понять что у Вас не так работает, мы сами будем листы книги заполнять "рогами и копытами" в нужных местах?)
Ну, теперь осталось от Вас ещё и файл-пример получить и "золотой ключик у нас в руках".
(Вы что, предполагаете, что для того, чтобы понять что у Вас не так работает, мы сами будем листы книги заполнять "рогами и копытами" в нужных местах?)
Ну, теперь осталось от Вас ещё и файл-пример получить и "золотой ключик у нас в руках".
(Вы что, предполагаете, что для того, чтобы понять что у Вас не так работает, мы сами будем листы книги заполнять "рогами и копытами" в нужных местах?)
cobra77777, вообще-то, нигде в вашем "коде" не обнаружилось ни наименований предприятий, ни записи тэгов. По одной простой причине - вы кинулись в нас не тем куском данных. и куском кода. и в нём наблюдается, что в процессе сохранения участвует ещё один рабочий лист. Который, между прочим, и сохраняется.
Могу вам сказать одно - откажитесь от использования .SaveAs FileFormat:=xlTextWindows и напишите небольшую собственную процедуру (пример) для сохранения данных (тем более, что у вас, судя по всему, это и предполагается - текст в сохраняемом листе весь вписывается в ячейки первого столбца - из которых вы легко сформируете единую символьную переменную). Впрочем, как замечалось, даже этот метод для работы с xml не очень подходит (но он лучше вашего ).
cobra77777, вообще-то, нигде в вашем "коде" не обнаружилось ни наименований предприятий, ни записи тэгов. По одной простой причине - вы кинулись в нас не тем куском данных. и куском кода. и в нём наблюдается, что в процессе сохранения участвует ещё один рабочий лист. Который, между прочим, и сохраняется.
Могу вам сказать одно - откажитесь от использования .SaveAs FileFormat:=xlTextWindows и напишите небольшую собственную процедуру (пример) для сохранения данных (тем более, что у вас, судя по всему, это и предполагается - текст в сохраняемом листе весь вписывается в ячейки первого столбца - из которых вы легко сформируете единую символьную переменную). Впрочем, как замечалось, даже этот метод для работы с xml не очень подходит (но он лучше вашего ). AndreTM
Могу вам сказать одно - откажитесь от использования .SaveAs FileFormat:=xlTextWindows и напишите небольшую собственную процедуру (пример) для сохранения данных (тем более, что у вас, судя по всему, это и предполагается - текст в сохраняемом листе весь вписывается в ячейки первого столбца - из которых вы легко сформируете единую символьную переменную). Впрочем, как замечалось, даже этот метод для работы с xml не очень подходит (но он лучше вашего ). Автор - AndreTM
Дата добавления - 06.11.2013 в 01:51
Я хочу вставить оператор if в ячейку через vba, который включает двойные кавычки.
Из-за двойных кавычек у меня возникают проблемы со вставкой строки. Как мне обрабатывать двойные кавычки?
Не прямой ответ, но уверены ли вы в этой строке? Это круговая ссылка, которая не начинается со знака равенства.
Я считаю, что самый простой способ - это удвоить кавычки, чтобы обработать цитату.
Некоторым нравится использовать CHR (34) *:
* Примечание: CHAR () используется в качестве формулы ячейки Excel, например, для записи в ячейке «= CHAR (34)», но для кода VBA вы используете функцию CHR ().
Я предпочитаю создавать глобальную переменную: Public Const vbDoubleQuote As String = "" "" 'представляет 1 двойную кавычку (") Public Const vbSingleQuote As String ="' "'представляет 1 одинарную кавычку (') и использует ее так: Shell" explorer.exe "& vbDoubleQuote & sPath & vbDoubleQuote, vbNormalFocus
Ответ @gicalle действительно изящный, он сводит всю путаницу «сколько одинарных и двойных кавычек» к одному определению, где это может быть задокументировано
Я использую множество глобальных определений во всех моих проектах VBA (когда у меня нет выбора в использовании чего-то другого, кроме VBA !!), которые определяют все общие вещи, такие как возврат каретки, двойные кавычки и т. Д.
Другой способ - создать строку с временным замещающим символом. Затем вы можете использовать REPLACE, чтобы заменить каждый временный символ двойными кавычками. Я использую тильду как временный замещающий символ.
Вот пример из проекта, над которым я работал. Это небольшая служебная программа для восстановления очень сложной формулы, если / когда на ячейку случайно наступят. Это сложная формула для ввода в ячейку, но эта небольшая утилита исправляет ее мгновенно.
На самом деле это простой трюк программирования, но он позволяет довольно легко ввести формулу в код VBA.
Читайте также: