Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Макрос замены текста внутри ячейки, если в ней встречается N - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос замены текста внутри ячейки, если в ней встречается N (Макросы/Sub)
Макрос замены текста внутри ячейки, если в ней встречается N
Yar4i4 Дата: Суббота, 12.03.2016, 14:25 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Добрый вечер!
В ячейке встречается различный текст в разном порядке. Необходимо внутри ячейки выстроить текст в виде "АБВГд 01-23-456-78"
Где АБВГд - это точно известные сочетания букв идущие без пробелов внутри себя (иногда "д" может идти после пробела, очень иногда).
АБВГд = ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ.
Следом должен идти пробел.
И завершает содержимое новообразованной ячейки четыре числа разделённых между собой тире "-". Тут полный бардак и иногда эти числа идут после буквы "Е", но тогда четыре цифры подряд - их нужно игнорировать. Но тут есть зависимость. Например: "Е2301-001-0 1 23-01-001- 01 ГЭСН" как мы видим, если в розовые цифры добавить тире после второй цифры, то они будут идентичны красным цифрам.
Иногда цифры имеют вид 1-2-3-4 и это допустимо, т.е. "ГЭСН 1-2-3-4" - тоже годится, и можно не выстраивать из них вид "12-34-567-89".
Много написал... Основные примеры встречающиеся на практике я привёл в прикреплённом файле. Если нужно, то могу ячейки, которые необходимо править расположить лишь в одном столбце.
К сообщению приложен файл: 2291046.xls(50Kb)


Сообщение отредактировал Yar4i4 - Суббота, 12.03.2016, 14:32
 
Ответить
СообщениеДобрый вечер!
В ячейке встречается различный текст в разном порядке. Необходимо внутри ячейки выстроить текст в виде "АБВГд 01-23-456-78"
Где АБВГд - это точно известные сочетания букв идущие без пробелов внутри себя (иногда "д" может идти после пробела, очень иногда).
АБВГд = ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ.
Следом должен идти пробел.
И завершает содержимое новообразованной ячейки четыре числа разделённых между собой тире "-". Тут полный бардак и иногда эти числа идут после буквы "Е", но тогда четыре цифры подряд - их нужно игнорировать. Но тут есть зависимость. Например: "Е2301-001-0 1 23-01-001- 01 ГЭСН" как мы видим, если в розовые цифры добавить тире после второй цифры, то они будут идентичны красным цифрам.
Иногда цифры имеют вид 1-2-3-4 и это допустимо, т.е. "ГЭСН 1-2-3-4" - тоже годится, и можно не выстраивать из них вид "12-34-567-89".
Много написал... Основные примеры встречающиеся на практике я привёл в прикреплённом файле. Если нужно, то могу ячейки, которые необходимо править расположить лишь в одном столбце.

Автор - Yar4i4
Дата добавления - 12.03.2016 в 14:25
AlexMen Дата: Суббота, 12.03.2016, 14:58 | Сообщение № 2
Группа: Заблокированные
Ранг: Участник
Сообщений: 66
Репутация: 4 ±
Замечаний: 100% ±

Excel 2010
ну с буквами понятно, поиск на совпадение (Найти/поиск) и после вставить спереди, по поводу цифр то можете маску использовать тип ##-##-###-##


lebensvoll отпишите на почту, а то меня _Boroda_ забанил, я Вам функцию сброшу
 
Ответить
Сообщениену с буквами понятно, поиск на совпадение (Найти/поиск) и после вставить спереди, по поводу цифр то можете маску использовать тип ##-##-###-##

Автор - AlexMen
Дата добавления - 12.03.2016 в 14:58
МВТ Дата: Суббота, 12.03.2016, 16:07 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 475
Репутация: 135 ±
Замечаний: 0% ±

Excel 2007
ну с буквами понятно, поиск на совпадение (Найти/поиск) и после вставить спереди, по поводу цифр то можете маску использовать тип ##-##-###-##

Да не надо ничего мудрить, все просто. Большая часть решается при помощи обычной регулярки. Остальное устраняется вместе с хаосом, который представляют способы введения данных.
[vba]
Код

Function tt(Text As String)
    Dim obj As Object
    Text = WorksheetFunction.Trim(Text)
    With CreateObject("VBScript.Regexp")
        .Ignorecase = False
        .MultiLine = False
        .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)"
        Set obj = .Execute(Text)
        If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0)
    End With
    tt = Text
End Function
[/vba]
Yar4i4, для большинства вариантов, которые есть в Вашем примере работает. Хотя в одном Вы правы: данные - полный бардак ;)
 
Ответить
Сообщение
ну с буквами понятно, поиск на совпадение (Найти/поиск) и после вставить спереди, по поводу цифр то можете маску использовать тип ##-##-###-##

Да не надо ничего мудрить, все просто. Большая часть решается при помощи обычной регулярки. Остальное устраняется вместе с хаосом, который представляют способы введения данных.
[vba]
Код

Function tt(Text As String)
    Dim obj As Object
    Text = WorksheetFunction.Trim(Text)
    With CreateObject("VBScript.Regexp")
        .Ignorecase = False
        .MultiLine = False
        .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)"
        Set obj = .Execute(Text)
        If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0)
    End With
    tt = Text
