空白前・空白後で文字を分けて抽出する【エクセル関数】

万一の操作ミスに備え、対象エクセルは作業前にコピー(バックアップ)しておく事をお勧めします。※それに救われた事が多々あります

空白(スペース)の前と後で文字を分ける

氏名などの文字列を空白の前・後でそれぞれ分けて抽出する関数です。

空白は全角・半角いずれかに統一されている必要がありますが、どちらか分からない場合でも一括で変換する事は可能です。※氏名に空白が無いものは空白を追加してください

関数を入力する

C3の関数 (空白より前)
=LEFT(B3,FIND(" ",B3)-1)
D3の関数 (空白より後)
=RIGHT(B3,LEN(B3)-FIND(" ",B3))

図の様なレイアウトの場合の関数なので、配置が異なる場合は関数のセルの値(B3)を変更してください。

また、氏名の空白が半角の場合は関数の「” “」内を半角の空白に変更してください。

1番下まで関数をコピー

関数を入れたC3・D3セル両方を選択状態にし、D3セル右下にマウスでカーソルを合わせてダブルクリックすると関数が一番下までコピーされます。ただし、B列の途中に空欄セルがある場合はその手前の行までしかコピーされません。

その際、書式までコピーされてしまいますので、関数のみを適用するにはオートフィルオプションをクリックして書式なしコピー(フィル)を選択してください。

一番下まで関数コピーできない場合

例えば氏名のセルに何も値が入っていない空欄のセルがあると、関数の式を一番下までパッとコピーできない場合があります。いくつかやり方はありますが、儚くも美しいやり方を紹介します。

色々と使えるエクセル小技
  1. [C列]を右クリックで「挿入(I)」
  2. C列の適当なセルをクリックして「Ctrl + ↓」
  3. 「←」※B列の最下部に移動
  4. 「Ctrl + ↑」 ※B列の一番下の氏名に移動
  5. 「→」 ※C列で関数入れたい一番下の行
  6. 「Ctrl + Shift + ↑」※この後Shiftだけ押しっぱなし
  7. Shiftを押し続けている状態で「↓」を2回 ※関数を入れたい1番上の行に
  8. Shiftを離して数字の「1」をクリック
  9. 「Ctrl + Enter」※数値が直接入力モードじゃない場合はEnter×2回

※何か値が入っていれば良いので「1」じゃなくてもOKです

画像はクリックで拡大し、左右にスライド表示できます

イレギュラーが無い限り、この作業後に関数を入力・下までコピーすれば解決しているはずです。その後C列は削除します。(ここが儚いポイント)

#VALUE! を非表示にしたい

IFFERROR関数でエラーを非表示にします。IFERRORで囲んでしまえばほとんどのケースで有効な手法なのでぜひ覚えておいてください。

C3の関数
=IFERROR(LEFT(B3,FIND(" ",B3)),"")
D3の関数
=IFERROR(RIGHT(B3,LEN(B3)-FIND(" ",B3)),"")

空白を全角or半角で統一する【一括変換】

[B列]を選択(B列が氏名の場合)し、「Ctrl + F」で検索と置換を起動します。半角の空白を全角にする場合は以下の様に入力して「すべて置換(A)」で完了です。

Comment

  1. メイ より:

    お久しぶりです。
    といっても覚えてらっしゃらないかと思いますが、

    https://unity-right.com/globalnavi/

    の記事でコメントしたカスタマイズ乞食のメイでございます。
    今でもunity-rightさんのページが好きで定期的に見に来てしまいます。

    そこで一つ気づいた点があります。昔、さーてんさんのアイキャッチ画像の両端に「わずかな隙間」があった(スマホで見たとき)と思うのですが、それをどのように埋めたのでしょうか?

    というのも自分も同じようにしたく、
    https://wp-cocoon.com/community/customs/%E3%82%B9%E3%83%9E%E3%83%9B%E8%A1%A8%E7%A4%BA%E3%81%AE%E9%9A%9B%E3%81%AB%E8%A8%98%E4%BA%8B%E5%86%85%E7%94%BB%E5%83%8F%E3%82%92%E6%A8%AA%E5%B9%85%E3%81%84%E3%81%A3%E3%81%B1%E3%81%84%E3%81%AB%E8%A1%A8/グローバルナビの設定とカスタマイズ方法【Cocoon】https://unity-right.comスマホ表示の際に記事内画像を横幅いっぱいに表示したい(左右の余白をなくしたい)https://wp-cocoon.comスマホのみアイキャッチ画像を全幅にしたいhttps://wp-cocoon.com

    https://wp-cocoon.com/community/customs/%E3%82%B9%E3%83%9E%E3%83%9B%E3%81%AE%E3%81%BF%E3%82%A2%E3%82%A4%E3%82%AD%E3%83%A3%E3%83%83%E3%83%81%E7%94%BB%E5%83%8F%E3%82%92%E5%85%A8%E5%B9%85%E3%81%AB%E3%81%97%E3%81%9F%E3%81%84/

    を参考にしたのですが、どうしてもスマホ表示では画面端の数ミリが埋まらないのです。

    前は、さーてんさんのアイキャッチも同じようになっていたため、「あ、、この人ができないなら自分もできなくていっか、、、」と放置していた次第です。
    しかし、端の隙間がいつの間にか埋まっていたため、埋めた方法をお聞きしたくコメントさせていただきました。

    いったいどのようにしたのかコメントなり記事にして教えていただけると幸いです。(無関係の記事にコメントすみません)
    どうぞよろしくお願いします。

    • sartensarten より:

      覚えてますよww
      メイさんこんにちわww

      アイキャッチ全幅やりたいの件、私も昨年末くらいにやっと実現したのですが、昨年末か年始のCocoonアップデートでまた余白が生まれてしまいました。

      それをも乗り越えてしまった自分に成長を感じます(自画自賛)

      簡単にですが新たに記事を作成しましたので、ちょっと試してみてください(^∀^)

      • メイ より:

        覚えていただいてるなんて恐縮です震
        まるでストーカーのようにunity-rightさんのページの変更点を観察している変態ですみません震震

        さて、カスタマイズの件ですが解決することができました。
        やっぱり画面端がくっつのはかっこいいです・・・本当にありがとうございました。

        • sartensarten より:

          出来た様で良かったです!

          趣味で勉強しながらサイト作ってる私も変態みたいなもんですから(震

          今後ともよろしくお願いします(^ω^ )

タイトルとURLをコピーしました