Excel замена символа в строке
В excel замена символа в строке
ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)
Смотрите также какой-то символ на 0 3 0 findstr = «MSВ результате действия функции примера удаляет возвраты=CLEAN(B2)
Описание
столбца (C2) введите текста в обрабатываемой(Find what) и Если же файл
теперь для обозначения тот, который Вам в Excel 2013,абвгдеёжзий
старом тексте, начиная или двухбайтовый) за
В этой статье описаны Перенос строки. Тут0 1 5
» ‘подстрока дляReplace каретки из всехСкопируйте формулу во все формулу для удаления/замены ячейке. Например, можно нажмите.csv разрыва строки используют
больше подходит: 2010, 2007 и2009 с которого требуется один вне зависимости синтаксис формулы и
решение довольно изящное: 0 0 0 замены newstr =возвращается исходная строка ячеек на активном ячейки столбца. переносов строк. Ниже удалить возвраты каретки,Ctrl+Jполучен от поклонника два различных непечатаемыхУдаляем все переносы строк
2003.123456 выполнить замену новым от языка по использование функций
Синтаксис
т.е. третий символ
«» ‘ новая с замененным строковым
листе.По желанию, можете заменить приведены несколько полезных а затем найти
. На первый взгляд Linux, Unix или символа: вручную, чтобы быстроПереносы строк могут появитьсяФормула
текстом. умолчанию.ЗАМЕНИТЬ «Заменить на:» ввести стал «1»
подстрока для замены фрагментомSub RemoveCarriageReturns() Dim исходный столбец новым,
формул для различных лишние начальные или поле покажется пустым, другой подобной системы,
Пример
Возврат каретки навести порядок на в тексте поОписание (результат)Число_знаковФункция ЗАМЕНИТЬБ считает каждыйи 010 с нажатымabcd84 retval = Replace(sample,Параметры MyRange As Range с удалёнными переносами
конечные пробелы, или
но если посмотрите
то готовьтесь к
(Carriage return, CR
разным причинам. Обычно
Обязательный. Число символов в двухбайтовый символ заЗАМЕНИТЬБ ALTом. При этом
: Вариант формулой в
Expression Application.ScreenUpdating = False строк:
Эта формула подходит для
лишние пробелы между
внимательно, то увидите встрече только с или ASCII код
Удаляем переносы строк при
Как удалить переносы строк (возвраты каретки) из ячеек в Excel 2013, 2010 и 2007
возвраты каретки встречаются=ЗАМЕНИТЬ(A2;6;5;»*») старом тексте, которые два, если включенав Microsoft Excel. в самом поле другой столбец Код ‘ меняем одинОбязательный аргумент — строка, Application.Calculation = xlCalculationManualВыделите все ячейки в использования с комбинациями
словами. в нём маленькую символом перевода строки. 13) и помощи формул и в рабочей книге,Заменяет пять знаков последовательности требуется ЗАМЕНИТЬ новым поддержка ввода наФункция ЗАМЕНИТЬ заменяет указанное ничего не появится, =ПОДСТАВИТЬ(C1;»0″;»1″;2) раз, начиная с в которой требуется For Each MyRange
столбце возврат каретки /В некоторых случаях переносы точку.Плюсы:Перевод строки настраиваем таким образом например, когда текст абвгдеёжзий одним знаком текстом. языке с двухбайтовой
число символов текстовой но после нажатияВот так наверное первого символа Debug.Print
- замена In ActiveSheet.UsedRange IfC перенос строки, характерными
- строк необходимо удалять,В полеЭтот способ самый(Line feed, LF комплексную обработку текстовых
- скопирован с веб-страницы, *, начиная сЧисло_байтов кодировкой, а затем
строки другой текстовой на «Заменить» - вернее)) Код =ЗАМЕНИТЬ(A1;3;1;»1″) retvalFind 0 (упрощенное письмо), китайский
доступны не на
точка курсора, находящегося Проблема в том,0 0 0Count VBA, рекомендую изучитьВставить пробел). В таком(LOOKUP). просто удалить переносы которых требуется удалить
импорте из файлов
работе на печатных
причина их появления, последовательности 123456 одним вставьте их в (традиционное письмо) и
всех языках.
на «строке ниже».
- Минусы: строк, оставьте поле возвраты каретки или.txt машинках и обозначали сейчас перед нами знаком @.
- ячейку A1 нового корейский.Функция ЗАМЕНИТЬ предназначена дляКстати, Alt+Enter можно заходит об Excel,0 0 5 число
- как вставить и
Удаляем переносы строк при помощи макроса VBA
Удалите вспомогательный столбец. будут объединены иПотребуется создать вспомогательныйЗаменить на заменить их другим
или две различных операции. стоит задача удалить
@456 листа Excel. ЧтобыЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст) языков с однобайтовой вводить в текстовую
все инстинктивно начинают 0 0 0Compare выполнить код VBAПлюсы: лишние пробелы не столбец и выполнить(Replace With) пустым. символом..csv Любознательный читатель может возвраты каретки, такЭта инструкция познакомит Вас
отобразить результаты формул,ЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст) кодировкой, а ЗАМЕНИТЬБ константу в формуле. советовать формулы. Во. Необязательный аргумент — вид
в Excel.Создаём один раз
появятся.
множество дополнительных шагов.
Replace
Функция Replace
данные обычно содержат
самостоятельно найти подробную как они мешают с тремя способами выделите их иАргументы функций ЗАМЕНИТЬ и
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.
Её синтаксис таков:
=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )
- Ячейка — ячейка с текстом, где производится замена
- Старый_текст — текст, который надо найти и заменить
- Новый_текст — текст, на который заменяем
- Номер_вхождения — необязательный аргумент, задающий номер вхождения старого текста на замену
Обратите внимание, что:
- Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
- Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
- Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)
Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.
Замена или удаление неразрывных пробелов
При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:
Подсчет количества слов в ячейке
Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:
Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.
Извлечение первых двух слов
Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:
У нее простая логика:
- заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
- ищем позицию символа # функцией НАЙТИ (FIND)
- вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)
Функции VBA для работы с текстом
- ASC ()— эта функция позволяет вернуть числовой код для переданного символа. Например, ASC( «D» ) вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду.Варианты этой функции — AscB() и AscW():
- AscB () — возвращает только первый байт числового кода для символа.
- AscW () — возвращает код для символа в кодировке Unicode
- Chr () — возвращает символ по его числовому коду. Может использоваться в паре с функцией Asc(), но чаще всего её применяют, когда нужно вывести служебный символ (например кавычки — » ), т.к. кавычки просто так в VBA-коде не ввести(нужно ставить двойные). Я обычно именно эту функцию и использую.
Dim sWord As String sWord = Chr(34) & «Слово в кавычках» & Chr(34)
Есть варианты этой функции — ChrB() и ChrW(). Работают аналогично таким же вариантам для функции Asc().
InStr () и InStrRev ()— одна из самых популярных функций. Позволяет обнаружить в теле строковой переменной символ или последовательность символов и вернуть их позицию. Если последовательность не обнаружена, то возвращается 0.Dim sStr As String sStr = «w» If InStr(1, «Hello, World!», sStr, vbTextCompare) > 0 Then MsgBox «Искомое слово присутствует!» Else MsgBox «Искомое слово отсутствует!» End If
Разница функций в том, что InStr() ищет указанное слово от начала строки, а InStrRev() с конца строки
Left (), Right (), Mid ()— возможность взять указанное вами количество символов из существующей строковой переменной слева, справа или из середины соответственно.Dim sStr As String sStr = «Hello, World!» MsgBox Mid(sStr, 1, 5)
Dim sStr As String sStr = «Hello, World!» MsgBox Replace(sStr, «Hello», «Bay»)
Dim sStr As String sStr = «Hello, World!» MsgBox StrConv(«Hello, World!», vbUpperCase)
В качестве второго параметра параметра могут применяться константы:
- vbUpperCase: Преобразует все текстовые символы в ВЕРХНИЙ РЕГИСТР
- vbLowerCase: Преобразует все текстовые символы в нижний регистр
- vbProperCase: Переводит первый символ каждого слова в Верхний Регистр
- *vbWide: Преобразует символы строки из однобайтовых в двухбайтовые
- *vbNarrow: Преобразует символы строки из двухбайтовых в однобайтовые
- **vbKatakana: Преобразует символы Hiragana в символы Katakana
- **vbHiragana: Преобразует символы Katakana в символы Hiragana
- ***vbUnicode: Преобразует строку в Юникод с помощью кодовой страницы системы по умолчанию
- ***vbFromUnicode: Преобразует строку из Юникод в кодовую страницу системы по умолчанию
* применимо для локализацией Дальнего востока
** применимо только для Японии
*** не поддерживается операционными системами под управлением Macintosh
Excel замена символа в строке
Други — срочно!
кто знает как в множестве ячеек листа избавиться от ненужного символа переноса строки?
чертов квадратик не дает вычислять и корректно конвертировать содержимое.
имею ввиду символ нажатия «enter» , который приводит к переносу вводимого текста на строку ниже.
Добавление от 12.12.2004 01:23:
замена сработала с кодом 010 — НО! после этого функция замены вырубается
как пофиксить?
и еще — нужно в пустые ячейки автоматом вставлять некое значение — возможно ли? — путем автозамены? или только вручную? — большой гимор.
спасибо за ответы
после конвенртирования данных из 1с в Excel имеем:
часть данных конвертируется некорректно, имея в ячейках спецсимвол, с этим почти разобрались — см. выше.
некоторые массивы имеют хаотично разбросанные пустые поля, которые надо заполнить одинаковым значением, в моем случае — нулями, иначе не работает формулы вычисления для соседних ячеек, итоговая автосумма по столбцу и т.п.
вопрос — можно ли в ПУСТЫЕ ячейки забить нули автозаменой? похоже нет. а лопатить огромные листы на тысячи позиций и вручную заполнять пустые позиции — это тупо и долго.
Наверно мы с Вами говорим о разных вещах.
Настоящая автозамена, это когда мы вводим в ячейку, например z, а получаем 0 (Сервис-Автозамена)
Естественно, что для этого нам необходимо предварительно указать, что z — это неправильный вариант, а 0 — правильный.
Вы, по всей видимости, имеете ввиду меню Правка — команда Заменить
Но это не важно, так как в принципе нам ничего не мешает выделить диапазон ячеек, содержащий пустые ячейки, а затем ввести 0 сразу во все эти ячейки.
— Выделяете диапазон ячеек (можно выделить несмежные и не обязательно выделять строго пустые ячейки), затем в меню Правка-Перейти-Выделить-Пустые ячейки-ОК
— Вводим 0, затем CTRL + ENTER *
SS-18
Чтобы определить код символа можно воспользоваться функцией КОДСИМВ.
Допустим, символ переноса строки находится на пятой позиции в ячейке G30, тогда формула:
=КОДСИМВ(ПСТР(G30;5;1))
возвратит значение 10
Удалить этот символ из ячейки можно с помощью формулы:
=ПОДСТАВИТЬ(G30;СИМВОЛ(10);»»)
В некоторых случаях может понадобиться заменить этот символ другим, например, пробелом:
=ПОДСТАВИТЬ(G30;СИМВОЛ(10);СИМВОЛ(32))
Другой способ удалить символ с кодом 10 — использовать функцию ПЕЧСИМВ:
=ПЕЧСИМВ(G30)
SS-18
замена сработала с кодом 010 — НО! после этого функция замены вырубается
Когда вызываешь Замену во второй раз, в полях ввода ОСТАЮТСЯ предыдущие значения!
То есть там стоИт сомвол 010, но его не видно, ты дописываешь что-то. и такая комбинация символов, действительно, не встречается.
Метод: после вызова Замены поставить курсор в поле «Что» и несколько раз нажать BackSpace и Delete.
Забить пустые ячейки нулями? Нет проблем!
Выдели ячейку где-то внутри таблицы, можно в первой строке, где заголовки.
Правка-Перейти-Выделить-Текущую область (выделил всю таблицу).
Ctrl-H — Что:пустая строка, Заменить на:0 — Заменить все.
Поторопился я обрадоваться
— не помогает стирание спецсимвола и не работает функция замены после его удаления. Стирание поля в графе «заменить» — не приводит к ее активизации.
— не работают оба способа замены массивы пустых ячеек на ноль — в способе Nosorog
выделяется ТОЛЬКО вся таблица ( включая заголовки и графы с названием) и нули вставляются ТОЛЬКО вокруг ячеек массива.
в способе Vitaly K.
вообще не активна функция ОПЕРАЦИИ в специальной вставке — ее просто НЕТ ТАМ.
Попробуй задействовать такой макрос:
код: Он, собственно, применяет функцию ПЕЧСИМВ к каждой ячейке таблицы, и пишет 0 в каждую пустую ячейку.
2pashulka
хммм.. уточняю — у меня дома стоит excel 2000-й. попытка использовать режим «правка-спецвставка» показывает окошко в котором и в помине нет группы ОПЕРАЦИЯ, а есть только выбор вида текста.. ну там Unicode или другой. и больше никаких ..
-отсюда вопрос — как в Excel 2000 получить указанный режим функции спец вставка?
Vic_K
Спасибо за вопрос, но я не знаю как формируется файл из 1С.. знаю, что у жены на работе что-то сбито в ее настройках, после того как там покопался вызванный программист по 1С. а вызывать их на каждый сбой ох как дорого, да еще с такими результатами. все вышеописанные глюки появились именно после визита программиста. т.е. справеливее сказать, что он настраивал именно то, что просили добавить, но потом обнаруживались ужасные последствия в других местах.
Политика у них такая что-ли, чтобы делать людей зависимыми от их «услуг» и на бабки разводить?
извините, ответил не квалифицировано, как смог.
30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)
Вчера в марафоне 30 функций Excel за 30 дней мы использовали функцию INDEX (ИНДЕКС), чтобы вернуть значение ячейки, расположенной на пересечении заданных строки и столбца.
25-й день марафона мы посвятим изучению функции REPLACE (ЗАМЕНИТЬ), которая относится к категории текстовых. Она заменяет определённое количество символов текстовой строки другим текстом.
Итак, давайте внимательно посмотрим информацию и примеры по функции REPLACE (ЗАМЕНИТЬ), а если у Вас есть дополнительные сведения или примеры, прошу делиться ими в комментариях.
Функция 25: REPLACE (ЗАМЕНИТЬ)
Функция REPLACE (ЗАМЕНИТЬ) заменяет символы внутри текста, основываясь на заданном количестве символов и начальной позиции.
Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?
Функция REPLACE (ЗАМЕНИТЬ) может заменять символы в текстовой строке, например:
- Изменить код города в телефонном номере.
- Заменить пробел на двоеточие с пробелом.
- Использовать вложенную функцию REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов.
Синтаксис REPLACE (ЗАМЕНИТЬ)
Функция REPLACE (ЗАМЕНИТЬ) имеет вот такой синтаксис:
REPLACE(old_text,start_num,num_chars,new_text)
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
- old_text (старый_текст) – текстовая строка, в которой будут заменены символы.
- start_num (нач_поз) – начальная позиция старых символов.
- num_chars (число_знаков) – количество старых символов.
- new_text (новый_текст) – текст, который будет вставлен вместо старых символов.
Ловушки REPLACE (ЗАМЕНИТЬ)
Функция REPLACE (ЗАМЕНИТЬ) заменяет определённое количество символов, начиная с указанной позиции. Чтобы заменить определённую строку текста в любом месте текста оригинала, Вы можете использовать функцию SUBSTITUTE (ПОДСТАВИТЬ), которую мы рассмотрим позже в рамках нашего марафона.
Пример 1: Меняем код города в телефонном номере
При помощи функции REPLACE (ЗАМЕНИТЬ) Вы можете изменить первые три цифры в телефонном номере, например, когда задан новый код города. В нашем случае новый код города введён в столбце C, а откорректированные телефонные номера отображаются в столбце D.
Пример 2: Заменяем пробел на двоеточие с пробелом
Чтобы определить начальную позицию для функции REPLACE (ЗАМЕНИТЬ), Вы можете использовать функцию FIND (НАЙТИ), чтобы обнаружить определённую строку текста или символ. В этом примере мы хотим заменить первый пробел, встречающийся в текстовой строке, на двоеточие с пробелом.
=REPLACE(B3,FIND(» «,B3,1),1,»: «)
=ЗАМЕНИТЬ(B3;НАЙТИ(» «;B3;1);1;»: «)
Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов
Функцию REPLACE (ЗАМЕНИТЬ) разрешается вкладывать одну в другую, таким образом в исходном тексте можно делать множественные замены. В этом примере телефонные номера должны содержать дефисы после первой тройки символов и после второй. Используя ноль, как количество символов для замены, мы получим такой результат, что ни один из символов телефонного номера не будет удалён, лишь добавятся 2 дефиса.