End Function
[/vba]
Yar4i4, для большинства вариантов, которые есть в Вашем примере работает. Хотя в одном Вы правы: данные - полный бардак ;)

Автор - МВТ
Дата добавления - 12.03.2016 в 16:07
Yar4i4 Дата: Понедельник, 14.03.2016, 14:42 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
все просто

не могу вставить в макрос - впервые с функцией в виде кода столкнулся.
А можно упростить задачу: если константы (ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ) встречаются в ячейке, то просто перенести их в начало?
Потом мне останется лишь удалить лишние цифры.
 
Ответить
Сообщение
все просто

не могу вставить в макрос - впервые с функцией в виде кода столкнулся.
А можно упростить задачу: если константы (ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ) встречаются в ячейке, то просто перенести их в начало?
Потом мне останется лишь удалить лишние цифры.

Автор - Yar4i4
Дата добавления - 14.03.2016 в 14:42
Yar4i4 Дата: Четверг, 17.03.2016, 15:58 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
создал модуль 32, вызываю функцию, но далее незнаю что да как. (не моё это, я пока на уровне =СУММ)
А можно через макрос? Просто данная задача является завершающим звеном в одном огромном теле макроса. И я точно не смогу внутри тела переход на функцию сделать.
К сообщению приложен файл: 8129357.jpg(29Kb)
 
Ответить
Сообщениесоздал модуль 32, вызываю функцию, но далее незнаю что да как. (не моё это, я пока на уровне =СУММ)
А можно через макрос? Просто данная задача является завершающим звеном в одном огромном теле макроса. И я точно не смогу внутри тела переход на функцию сделать.

Автор - Yar4i4
Дата добавления - 17.03.2016 в 15:58
Manyasha Дата: Четверг, 17.03.2016, 16:14 | Сообщение № 6
Группа: Модераторы
Ранг: Старожил
Сообщений: 1583
Репутация: 659 ±
Замечаний: 0% ±

Excel 2007, 2010
Yar4i4, udf-ки используются на листе также, как и обычные функции. В ячейку в который нужен результат, пишите
Код
=tt(B1)
и протягиваете вниз.

Если нужно из другого макроса вызвать, то так:
[vba]
Код
sub Ваш_макрос()
s = tt("какой-то текст")
End sub
[/vba]
создал модуль 32
в одном модуле может содержаться несколько макросов/udf. Не нужно так плодить модули.


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеYar4i4, udf-ки используются на листе также, как и обычные функции. В ячейку в который нужен результат, пишите
Код
=tt(B1)
и протягиваете вниз.

Если нужно из другого макроса вызвать, то так:
[vba]
Код
sub Ваш_макрос()
s = tt("какой-то текст")
End sub
[/vba]
создал модуль 32
в одном модуле может содержаться несколько макросов/udf. Не нужно так плодить модули.

Автор - Manyasha
Дата добавления - 17.03.2016 в 16:14
Yar4i4 Дата: Четверг, 17.03.2016, 16:36 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
В ячейку в который нужен результат

В исходную ячейку результат и нужен.
Например: если в ячейке B1 (прикреплённого файла) содержится "Е1303-1-2 13-03-001-02 ГЭСН", то мне нужно, что бы в ячейке B1 стало "ГЭСН 13-03-001-02".

Не нужно так плодить модули.

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

В исходную ячейку результат и нужен.
Например: если в ячейке B1 (прикреплённого файла) содержится "Е1303-1-2 13-03-001-02 ГЭСН", то мне нужно, что бы в ячейке B1 стало "ГЭСН 13-03-001-02".

Не нужно так плодить модули.

Пустые удалю. Спасибо.

Автор - Yar4i4
Дата добавления - 17.03.2016 в 16:36
Manyasha Дата: Четверг, 17.03.2016, 16:58 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 1583
Репутация: 659 ±
Замечаний: 0% ±

Excel 2007, 2010
Yar4i4, ну можно так:
[vba]
Код
Sub macro()
    'Если оставите эту строчку, будет работать на выделенном диапазоне
    'For Each cell In Selection

    'для всего столбца B
    For Each cell In Range("b1:b" & Cells(Rows.Count, "b").End(xlUp).Row)
        cell.Value = tt(cell.Value)'вызов UDF от МВТ
    Next cell
End Sub
[/vba]


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеYar4i4, ну можно так:
[vba]
Код
Sub macro()
    'Если оставите эту строчку, будет работать на выделенном диапазоне
    'For Each cell In Selection

    'для всего столбца B
    For Each cell In Range("b1:b" & Cells(Rows.Count, "b").End(xlUp).Row)
        cell.Value = tt(cell.Value)'вызов UDF от МВТ
    Next cell
End Sub
[/vba]

Автор - Manyasha
Дата добавления - 17.03.2016 в 16:58
Yar4i4 Дата: Пятница, 18.03.2016, 06:26 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013

Спасибо. По столбцу "B" оставил.
 
Ответить
Сообщение
Спасибо. По столбцу "B" оставил.

