Excel 2010 - удаление символов из ячейки, количество символов варьируется

Question

У меня есть список, экспортированный из другого приложения. Результаты возвращаются с дополнительными символами между ними.

Что я получаю:

Mary One;#123;#Bob Two;#2345;#Charles Three;#445

Что я хочу:

Mary One; Bob Two; Charles Three

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

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

Я собираюсь использовать SUBSTITUTE и LEN, но я мог бы использовать некоторую помощь, чтобы сохранить это в чистоте.

Answer 1

Я собираюсь представить здесь несколько вариантов на выбор, но все они основаны на регулярных выражениях. Можно не использовать регулярные выражения, но я думаю, что это может занять немного больше времени.

[Вариант 1 - https://regex101.com/]
Посетите страницу https://regex101.com/
Установить «pcre (php)» - это опция по умолчанию
Регулярное выражение: (?<=;)(#\d+;#)|(;#\d+$) Флаг: g
Тестовая строка: Mary One;#123;#Bob Two;#2345;#Charles Three;#445
Замена: // один пробел (или его можно оставить пустым)
Выход: Mary One; Bob Two; Charles Three

Выход должен быть обрезан, так как в конце есть еще один пробел.

[Вариант 2 - LibreOffice Calc (переносной)]
Окно Calc «Найти и заменить» позволяет использовать регулярные выражения.

Ctrl+H,
Найти: (?<=;)(#\d+;#)|(;#\d+$)
Заменить на: один пробел или пробел
В разделе «Другие варианты» отметьте «Регулярные выражения».
Нажмите кнопку «Заменить все».

[Вариант 3 - Блокнот ++]
Я упоминаю здесь N ++, поскольку это действительно мощный инструмент для работы со многими типами текстов. Его сила в значительной степени заключена в многочисленных плагинах. Но здесь я буду ссылаться на стандартное окно замены. Regex позволяет разбивать совпадающие части текста. Это называется группировка. У каждой группы может быть имя, и это имя можно использовать при замене.

Ctrl +H → Заменить вкладку
Обернуть: проверено
Режим поиска: регулярное выражение
Найти что: (?'name_surname'[^;#]+\s[^;#]+;)(#\d+(;#|$))
Заменить на: $+{name_surname}
Нажмите на кнопку «Заменить все»

Имея в виду:
«Найти что» находит все - желаемый и нежелательный текст.
«Заменить» заменяет необработанный текст только требуемыми частями.

[Вариант 4 - Excel - пользовательская функция (VBA)]
Откройте редактор Visual Basic (Alt+F11)
Добавить ссылку:
Сервис -> Ссылки -> Регулярные выражения Microsoft VBScript 5.5

Option Explicit
Function leaveNames(CellValue As Variant)
    Dim RegEx As RegExp
    Dim Expr As String
    Set RegEx = New RegExp
    Expr = "(#\d+;#)|(;#\d+$)"
    RegEx.Global = True
    RegEx.IgnoreCase = False
    RegEx.MultiLine = False
    RegEx.Pattern = Expr
    leaveNames = RegEx.Replace(CellValue, "")
End Function

[Резюме]
Я попытался сосредоточиться на довольно быстрых, но полностью функциональных решениях, поэтому думаю, что из этого можно извлечь.

Answer 2

Я попробовал формулу. Это было не красиво. Хотя я уверен, что есть и другие способы сделать это.

Эта формула возьмет вашу строку в ячейку A1 и удалит строки #123 и #2345 . Вы всегда можете расширить технику дальше, если хотите. Он находит вхождения «#», а затем разбивает строку на более мелкие части.

=LEFT(A1,SEARCH("#",A1,1)-1) &  MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,SEARCH("#",MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,LEN((A1))),1)-1)

Это не чисто и не легко общаться.

Я предлагаю пользовательскую функцию. Вот статья, объясняющая, как установить и распространить пользовательскую функцию. Поинтересуйтесь UDF, и если у вас есть вопросы, касающиеся процесса, пожалуйста, спросите.