エクセル関数で検索地の空白を無視して検索したい

質問

エクセル関数で下記表の電話番号（A)と同じものを検索して住所(B)を導き出す際、電話番号(A)のセルに空白があるとうまく検索できません。
空白を無視するまたはセル内の文字列のみを対象にして検索できませんか。

シート1
　　　A　　　　　　　　　　　B　　　　　　　　C
03-3333-****　　　　東京都　　　　　　犬
045-642-****　　　　神奈川県　　　　ねこ
03-1111-****　　　　東京都　　　　　　兎

シート2
　　A　　　　　　　　　　　　　　　　B　　　　　　　　
03-3333-****　　　　VLOOKUP（A1.シート1A1:A3，2）　=東京都　　　　
045-642-****□□　VLOOKUP（A2.シート1A1:A3，2）　=エラー
　　　　　　　　　　　　←A2の欄にある資料から電話番語をコピーすると、番号の後に
　　　　　　　　　　　　　空白スペースがコピーされてしまうためエラーが出てしまう。
　　　　　　　　　　※空白を無視して検索することはできませんか。
わかりにくい質問で申し訳ありません。

回答No.2

kagakusuki · ベストアンサー

尚、やむを得ない事情により、空白スペースを削除する訳には行かない場合において、もし、Sheet1のA列の方に入力されている電話番号には空白スペースが入っていないという事でしたら、次の様な方法もあります。

【空白スペースが電話番号の前か後ろのみに付いているだけで、電話番号の途中に空白スペースが挟まっている事は無い場合】

=VLOOKUP(TRIM($A1),Sheet1!$A:$A,2,FALSE)

【電話番号の途中に空白スペースが挟まっている事もある場合】

=VLOOKUP(SUBSTITUTE(SUBSTITUTE($A1," ",),"　",),Sheet1!$A:$A,2,FALSE)

　尚、もしも、空白スペースが入っている理由が、単に「電話番号がセル内に表示される位置を左詰めや中央揃えにしたい」というだけの事である場合には、それはやむ得ない事情とは言えません。
　その様な場合には、空白スペースを削除してしまった上で、セルの書式設定の配置を[左詰め]や[中央揃え]にするのが一般的なやり方です。

回答No.6

kagakusuki

もし、空白スペースが電話番号の後ろにのみ付いていて、電話番号の途中に空白スペースが挟まっている事は無い場合には、次の様な方法もあります。

=VLOOKUP(TRIM($A1)&IF(COUNTIF(Sheet1!$A:$A,TRIM($A1)&" *")," *",IF(COUNTIF(Sheet1!$A:$A,TRIM($A1)&"　*"),"　*","")),Sheet1!$A:$C,2,FALSE)

回答No.5

kagakusuki

尚、Sheet1のA列に入力されている電話番号の中にも空白スペースが含まれているものがあり、尚且つ、処理が重くなる事は避けたい、という場合には、関数を使用して一旦、別の列に空白スペースを削除した電話番号を表示させておき、その新たに表示させた空白スペース抜きの電話番号を使用して、検索を行う様にします。
　この様に、検索等の情報処理が行い易い様にするために使用する列の事を作業列と言います。

　今仮に、Sheet3のA列を作業列として使用するものします。
　まず、Sheet3のA1セルに次の様な関数を入力して下さい。

=SUBSTITUTE(SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW())," ",),"　",)

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に（Sheet1において電話番号の表として使用されている最下段の行を上回るのに十分な行数となるまで）貼り付けて下さい。
　その上で、Sheet2のB1セルには次の様な関数を入力して下さい。

=INDEX(Sheet1!$A:$C,MATCH(SUBSTITUTE(SUBSTITUTE($A1," ",),"　",),Sheet3!$A:$A,0),2)

回答No.4

kagakusuki

配列数式は処理が重くなってしまうため、前に投稿した回答No.1や回答No.2では御勧めしなかったのですが、もし、配列数式の様な処理が重くなってしまう方法でも構わないという場合には、SUMPRODUCT関数を使用するという方法もあります。
（こちらは、配列数式ではなく通常の関数ですので、「Ctrl+Shift+Enter」は不要です）

【空白スペースが電話番号の前か後ろのみに付いているだけで、電話番号の途中に空白スペースが挟まっている事は無い場合】

=INDEX(Sheet1!$A:$C,SUMPRODUCT((TRIM(Sheet1!$A$1:$A$3)=TRIM($A1))*ROW(Sheet1!$A$1:$A$3)),2)

【電話番号の途中に空白スペースが挟まっている事もある場合】

=INDEX(Sheet1!$A:$C,SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1:$A$3,"　",)," ",)=SUBSTITUTE(SUBSTITUTE($A1,"　",)," ",))*ROW(Sheet1!$A$1:$A$3)),2)

回答No.3

bunjii

>A2の欄にある資料から電話番語をコピーすると、番号の後に空白スペースがコピーされてしまうためエラーが出てしまう。　>※空白を無視して検索することはできませんか。
検索値の前後に空白（半角/全角）があるときはTRIM関数で両端の空白を除けば正常に検索できます。

=VLOOKUP(TRIM(A1),シート1!A$1:A$3,2) → 東京都
=VLOOKUP(TRIM(A2),シート1!A$1:A$3,2) → 神奈川県

範囲のキー列の文字列の右側に空白があるときはTRIM関数で処理しなくても無視されます。
左側に空白があるときはTRIM関数で処理する必要があります。

=VLOOKUP(TRIM(A1),TRIM(シート1!A$1:A$3),2) → 東京都

この時、TRIM関数の返り値は配列値を必要とするため、式を入力後Ctrl+Shift+Enterで確定します。
尚、シート名とセルの間に ! を忘れないようにしてください。
また、式を他のセルへコピーするときはセルのアドレスを絶対アドレスと相対アドレスを使い分けるようにしてください。
今回の例では範囲のA1:A3をA$1:A$3とすることで下へコピーしても範囲が固定されますので正確に処理されます。

回答No.1

kagakusuki

空白スペースが入っている事が問題になっているのでしたら、A列に入力されている空白スペースを全て削除してしまえば良いと思います。
　その際、一々、1セル毎に削除して行ったのでは手間が掛かり過ぎますので、次の様な操作を行う事で、A列に入力されている空白スペースを一括して削除されては如何でしょうか。

A列全体をまとめて範囲選択
　　↓
[Ctrl]キーを押しながら[H]キーを押す
　　↓
現れた「検索と置換」ダイアログボックスの[置換]タブをクリック
　　↓
「検索する文字列」欄に半角の空白スペースを1文字だけ入力
　　↓
「置換後の文字列」欄には（空白スペースも含めて）何も入力されていない状態にする
　　↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
　　↓
「検索する文字列」欄に全角の空白スペースを1文字だけ入力
　　↓
「置換後の文字列」欄には（空白スペースも含めて）何も入力されていない状態にする
　　↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
　　↓
「検索と置換」ダイアログボックスの[閉じる]ボタンをクリック