☰

セルからテキストを削除する

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
セルからテキストを削除する

一般的に次の形式になっている 54,000 を超える名前を含む AD 抽出データがあります (サンプル データ):

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 が 1 つも存在しない場合もあれば、MI が 3 つ存在する場合もあります。また、[RGN] が常に存在するわけではありません。さらに悪いことに、スペースを含む名前もかなりあります (例: Da Vinci)。


これらの名前を整理して、 LastName、FirstNameと読めるようにする必要があります。

LEFT、RIGHT、および Text-to-Columns 関数を試してみましたが、ほとんど成功しませんでした。これまでのところ、最良の結果は、スペースで区切られた Text-to-Columns 方式で、(a1&" "&b1) を使用して結合したものです。これは名前の半分程度しか機能しないため、現在は、引き続きセルを結合し (a1&b1&c1 など)、手動で名前を確認して正しいことを確認する予定です。誰かが私と私の目をこのことから救ってくれて、試すためのアイデアを持っていることを祈り、願っています。

答え1

私が使う正規表現検索/置換アドイン正規表現対応のワークシート関数を使用します。

これにより、MI と RGN を簡単に削除できます。

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

  1. パターンを見つける" [A-Z ]*(\[.*)*"

    • 名前の後のスペースに一致します
    • [A-Z ]*オプションでイニシャルに一致
    • (\[.*)*オプションで地域に一致
  2. 何もない状態に置き換える""
  3. 最後に出現したものだけを置き換えます-1

ここに画像の説明を入力してください

答え2

正規表現またはそれに類似したものを使用して、その文字列を 3 つの部分に分割します。LastName、FirstName、extra。

RGN (地域?) の短いリストがあると仮定すると、おそらく同じ機能を使用して余分な部分を MI と RGN に分割できます。

カンマを区切り文字として使用して姓を先頭から取り出すのは簡単です。スペースを含むファーストネームを見つけるのは難しいかもしれません。ただし、3 つの MI を持つものは単一のイニシャルであると仮定すると、最初の 1 文字を最初のミドルネームのイニシャルとして探すことができます。それより前の文字はすべてファーストネームです。

何をするにしても、段階的に行ってください。姓は簡単に削除できます。残りは FirstName MI [RGN] です。地域を示す括弧がある場合は、これも簡単になります。括弧を取り除いてください。

残るは FirstName MI です。右から左に作業します。最後の文字がスペースの場合は削除します。最後の文字が文字で、その左側の文字がスペースの場合は、イニシャルです。それを取り出して、MI の宛先フィールドの先頭に追加します。複数文字の文字列になるまで繰り返します。これで FirstName は終わりです。おめでとうございます。これで分割がすべて完了しました。

もう 1 つの質問: AD に戻って再度エクスポートし、表示名ではなく個々のフィールドを取得できますか? (powershell、Get-ADUser ...) 表示名を再構築する方が、先ほど挙げた操作を実行するよりもはるかに簡単です。

関連情報