Excelで、文字列の中間と前後のスペースor特殊文字を一括して変換する方法

業務で表題の処理をする必要があり、関数を使って処理をしたのでメモ書きします。

リストに従業員名の一覧があり、各ユーザーに対する上司を左側のセルにvlookupで表示するというものですが、vlookupには問題ないはずが引当てがうまくできず、従業員名乗せる内容を確認したところ、スペースと思われる部分に問題があることが分かりました。
※vlookupの検索値が大文字で検索対象が小文字だったため、その問題かとも思いましたが、vlookupは標準では大文字と小文字は区別しないようです。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12142771067

ということで検索値をvlookupで引当てできるように整形しないといけなくなりましたが、行数が多いため手作業では厳しいため、Excelの関数を使って一括処理することにしました。
具体的には、以下画像のように文字列の中間と末尾に空白があるものを、中間の空白を半角スペースにし、末尾の空白の削除を行います。

整形する文字列
整形する文字列


中間の空白は一見半角スペースの様に見えますが、同じfirstname lastnameという文字列とvlookupで照合できないため、通常の半角スペースではないようです。

Excelのデータ>区切り位置の機能で複数の行を一括処理することもできますが、今回の場合、行ごとに空白の位置が異なるため、
このような時は、まず対象の文字列の種類(コード)を特定します。
以下のようにCODE関数とMID関数を組み合わせ、文字列の中の特定の位置の文字のコードを確認します。


=CODE(MID(対象の文字列のあるセル,対象の文字列の先頭からの順番,確認する文字数))
今回の例:=CODE(MID(G3,10,1))

CODE関数
MID,CODE関数


確認した結果、コード160と表示されました。
こちらのコードは海外ではよく使用される形式らしいです。


その他いくつかの行の中間の空白を確認したところ、同じコードだったので、次はTRIM関数とSUBSTITUTE関数を組み合わせ、vlookupで検索できる形に文字列を編集します。


=TRIM(SUBSTITUTE(対象セル,CHAR(対象コード),"変換する文字列"))
今回の例:=TRIM(SUBSTITUTE(G3,CHAR(160)," "))



ここまでできたら、上記セルをそのままvlookupの検索値にして使用すれば照合できるようになります。







コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です