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


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

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

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

Что я хочу:

Mary One; Bob Two; Charles Three

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

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

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


0
2018-01-04 19:07


Источник


Должна быть формулой? Создание пользовательской функции с использованием RegEx было бы простым способом решения этой проблемы. Хотя распространение UDF может создать трудности. - B540Glenn
Вам нужен ответ в одной ячейке или через несколько ячеек? (То есть A1 = длинная строка, B1 = «Mary One», C1 = «Bob two» и т. Д. - bvaughn


Ответы:


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

[Опция 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 (переносной)]
Окно «Найти и заменить» позволяет использовать регулярные выражения.

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


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


1
2018-01-04 22:58



Вариант 4 оказался именно тем, что нужно моим пользователям. Большое спасибо! - DeNaeL
К сожалению, я просто запустил его в новом отчете, и он разбивается, если есть более двух имен. В ячейке с третьим именем я получил: Tom Kent, Julie Y Busse, # 562; DeNae Leverentz - DeNaeL
Я думаю, что ключевое различие между строкой от вопроса и комментарием заключается в том, что в последнем не все имена начинаются с хэша. Я изменил регулярное выражение, добавив вопросительный знак, который делает хэш необязательным: Expr = "(#\d+;#?)|(;#\d+$)", Новое выражение все еще работает с предыдущими строками. Если вы обнаружили еще несколько отрицательных сценариев, то, скорее всего, основная причина будет сидеть в regex, но, к счастью, это легко изменить. - wlod
Спасибо! Я не нашел никаких других негативных сценариев! - DeNaeL


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

Эта формула примет вашу строку в ячейке 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 идти, и если у вас есть вопросы относительно процесса, спросите пожалуйста.


0
2018-01-04 21:42