Автор - Yar4i4
Дата добавления - 18.03.2016 в 06:26
Yar4i4 Дата: Пятница, 18.03.2016, 08:02 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
утром проверил макрос в боевых условиях. Цепляется за первую букву после "ГЭСН" и ставит её в ячейку.
Например:
Из "Е2203-14-3 22-03-014- 03 ГЭСН изм. вып.2" делает "ГЭСН и 22-03-014- 03"
ИЗ "Е2505-27-2 25-05-027- 02 ГЭСНмр тех.ч,р.3, примеч.п.2" делает "ГЭСНмр т 25-05-027- 02"
В общем если за "ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ" идёт любая буква, то макрос/функция её вставляет в итоговую ячейку.
 
Ответить
Сообщениеутром проверил макрос в боевых условиях. Цепляется за первую букву после "ГЭСН" и ставит её в ячейку.
Например:
Из "Е2203-14-3 22-03-014- 03 ГЭСН изм. вып.2" делает "ГЭСН и 22-03-014- 03"
ИЗ "Е2505-27-2 25-05-027- 02 ГЭСНмр тех.ч,р.3, примеч.п.2" делает "ГЭСНмр т 25-05-027- 02"
В общем если за "ГЭСН, ГЭСНм, ГЭСНмр, ГЭСНп, ГЭСНр, ФЕР, ФЕРм, ФЕРмр, ФЕРп, ФЕРр, ФСЭМ, ФССЦ" идёт любая буква, то макрос/функция её вставляет в итоговую ячейку.

Автор - Yar4i4
Дата добавления - 18.03.2016 в 08:02
Yar4i4 Дата: Пятница, 25.03.2016, 06:19 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Не срабатывает на ячейках вида "ФЕР09-05-006-01
Пр. Минрегион от 17.11.08 № 253". Файл приложил, но он не обязателен.
Очень, очень прошу. Я не понимаю как он работает. Скорее всего цепляется за большие буквы...
 
Ответить
СообщениеНе срабатывает на ячейках вида "ФЕР09-05-006-01
Пр. Минрегион от 17.11.08 № 253". Файл приложил, но он не обязателен.
Очень, очень прошу. Я не понимаю как он работает. Скорее всего цепляется за большие буквы...

Автор - Yar4i4
Дата добавления - 25.03.2016 в 06:19
Wasilich Дата: Пятница, 25.03.2016, 09:32 | Сообщение № 12
Группа: Друзья
Ранг: Ветеран
Сообщений: 855
Репутация: 220 ±
Замечаний: 0% ±

2003
А можно через макрос? ... не смогу внутри тела переход на функцию сделать.
:)
[vba]
Код
Sub макрос()
  Dim obj As Object, Text As String
  For K = 2 To 3
    For I = 1 To 23
      Text = WorksheetFunction.Trim(Cells(I, K))
      With CreateObject("VBScript.Regexp")
        .Ignorecase = False
        .MultiLine = False
        .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)"
        Set obj = .Execute(Text)
        If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0)
      End With
      Sheets("итог").Cells(I, K) = Text
    Next
  Next
End Sub
[/vba]Может и правда, проще будет.
К сообщению приложен файл: Yar4i4.xls(58Kb)
 
Ответить
Сообщение
А можно через макрос? ... не смогу внутри тела переход на функцию сделать.
:)
[vba]
Код
Sub макрос()
  Dim obj As Object, Text As String
  For K = 2 To 3
    For I = 1 To 23
      Text = WorksheetFunction.Trim(Cells(I, K))
      With CreateObject("VBScript.Regexp")
        .Ignorecase = False
        .MultiLine = False
        .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)"
        Set obj = .Execute(Text)
        If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0)
      End With
      Sheets("итог").Cells(I, K) = Text
    Next
  Next
End Sub
[/vba]Может и правда, проще будет.

Автор - Wasilich
Дата добавления - 25.03.2016 в 09:32
Yar4i4 Дата: Пятница, 25.03.2016, 12:40 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
проще

Спасибо. Работает.
Но цепляется ко всем большим буквам и думает, что они и есть ГЭСН, ФЕМ, ФЕРм...
В частности на "ГЭСН 09-06-024-05 К=0,7" вместо ГЭСН берёт К.
Можно как-нибудь (не знаю как) использовать условие:
IF в ячейке есть ГЭСН ... OR ГЭСНм OR
THEN ГЭСНм 12-34-567-89

Извините за назойливость.
 
Ответить
Сообщение
проще

Спасибо. Работает.
Но цепляется ко всем большим буквам и думает, что они и есть ГЭСН, ФЕМ, ФЕРм...
В частности на "ГЭСН 09-06-024-05 К=0,7" вместо ГЭСН берёт К.
Можно как-нибудь (не знаю как) использовать условие:
IF в ячейке есть ГЭСН ... OR ГЭСНм OR
THEN ГЭСНм 12-34-567-89

Извините за назойливость.

Автор - Yar4i4
Дата добавления - 25.03.2016 в 12:40
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос замены текста внутри ячейки, если в ней встречается N (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!