☰

Удалить текст из ячейки

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Удалить текст из ячейки

У меня есть выписка из AD с более чем 54000 имен, которые, как правило, представлены в следующем формате (пример данных):

Barnes, Bob B [WST]  
Santiago, Francis AB [AT]  
Chandler, Ronald  
Jefferson,  Katherine T [NT]  
Forrest,  Stan S  [WST]  
El Guerrero,  Annette BMD [AT]  
Lopez,  Todd TA  [CNT]  
Jackson, Lincoln F [NT]  
Nunez, Mattie Joey LC [WST]  
Moran,  David DA [AT]  
Gowan, Darryl V  
Hansen,  Rick AF  [NT]  
Lowe,  Denise DR  [WST]  
Butler,  Doug D  [AT]  
Kim, Erika

К сожалению, этот формат не является единообразным во всех данных. Иногда может отсутствовать MI или может быть до 3 MI. Кроме того, [RGN] не всегда присутствует. И что еще хуже, есть довольно много имен, в которых также есть пробелы (например, Da Vinci).

Мне нужно привести эти имена в порядок, чтобы они выглядели так:
Фамилия, Имя.

Я пробовал использовать функции LEFT, RIGHT и Text-to-Columns с минимальным успехом. Пока что лучшим результатом является метод Text-to-Columns, разделенный пробелом, а затем объединение их с помощью (a1&" "&b1). Это работает только, может быть, для половины имен, и в настоящее время я планирую продолжить объединять ячейки (a1&b1&c1 и т. д.), а затем вручную проверять имена, чтобы убедиться, что они верны. Я молюсь и надеюсь, что кто-то сможет спасти меня и мои глаза от этого и у него есть идея, которую я могу попробовать.

решение1

я используюНадстройка RegEx Find/Replaceдля включения функции рабочего листа с регулярными выражениями.

С помощью этого вы можете легко удалить MI и RGN:

=RegExReplace(A1," [A-Z ]*(\[.*)*","",-1)

  1. Найти шаблон" [A-Z ]*(\[.*)*"

    • соответствует пробелу после имени
    • [A-Z ]*опционально соответствует инициалам
    • (\[.*)*опционально соответствует региону
  2. заменяет его на ничто""
  3. заменяет только последнее вхождение-1

введите описание изображения здесь

решение2

Используйте регулярное выражение или что-то подобное, чтобы разбить эту строку на три части. Фамилия, Имя, дополнительная часть.

Вероятно, вы можете использовать ту же функциональность для разделения дополнительных данных на MI и RGN, предполагая, что у вас короткий список RGN (регионов?).

Использовать запятую в качестве разделителя для вытягивания фамилии вперед легко. Найти имена с пробелами может быть сложнее. Однако, предполагая, что те, у которых 3 MI, имеют одиночные инициалы, вы можете искать первый одиночный символ как первый инициал среднего имени. Все, что до него, является именем.

Что бы вы ни делали, делайте это поэтапно. Вытащите фамилии, так как это легко. Остальное — FirstName MI [RGN]. Если скобки есть там, где есть регион, это тоже упрощает задачу. Уберите их.

Остается FirstName MI. Я бы работал справа налево. Если последний символ — пробел, опустите его. Если это символ, а символ слева от него — пробел, то это инициал. Вытащите его и добавьте в начало поля назначения для MI. Повторяйте, пока не получится многосимвольная строка. Это конец FirstName. Поздравляю, вы закончили разделение.

Еще один вопрос: можно ли вернуться в AD, повторно экспортировать и получить отдельные поля, а не отображаемое имя? (PowerShell, Get-ADUser ...) Было бы НАМНОГО проще заново собрать отображаемое имя, чем делать то, что я только что перечислил.

Связанный контент