Semenalidery.com

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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 If​C​ перенос строки, характерными​
  • ​ строк необходимо удалять,​В поле​Этот способ самый​(Line feed, LF​ комплексную обработку текстовых​
  • ​ скопирован с веб-страницы,​ *, начиная с​Число_байтов​ кодировкой, а затем​

​ строки другой текстовой​​ на «Заменить» -​ вернее)) Код =ЗАМЕНИТЬ(A1;3;1;»1″)​ retval​Find​ 0 ​ (упрощенное письмо), китайский​
​ доступны не на​

​ точка курсора, находящегося​ Проблема в том,​0 0 0​Count​ VBA, рекомендую изучить​Вставить​ пробел). В таком​(LOOKUP).​ просто удалить переносы​ которых требуется удалить​

​ импорте из файлов​
​ работе на печатных​

​ причина их появления,​ последовательности 123456 одним​ вставьте их в​ (традиционное письмо) и​

​ всех языках.​
​ на «строке ниже».​

  • ​ что когда речь​ 0 3 0​
  • ​Необязательный аргумент — указывает​ статью о том,​(Insert).​ случае строки не​
    • ​Минусы:​ строк, оставьте поле​​ возвраты каретки или​​.txt​​ машинках и обозначали​​ сейчас перед нами​ знаком @.​
    • ​ ячейку A1 нового​​ корейский.​​Функция ЗАМЕНИТЬ предназначена для​​Кстати, Alt+Enter можно​​ заходит об Excel,​​0 0 5​​ число​
    • ​ как вставить и​
  • Удаляем переносы строк при помощи макроса VBA

    ​Удалите вспомогательный столбец.​​ будут объединены и​Потребуется создать вспомогательный​Заменить на​ заменить их другим​

    ​или​​ две различных операции.​ стоит задача удалить​

    ​@456​ листа Excel. Чтобы​ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)​ языков с однобайтовой​ вводить в текстовую​

    ​ все инстинктивно начинают​ 0 0 0​Compare​ выполнить код VBA​Плюсы:​ лишние пробелы не​ столбец и выполнить​(Replace With) пустым.​ символом.​.csv​ Любознательный читатель может​ возвраты каретки, так​Эта инструкция познакомит Вас​

    ​ отобразить результаты формул,​ЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст)​ кодировкой, а ЗАМЕНИТЬБ​ константу в формуле.​ советовать формулы. Во​. ​Необязательный аргумент — вид​

    Читать еще:  Замена первого символа в excel

    ​ в Excel.​Создаём один раз​
    ​ появятся.​
    ​ множество дополнительных шагов.​

    Replace

    Функция Replace

    ​данные обычно содержат​

    ​ самостоятельно найти подробную​​ как они мешают​​ с тремя способами​ выделите их и​Аргументы функций ЗАМЕНИТЬ и​

    Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

    Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

    Её синтаксис таков:

    =ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

    • Ячейка — ячейка с текстом, где производится замена
    • Старый_текст — текст, который надо найти и заменить
    • Новый_текст — текст, на который заменяем
    • Номер_вхождения — необязательный аргумент, задающий номер вхождения старого текста на замену

    Обратите внимание, что:

    • Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
    • Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
    • Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

    Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

    Замена или удаление неразрывных пробелов

    При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

    Подсчет количества слов в ячейке

    Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

    Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.

    Извлечение первых двух слов

    Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

    У нее простая логика:

    1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
    2. ищем позицию символа # функцией НАЙТИ (FIND)
    3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (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

  • StrReverse () — «перевернуть» строку, разместив ее символы в обратном порядке. Функция работает только начиная от Excel 2000 и выше. Пример использования функции, а так же иные методы переворачивания слова можно посмотреть в этой статье: Как перевернуть слово?
  • Tab () — еще одна функция, которая используется для форматирования вывода на консоль. Размножает символы табуляции в том количестве, в котором вы укажете. Если никакое количество не указано, просто вставляет символ табуляции. Для вставки символа табуляции в строковое значение можно также использовать константу vbTab.
  • String () — позволяет получить строку из указанного количества символов (которые опять-таки указываются Вами). Обычно используются для форматирования вывода совместно с функцией Len().
  • 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 дефиса.

    Ссылка на основную публикацию
    Adblock
    detector