今回の記事では
スペース(空白)を基準にして文字列を左右それぞれに分ける関数って無いの？
なるべく簡単な方法で分けられたら便利だけどそんな方法ってるのかな
関数で左右に分けるのも便利だけど他の方法で分けるにはどうしたら良いの？
複数の関数を使ったりネストさせるのって覚えるのが大変だから他の方法も知りたい
こういった疑問に答えます
✔空白を基準に文字列を左右に分ける方法のテーマ
①空白を基準に文字を左右に分けるには4つの関数を組み合わせれば実現出来ます
②区切り位置機能を使えば空白を基準に文字列を左右に分けることは簡単に出来ます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「空白を基準に文字列を左右に分ける方法」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
空白を基準に文字を左右に分けるには4つの関数を組み合わせれば実現出来ます
空白を基準に関数を使って左右に分けるには4つの関数を組み合わせることで実現出来ます
理由は関数を組み合わせないと空白の位置や文字数の取得が出来ないからです
では実際に作業をしながら今回の方法について整理していきましょう
例題として氏名を「性と名に分ける方法」を元に解説していきます
目的はこんな風に氏名を「性と名」に分けて表示させることです
次は実際に「性と名」を分ける方法を見ていきましょう
氏名から「性」を取得する方法
まずは「性」を分ける方法です
ポイントはLEFT(レフト)関数とFIND(ファインド)関数をネストさせることです
関数の使い方を１つずつ見てみましょう
まず「FIND関数」は「=FIND(“ ”,$A5)」のように設定します
こちらを日本語にしてみましょう「=FIND(空白(全角)の位置を見つけてね,A5のセルの中で)」といった感じです
FIND関数は指定した文字や数字を見つけ出す関数なので今回は「” ”」という文字(空白)の位置を左から数えて見つけ出してくれたという訳です
ちょっと分かりづらいのでFIND関数の動きを図解にしてみましょう
こんな感じで左から順に目的の文字(空白)を見つけられたのでこの場合は「3」という数字を返してくれます
これだけなのですごく簡単ですよね
次のポイントは「LEFT関数」ですね
LEFT関数もすごく簡単で「左から○文字」の文字を表示してくれる関数です
つまり「=LEFT($A5,2)」と指定すると「=LEFT(A5のセルの内容を左から,2文字取得してね)」という意味になります
LEFT関数の動きも図解しておきましょう
こんな風に左端から指定数分の文字を表示してくれるのがLEFT関数です
では、「=LEFT($A5,FIND(“ ”,$A5)-1)」はどんな設定方法なのか、まずは日本語にしてみましょう
「=LEFT(A5のセルを,FIND関数の数字-1文字分表示してね)」という意味になります
この設定のポイントは「FIND(“ ”,$A5)-1」ですね
どうして「-1」をするのか不思議ですよね
コレは「=LEFT($A5,FIND(“ ”,$A5))」だったとすると「=LEFT($A5,3)」と同じ内容になりますね
この場合「左から3文字(空白の位置)」取得してねという意味になるのでこのままだと「竹村 」となって必要がない「全角のスペース(空白)」まで取得してしまうので「-1」をして「左から2文字」を表示してねと設定しています
でも、どうしてわざわざ2つの関数を組合せる必要があるのでしょうか？
答えは名字の文字に変化があっても柔軟に対応出来るようにするためです
例えば、3文字の名字であっても柔軟に対応してくれます
念の為に今回の動きを図解にしてみましょう
こんな感じで空白の位置を取得して-1で左から「○文字分取得する」という設定をすることで柔軟に「性」を切り抜くことが出来ます
次は「名」を取得する方法について見てみましょう
氏名から「名」を取得する方法
名を取得するには次のように3種類の関数を組合せる必要があります
こんな風に「RIGHT関数+LEN関数+FIND関数の組合せ」で右から○文字を取得するという形で「名」を表示させる必要があります
RIGHT関数はLEFT関数と反対の動きをするだけで設定方法は一緒です
FIND関数も設定内容は先程と同じなので説明は省略します
右側から○文字目を得的するためのポイントはLEN(レン)関数にあります
LEN関数は指定された文字が何文字あるかを数字で返してくれる関数で「空白を含んだ」文字数を教えてくれます
こんな風に対象のセルの文字数を表示してくれました
では、今回のポイントになる右から何文字分文字を取得させればいいか、という部分を見てみましょう
こんな風に3つ以上の関数をネストさせる場合は、少しずつ考え方を整理するのが混乱しないためのコツです
一番重要なのが「=LEN($A6)–FIND(“ ”,$A6)」の部分で最後にRIGHT関数で囲むと思えば分かりやすいと思います
この式を日本語にすると、「=LEN(A6のセルのすべての文字を数えてね)–FIND(A6のセルの空白の位置を数えてね)」という意味なのでこの部分だけを式にすると
「7文字-4文字=3文字」となります
こんな風に全体の文字数から空白の文字までを引きます
その差つまり空白以降の文字数を基にRIGHT関数で取得すれば、右から3文字分を表示できるので「名」を取り出せるという訳です
それが「=RIGHT($A6,LEN($A6)-FIND(“ ”,$A6))」の式になります
考え方がちょっと難しいですが、この方法であれば名前が何文字でも柔軟に切り分けてくれるという訳です
繰り返しになりますが、空白を基準に関数を使って左右に分けるには4つの関数を組み合わせることで実現出来ます
よくある質問：性と名で文字を分けることは出来たけど、全角と半角のスペースが違うとエラーになるのはどうして？
よくある質問ですが、スペースは全角か半角どちらかに合わせるのが正解です
理由はExcelなどPC上は全角と半角は全く違うものだからです
では具体的な方法について見ていきましょう
エラーが出るのはこんな場合です
こんな風にFIND関数が「全角のスペース」を探していますが、5～14行目の氏名のスペースは半角になっていることが原因でエラーが発生しています
こういった全半角が混ざった状態は実務でもよくあることなので、サクッと修正する方法を覚えておきましょう
その方法は置換(ちかん)機能を使います
置換とは要は置き換えることです
修正したいのは半角のスペースを全角にしたいので次の手順で作業をします
- 修正したい範囲の選択
- 置換機能で半角スペースを全角スペースに変換
これだけです めっちゃ簡単ですね
※今回のように全半角が文字や関数にも設定されている場合はトラブル防止のために変換したい範囲を限定しておきましょう
「編集グループの検索と選択ボタン」をクリック→「置換」をクリック
「検索する文字列に半角スペース」を入力→「置換後の文字列に全角スペース」を入力→「すべてを置換」をクリック
置換完了のダイアログを確認したら「閉じる」ボタンをクリック
これで選択範囲がすべて置換されました
※スペースの置換は全半角どちらもスペースが見えませんが、ちゃんと設定できていれば問題なく修正できます
繰り返しになりますが、スペースは全角か半角どちらかに合わせるのが正解です
区切り位置機能を使えば空白を基準に文字列を左右に分けることは簡単に出来ます
名前を分けるには区切り位置機能を使えばもっと簡単に分けることが出来ます
なぜなら、関数を使わなくても区切り位置機能を使えばスペースを基準に自動的に文字を分けてくれる機能があるからです
手順はすごく簡単なので実際のやり方を見ていきましょう
先に氏名を全て選択しておきます
「データタブのデータツール」→「区切り位置」をクリック
「次へボタン」をクリック
「スペースにチェック」→「次へ」ボタンをクリック
「表示先」をB5に設定して「完了ボタン」をクリック
警告画面がでますが「OKボタン」をクリック
コレで完了です
すごく簡単で関数を設定するよりも簡単ですね
あっけないほど単純ですが、関数まだ使いこなせない場合はこちらの方法を活用してみましょう
繰り返しになりますが、名前を分けるには区切り位置機能を使えばもっと簡単に分けることが出来ます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す