- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
区切り位置の指定 ～ 数値や文字列を異なるセルに分割する ～
メールソフトや年賀状ソフトから、住所録をカンマ区切りのテキストファイル（CSVファイル）などにエクスポートしたものをExcelで開くことがあると思います。
このように自分で最初から作成していないデータファイルをExcelで開いた場合、まれに起こるのが、1つのセルにまとめて文字列が入ってしまうケースです。（インポートとはデータを取り込む、エクスポートは取り出すこと。CSVファイルについては、主な拡張子 を参照してください）
例えば、「姓」と「名」を別の列に分けていたものが同じセルに入ってしまったり、ひどいときには名前も住所も同じセルに1行でまとめて入っていたりします。
また、日付が文字列として取り込まれている場合もまれにあります。例えば、「2018/4/1」として日付認識していたデータが「201841」や「20180401」というただの数字もしくは文字列として取り込まれるケースです。
こうなると、関数を用いて日付を曜日に変換することも困難になります。（曜日変換の関数については、基本操作編の その他の基本的な関数 を参照してください）
こうした場合に、もう一度、エクスポートし直してExcelで開いてみて正しくなればよいですが、なかなかそうはいきません。ではどうするのかというと、Excelはこのような場合でも便利な機能を備えていて、1セルの文字列を分割して複数のセルに再配置することができます。
この機能はExcelを使いこなすうえで非常に便利な機能になりますので、ぜひ習得してください。
まず、下図のように名前の「姓」と「名」が1つのセルに入っている場合に、姓と名に分割して、2つのセルに振り分けてみましょう。
文字列を区切るには、対象セルまたは列や行などの範囲をマウスで範囲指定して、「データ」タブの「区切り位置」ボタンをクリックします。
すると、「区切り位置指定ウィザード」が起動します。
ウィザードとは、対話形式で設定・操作等ができるように工夫された機能のことで、「1／3」とあるように「次へ」ボタンをクリックしながら、3画面進むと操作が完了します。
まず最初に問われるのが「元データの形式」です。2択になっていますが、元データ（つまり選択しているセルの値）がどのようなデータなのかを指定する必要があります。少し難解ですが、ここの理解は結構重要ポイントになってきます。
カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ
これは読んで字のごとく、カンマ「,」やタブ記号「スペースのようなもの」で文字列を区切ってあるデータのことで、カンマ区切りのCSVファイルや、タブ区切りのTVSファイルをExcelで開いた場合を想定しています。
例えば「名前,住所,電話番号」というようにカンマ等の区切り文字で区切った文字列が続いている場合がこれに該当します。通常はこちらを選択すれば問題ありません。
スペースによって右または左に揃えられた固定長フィールドのデータ
こちらは「スペース」という文字に困惑されてしまいがちですが、「固定長フィールド」というのがポイントです。固定長フィールドとは「データの長さ」のことです。
つまり、区切り記号ではなく、データの長さで区切るという意味になります。要するに一定の「文字数」で区切ると考えてください。
したがって、右または左から任意の文字数で区切ることができるデータです。例えば、数字のみのデータで「20180401001001」とあり「日付（20180401）地域コード（001）商品コード（001）」のように桁数がすべて同じ場合に選択します。
では、例題に戻ると、「姓」と「名」の間にスペースが挿入されているため、どちらを選択するのか困惑しますが、この場合は「スペースで区切られている」と考えます。
感覚的に「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択しがちですが、ここは変更せず「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して「次へ」をクリックします。
次の画面で指定するのは「区切り文字」の種類です。この場合は「スペース」で区切られているので「スペース」にチェックを入れます。
このほか、デフォルト（初期設定）では「連続した区切り文字は1文字として扱う」にチェックが入った状態になっており、「文字列の引用符」にダブルクォーテーション「"」が選択された状態になっています。
通常、この部分を変更する必要はありませんが、それぞれの意味を理解しておきましょう。「連続した区切り文字は1文字として扱う」というのは、例えば「姓」と「名」の間のスペースが連続して2つあった場合にどうするかという意味になります。
つまり、スペース1つの「工藤 勝信」という場合と、スペース2つの「塩辛 恭久」が混在する場合です。スペースのみならずカンマ等の記号が連続する場合も同様です。
連続して区切り文字が入ってる場合、例えばスペース2つの場合に、2つを1つの区切り文字として扱うということです。2文字ではなく1文字として認識するという意味です。
このチェックを外すと、連続している区切り文字が2つの場合は2つの区切りがあるものとして扱われ、文字列が3分割されるようになります。
この例の場合では、連続した区切り文字はありませんが、仮にあったとしても1つの区切り文字として扱うので、チェックはそのままにしておきます。
また、「文字列の引用符」についても通常は変更する必要はありません。デフォルトでは「"」になっていますが、例えば「"工藤 勝信"」のように「"」で囲まれた部分は文字列として扱うことを意味しています。
詳しくは割愛しますが、「"工藤 勝信"」の場合、スペースで分割されず、「姓」と「名」に分割することができなくなります。スペースだけでなくカンマであっても同様に区切り文字が文字列の認識になります。
この例の場合では、文字列の引用符はありません。そもそも、通常の利用においてセルに文字列の引用符が入っているケースはほぼないので、ここは「"」のままで問題ありません。
そして、「区切り文字」のチェックを入れることで、画面下の「データのプレビュー」に区切り位置のラインが入ります。スペースの位置で分割されることを確認してください。
このまま「次へ」ボタンをクリックします。次ページは、「3／3」で最後の画面になります。
まず、「列のデータ形式」を指定します。「データのプレビュー」画面では分割された左側の列が選択された状態になっています。つまり、左右の列でそれぞれ指定する必要があります。
前ページ（2／3）では同画面を操作することはできませんでしたが、このページ（3／3）では、マウスでクリックすると、下図のように列を切り替えることができます。
この例の場合、「列のデータの形式」は「G／標準」のままです。説明文にあるように、「G／標準」は、数字は数値に、日付は日付形式の値に、その他の値は文字列に変換してくれるので、ここも基本的に変更する必要はありません。
ただし、数値を文字列で表示したい場合（「1」を「001」と表示したい場合）などは「文字列」を選択します。「日付」と「削除」については後述します。
そして、その下の「表示先」で2つに分割した列をどのセルに配置するのかを指定します。
分割した2つの列はどのセルにでも移動させることが可能ですが、指定するのは列の「先頭」にあたるセルになります。ここでは、隣の「C3」セルを指定します。マウスでクリックすると自動的に記述されます。
この「表示先」はどのセルを指定することも可能だといいましたが、現在の列である「B列」を指定することも可能です。ただし、分割した列は隣り合う位置に配置されます。
例えば、「C列」のセルを指定した場合は、「C列」の隣の「D列」に分割された文字列が配置されます。そのため、「データのプレビュー」画面で切り替えた列をそれぞれ指定することはできません。
表示先のセルを指定したら「完了」ボタンをクリックします。すると、下図のように「C列」と「D列」に「姓」と「名」を分割することができました。そして、区切り文字である「スペース」は削除されます。したがって、区切り文字がカンマであれ何であれ削除されることになります。
以上で、簡単なデータ分割の操作方法は終了です。次は、いろいろなパターンのデータを分割して再配置してみましょう。
今度は、下図の「E列」ように「郵便番号」と「住所」をカンマで区切った文字列を分割して再配置します。
同様にE列を範囲指定して「区切り位置」ボタンをクリックし、「区切り位置指定ウィザード」を起動します。この場合は、一目瞭然「カンマ」で区切ってあるので、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して次に進みます。
そして、「区切り位置」には「カンマ」を選択します。「データのプレビュー」で正しい位置に区切りのラインが引かれたことを確認して次へ進みます。
最後の画面の「列のデータ形式」は標準で問題ありませんが、ここでは「表示先」に範囲指定した自身のセルを指定してみましょう。つまり、同じ「E列」を指定することができます。
これで「完了」ボタンをクリックすると、「既にデータがありますが、置き換えますか？」のメッセージが表示される場合がありますが、問題ないので「OK」ボタンを選択します。
すると、下図のように元データが入力されていた「E列」から隣の「F列」にかけて分割されたデータが再配置され、カンマは削除されます。
このように、自身のセル（列）を指定することで、新たな列を何列も作成する必要がなく、今ある列を活用することができるので、非常に便利です。
次は、その他の区切り文字で分割してみましょう。また、不要な文字をついでに削除することもできます。
今度は、先ほどの例の郵便番号をハイフン「-」で区切り、前半部分と後半部分に分け、なおかつ郵便マーク「〒」を削除してみます。
まず、郵便マーク「〒」のことは後にして、ハイフン「-」で分割するには、ウィザードの「2／3」ページ目で、「区切り文字」に「その他」を選択し、任意の文字（この場合はハイフン「-」）を記述します。
これで、「データのプレビュー」に区切りラインが引かれます。つまり、ここに入力できる文字や数値であれば、その値で区切ることが可能です。「〒」や「&」といった文字でも区切ることもできます。これは非常に便利な機能なので、ぜひ覚えておいて活用してください。
ただ、この方法では郵便番号の前半と後半に分割することは容易ですが、郵便マーク「〒」を削除することはできません。削除するにはどうするかというと、「3／3」ページ目で、「列のデータの形式」に「削除」を選択すると削除することができます。
しかし、あくまで「列」の削除であって、郵便マークだけを削除することができないのです。この場合では「〒」マークから「-」までの前半の番号すべてが削除されてしまうのです。
では、どうするのかというと、こういう場合に前述の「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択します。
郵便番号は長さが同じ桁数で構成されているので、「スペースによって右または左に揃えられた固定長フィールドのデータ」に合致しています。こちらを選択して次の画面に進みます。
すると、「2／3」ページ目の画面が異なってきます。区切り位置の「作成」、「削除」、「移動」と説明分がありますが、要は、マウス操作で自由に区切り位置を指定することができるということです。
どのように指定するのかというと、「データのプレビュー」のウィンドウで任意の位置にマウス操作でラインを引きます。説明文のとおりラインの作成はクリック、削除はダブルクリック、移動はドラッグで描画します。
まず、郵便マーク「〒」のみを削除したいので、マークの直後にラインを引き、ハイフン「-」も不要になるので、ハイフンの前後にもラインを引きます。
このように自由にラインを引けるので、文字列を4分割することができます。そして次へ進み、それぞれの列で「列のデータ形式」を指定します。
前述のとおり「データのプレビュー」で列を切り替えながら、「〒」と「-」の列で「削除する」を指定することで、〒マークとハイフンといった記号のみを削除することができます。
このように、郵便番号を前半部分と後半部分に分け、なおかつ不要な記号も一度に削除できました。「列のデータ形式」は「標準／G」を選択したので、値は数値認識となりゼロサプレスとなります。ゼロを表示させたい場合は、「文字列」を選択するか、ユーザー定義で表示させる必要があります。（詳しくは、書式のユーザー定義 を参照してください）
では、次からは、ちょっとした応用編です。
この「区切り位置の指定」機能は、なにも文字列の分割だけではなく、表示形式を変換したい場合などにも重宝します。例えば、日付形式に変換したい場合です。
CSVファイルを開いた場合などによくあるのが、もともと日付形式だった値が、下図のようにただの数字や文字列になってしまっている場合です。
この場合、数字は右揃えレイアウトとなっており、Excelは数値認識しています。こうなると、セルの書式設定から書式を「日付」に変更したり、書式のユーザー定義 で学習した「ユーザー定義」を駆使しても、なかなか日付形式に変換することはできません。
かといって、手打ちでスラッシュ「/」などの区切り文字を入力していくのは大変な手間です。こういった場合に、区切り位置指定ウィザードを使うと、日付形式に変更できるようになります。
この場合は、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」と「スペースによって右または左に揃えられた固定長フィールドのデータ」のどちらを選択しもかまいません。
文字列を区切ることなく「3／3」ページ目まで進みます。文字列を区切っていないので、「データのプレビュー」画面は全列が選択された状態になっています。
この状態で「列のデータ形式」に「日付」を選択します。日付のリストメニューも任意の形式を選択します。「YMD」が「yyyymmdd」の形式になります。（詳しくは、書式のユーザー定義 を参照してください）
すると、数値データが日付形式に変換されます。
ただし、この方法で正しく変換されるのは「8桁」の数値のみです。例えば、「20180401」が「201841」のように「0」が省略されている場合や、存在しない月数や日数が入力されている場合は日付形式に変換されません。
では最後に、もう1つテクニックをご紹介します。
これは、そもそも区切り文字が入っていない文字列を上手に分割する方法です。少々イレギュラーな方法ですが、なかなか有用なので活用してみてください。
例えば住所録を開いた場合で、都道府県ごとの集計を取りたい場合があったとします。そんなときは、住所を「都道府県」と「市町村」に分割することで集計が可能になります。
このような場合、区切り文字もないうえ、都道府県は文字数も異なるので、同じ長さで区切ることはできません。「県」という文字を区切り文字として区切ることもできますが、そうすると「県」の文字が区切り文字とみなされて消去されてしまいます。
ではどうするのかというと、無理やり区切り文字を入れ込んでやればよいのです。
入れ込む方法は「置換」を使います。下図のように、「県」を「県,」のように区切り文字を入れ込んだ文字列に置換します。
すると、すべての県のあとに区切り文字であるカンマが挿入されます。（検索と置換について詳しくは、文字列の検索と置換 を参照してください）
この状態であれば容易に分割することができます。このようにして、置換を使うことで区切り文字がない文字列でも分割できる場合があります。
以上で、区切り位置の指定と分割は終了です。文字列は、分割することで集計が容易になり、活用の幅が広がるので、ぜひ活用してみてください。
更新履歴
- 2018年3月16日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！