【簡単】エクセルの文字列を分割する3つの方法

エクセル初心者

エクセルって、文字列が入ったセルを分割できるの?

宛名ラベルを作るために、住所を入力したセルを分けたいんだけど・・・

宛名ラベルを作るときって、「このセルは分けておけば良かった!」って思うことがよくあるよね。

エクセルの文字列の分割はとても簡単なので、一緒に試してみよう!

Dr.オフィス

今回は、エクセルの文字列を分割する方法について解説します。分割する方法を3つご紹介するので、状況に応じて使い分けられますよ。

文字列の分割を使って、リスト整理を効率よく行いましょう!

事務経験10年以上の私が、文字列を分割する方法を分かりやすく解説するよ!

Dr.オフィス

文字列の分割は、エクセルのリストを整理する上で重要な作業となります。

手作業でセルを分けるのは大変ですが、文字列分割の方法を知るだけで、簡単にリストを整えることができますよ！

※本記事は『OS:Windows11』画像は『Excelのバージョン:Microsoft365』を使用しています。

文字列を分割するにはエクセルの「区切り位置」を使おう!

今回は県庁所在地一覧を使って、文字列の分割について解説していきます。

まずは「県知事名」の苗字と名前を分割してみましょう。

作業前にバックアップを取ることを忘れずに!

Dr.オフィス

県知事名は、いずれのセルにも苗字と名前の間に半角スペースが入力されていますね。

このように、文字列を分割したい位置の前後にスペースや記号などがある場合は、エクセルの「区切り位置」機能を使うのが便利です。

エクセルの「区切り位置」機能の使い方

「区切り位置」機能はエクセルの指示に従って操作するだけなので、とても簡単です!

1. STEP

   文字列を分割したいセルを選び、データ→「区切り位置」を選択

   

   1. 文字列を分割したいセルを選択
   2. 「データ」タブを開く
   3. 「区切り位置」をクリック
2. STEP

   「区切り位置指定ウィザード」に従って操作

   あとは「区切り位置指定ウィザード」に従って操作するだけです。

   

   1. データのファイル形式は「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」にチェック
   2. 「次へ」をクリック
3. STEP

   区切り文字を選択

   

   1. 区切り文字は「スペース」にチェック
   2. データのプレビューで、区切り位置が正しく表示されているか確認
   3. 「次へ」をクリック

   そのほかの区切り文字について
   タブ:スペースと同じような、見えない記号のことです
   セミコロン:「　;　」のことです。下のポツにひげが付いているので注意!
   コンマ:「　,　」のことです
   その他:好きな文字を区切り文字にすることができます（後で解説します）

4. STEP

   データ形式を選択

   

   1. 列のデータ形式を選択
      分割した文字列が数字の場合は「文字列」をクリック
      それ以外は「G/標準」でOK
   2. データのプレビューで、文字が正しく表示されているか確認
      なお、区切られた文字列ごとにデータ形式を選ぶことができます（この例では分かりやすいように左側を「G/標準」右側を「文字列」としています）
   3. 文字列を分割する先を選択するために「↑」をクリック
   4. 文字列の分割を表示させるセル（表示先）をクリック
   5. 「完了」をクリック

これで県知事名の苗字と名前が分割できました。

なお、文字列分割の表示先のセルと表示元のセルを同じにした場合は、表示元のセルが上書きされます（分割する前の文字列が削除されます）。

また、表示先のセルに他のデータがすでにある場合は、すでにあるデータが上書きされます。

文字列の分割はスペースやコンマなどの記号がなくてもOK

エクセル初心者

苗字と名前の間にスペースがある場合はいいけど、スペースやコンマがない場合は「区切り文字」機能は使えないよね・・・

そのようなときでも、文字列を分割したい位置の前後に同じ文字が入力されている場合は、「区切り文字」機能を使って文字列を分割することができます。

諦めて手作業で分割していたリストが、簡単に分割することができるかも!

Dr.オフィス

このエクセルリストの「年齢・性別」セルを「年齢」と「性別」に分割したい場合、いずれのセルにも入力されている「歳」を区切り文字として使うことができます。

1. STEP

   文字列を分割したいセルを選び、データ→「区切り位置」を選択

   

   1. 文字列を分割したいセルを選択
   2. データタブを開く
   3. 「区切り位置」をクリック
2. STEP

   「区切り位置指定ウィザード」に従って操作

   

   1. データのファイル形式は「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」にチェック
   2. 「次へ」をクリック
3. STEP

   区切り文字を選択

   

   1. 区切り文字は「その他」にチェックし、「歳」を手入力
   2. データのプレビューで、区切り位置が正しく表示されているか確認
   3. 「次へ」をクリック
4. STEP

   データ形式を選択

   

   1. 列のデータ形式を選択
      分割した文字列が数字の場合は「文字列」をクリック
      それ以外は「G/標準」でOK
   2. データのプレビューで、文字が正しく表示されているか確認
   3. 文字列を分割する先を選択するために「↑」をクリック
   4. 文字列の分割を表示させるセル（表示先）をクリック
   5. 「完了」をクリック

これで、年齢と性別が入力された文字列を分割することができました。

区切り文字となった「歳」の文字は消えてしまいますので、必要に応じて追加してください。

住所の「市」の位置で「区切り位置」機能を使って文字列を分割したい場合

ひとつのセルに入力された住所を「区切り位置」機能を使って分割したい場合は、先ほどの「歳」と同じように「市」や「町」などを区切り文字にしてしまうのが手っ取り早いように思えます。

ですが、区切り文字を変えて何回か区切り位置の作業をしたり、「市川市」「四日市市」など「市」が地名に含まれている場合などは、その分だけ区切り位置が増えてしまい手修正が必要となるため、かえって手間になってしまいます。

そのため、「市」や「町」のあとにコンマやセミコロンなどの記号を挿入し、この記号を区切り文字として文字列分割を行いましょう。

手順が増えてしまいますが、ひとつひとつのセルを手作業で分割するよりは早くて確実に作業することができます。

1. STEP

   文字列を分割したいセルを選び、ホーム→「置換」を選択

   

   1. 文字列を分割したいセルを選択
   2. ホームタブを開く
   3. 「検索と選択」をクリック
   4. 「置換」をクリック

   置換について詳しくは【エクセルの置換】使い方を基本から応用まで解説！をご覧ください！

2. STEP

   「市」の後にセミコロンを入力して置換
   

   

   1. 置換タブをクリック
   2. 検索する文字列に「市」を入力
   3. 置換後の文字列に「市;」を入力（市のあとにセミコロンを入力）
   4. 「すべて置換」をクリック
3. STEP

   関数で、セルの中に含まれているセミコロンの数を数える

   

   

   「　= LEN(F4)-LEN(SUBSTITUTE(F4,”;”,””))　」と入力し、セル内に含まれたセミコロンの数を数える

   「〇〇市」という住所の場合、通常はセミコロンの数は1つとなるはずです。
   ですが、「市川市」「四日市市」などのように市名に「市」が複数含まれていたり、町名以下に「市」がつく地名もあったりするため、セミコロンの数が1つより多くなる場合もあります。
   そのため、セル内のセミコロンを数える数式を作成し、「〇〇市」以外に「市」という文字が含まれていないか確認しています。

   文字を置換するSUBSTITUTE関数については、こちらの記事「SUBSTITUTE関数を使って特定の文字を別の文字にする方法！」をご覧ください！

   また、文字数を数えるLEN関数は知っておくと便利です。ぜひ、「LEN関数は簡単に文字の数を表示できる優れもの！！」を読んでマスターしてくださいね。

4. STEP

   フィルターでセミコロンの数が1以外のセルを抽出し、手作業で修正

   

   1. リストのタイトル行をクリック
   2. 「データ」タブを選択
   3. 「フィルター」をクリック
   4. 「セミコロンの数」の下向き三角（▼）をクリック
   5. 「1」のチェックを外す
   6. OKをクリック

   

   セミコロンの数が1以外の場合は、区切り位置を確認するために手作業の修正が必要です。

   今回は、次の作業を行いました。

   • 千葉県千葉市中央区市場町の「市」と「場」の間にあったセミコロンを削除
   • 東京都新宿区は「市」がないので「区」の後にセミコロンを入力

   例えば市川市が多いなど同じ修正が続く場合は、置換機能を使って「市;川市;」を「市川市;」に置換すると効率が良いでしょう。

5. STEP

   「区切り位置」機能を使って文字列を分割

   

   あとはセミコロンを区切り文字にして、データ→「区切り位置」機能で文字列の分割をします。

このように、一見手ごわそうな住所も、少しの工夫で簡単に分割することができます。

「区切り位置」機能で文字列を分割する際の注意点

簡単でとても便利な「区切り位置」機能ですが、ひとつだけ注意点があります!

分割した文字列が住所の番地だった場合、区切り位置指定ウィザードのデータ形式を「G/標準」のままにしておくと、数字によっては、エクセルが「これは日付だ」と判断して表示がおかしくなってしまいます!

上記の例の場合、番地が「2023-8」と、年月に見えなくもない紛らわしいものとなっています。

そのため、番地のデータ形式を「Ｇ/標準」と「文字列」とした場合ではセルに表示される結果が異なってきます。

分割した文字が番地や部屋番号などの数字になる場合は、データ形式を「文字列」に設定しておいたほうが無難です。

エクセルの「フラッシュフィル」機能で文字列を自動的に分割

文字列の分割が自動的にできる「フラッシュフィル」機能。エクセル2013から搭載された機能で、セルに入力されたデータから自動的に法則性を見つけ出し、列全体に同じ処理をすることができます。

とても便利な機能ですので、「エクセルのフラッシュフィルで時短作業！」の記事もご覧になりながら、ぜひ活用してください。

関連

エクセルのフラッシュフィルで時短作業！関数よりも速い裏技を伝授

今回は「フラッシュフィル」機能を使って、電話番号を「市外局番・市内局番・加入者番号」の3つに分割してみましょう。

1. STEP

   事前準備のため、セルの表示形式を文字列に変更

   

   電話番号は先頭が0で始まるため、そのままコピー＆ペーストをすると先頭の0が消えてしまいます。そのため、先頭の0が消えないように、セルの書式を文字列に設定しておきます。

   1. 電話番号を分割する先のセルを選択（リストの下に別のデータを入力することがなければ、行全体を選択しても構いません）
   2. 「ホーム」タブを選択
   3. 「数値の書式」の下向き「く」の字をクリック
   4. 「文字列」をクリック
2. STEP

   1行目は法則性を手入力

   

   フラッシュフィル1行目は、エクセルに法則性を教えるために手作業で入力する必要があります。

   1行目の「市外局番」「市内局番」「加入者番号」をそれぞれ分けてコピー＆ペーストします。

3. STEP

   データ→「フラッシュフィル」をクリック

   

   1. STEP2で入力した市外局番をクリック
   2. 「データ」タブを選択
   3. 「フラッシュフィル」をクリック
4. STEP

   市外局番が自動的に分割された

   

   エクセルの「フラッシュフィル」機能が「ハイフンの位置で文字列を分割する」と判断したため、市外局番が2ケタの場合でも4ケタの場合でも自動的に分割されています。

   同じように「市内局番」「加入者番号」も同様にフラッシュフィル機能で分割します。

   

このように、法則性が明確な場合は、「フラッシュフィル」機能を使うことで「区切り位置」機能よりも早く文字列の分割をすることができますので、状況に応じて使い分けてみてくださいね。

文字列を分割するための便利な関数

文字列を分割する際に「区切り位置」機能はとても便利ですが、リストの列数や形式が固定されている場合や、会社のシステムからダウンロードしたデータを使ってリストを作成する場合などは、リストを更新するたびに「区切り位置」機能で文字列を分割しなければならず、少し面倒です。

その場合はエクセルの関数を予め準備しておくと効率がよいでしょう。

リストを更新する際は以前のリストから関数をコピーアンドペーストすることで、「区切り位置」機能を毎回行うよりもミスや作業忘れが少なくなります。

文字列の分割に便利な関数をいくつかご紹介しますので、どの関数が活用できそうか、チェックしてみてくださいね。

文字列分割の要となるFIND関数

FIND関数は文字の位置を調べることができる関数です。

この関数を単体で使用することはなく、このあとにご紹介するLEFT関数、MID関数、RIGHT関数と組み合わせて使います。

関数で文字列を分割する場合、先ほどの「区切り位置」機能でいうところの「区切り文字」の場所を調べるために使うのがFIND関数です。

この関数がないと、文字列をどの位置で区切ったらよいか指定することができません。文字列分割の要となる関数と言えます。

活用例として、リストのメールアドレスの「@」の位置が何文字目にあるのか調べてみましょう。

1. STEP

   FIND関数を入力

   

   E3のセルに「=FIND(“@”,D3,1)」を入力し、Enterを押す

2. STEP

   「@」の位置がセルに表示される

   

   「@」は先頭から数えて5文字目なので、「5」が表示される

FIND関数について、詳しくは「エクセルでFIND関数の使い方を解説！」の記事をご覧ください。

関連

エクセルでFIND関数の使い方を解説！文字列から文字の位置を調べよう！

LEFT関数で左から文字列を分割

LEFT関数は文字列の先頭から指定された文字数分の文字列を表示させる関数です。

文字列を前半と後半に分割する際、前半部分を抽出するために使用します。

LEFT関数とFIND関数を組み合わせて、文字列の前半部分（氏名の苗字部分）を分割した活用例を見てみましょう。

1. STEP

   LEFT関数・FIND関数を入力

   

   C3のセルに「=LEFT(B3,FIND(“　”,B3))」を入力しEnterを押す

   この場合の「”　”」とは、スペースキーを押すことで出来た空白部分（苗字と名前の間にあるスペース）のことです。

2. STEP

   文字列の前半部分が分割された

   

   苗字が2文字か3文字かに関わらず、B3セルの先頭からスペースの位置までを抽出することができました。

   もしもスペースを含めるのがいやな場合は、「=LEFT(B3,FIND(“　”,B3)-1)」と入力し、スペースの1文字前の位置で分割するよう指定します。

LEFT関数の基本が知りたい方は、「LEFT関数とは？基本から応用まで紹介！」の記事をご覧ください!

関連

【エクセル】LEFT関数とは？基本から応用まで紹介！

MID関数で真ん中の文字列を分割

MID関数は指定した開始位置から、指定した文字数分の文字列を表示させる関数です。

さきほどのLEFT関数と同様に、MID関数とFIND関数などと組み合わせることで文字列を分割することができます。

MID関数については「MID関数の使い方は？くわしく解説！」の記事をご覧ください。

関連

【エクセル】MID関数の使い方は？くわしく解説！

RIGHT関数で右から文字列を分割

RIGHT関数は文字列の右側から指定された文字数分の文字列を表示させる関数です。

使い方は先ほどのLEFT関数と同じです。文字列を前半と後半に分割する際、後半部分を抽出するために使用します。

RIGHT関数とFIND関数を組み合わせて、氏名の名前の部分（文字列の後半）を分割した活用例を見てみましょう。

1. STEP

   RIGHT関数・FIND関数を入力

   

   D3セルに=RIGHT(B3,LEN(B3)-FIND(“　”,B3))と入力しEnterを押す

   文字数を数えるLEN関数については、「LEN関数は簡単に文字の数を表示できる優れもの！！」で詳しく解説しています。

2. STEP

   文字列の後半部分が分割された

   

   LEN関数でセル内の文字数を数え、FIND関数でスペースの位置を調べることによって、「セル内の文字数マイナス苗字とスペース分の文字数」すなわち名前の分の文字数を、RIGHT関数で右側から表示させています。

RIGHT関数の基本が知りたい方は、「RIGHT関数を使いこなす便利技！」の記事をご覧ください!

関連

【エクセル】RIGHT関数を使いこなす便利技！

エクセルの新関数「TEXTSPLIT関数」なら、横だけでなく上下の分割も簡単にできる

文字列の分割が横（列）だけではなくて縦（行）にもできる「TEXTSPLIT関数」。Microsoft365では2022年8月下旬に搭載されました。

今回は、横一列に並んだ「県知事名」の苗字と名前を縦に分割してみましょう。

1. STEP

   TEXTSPLIT関数を呼び出す

   

   1. 文字列を分割したいセルを選択
   2. TEXTSPLIT関数を入力し、関数ダイアログボックスを開く
2. STEP

   ダイアログボックスで引数を設定する

   

   1. TEXTに、分割するセルを入力
   2. Row_delimiterに区切り文字（今回は半角スペース）を入力
   3. OKをクリック

   Row_delimiter・・・縦に分割する場合に入力します
   Col_delimiter・・・横に分割する場合に入力します

3. STEP

   縦方向に分割できた

   

   これで、縦（行）方向に分割することができました。

   オートフィルで数式をコピーして完成です。

   

エクセルVBAで文字列を自由に分割

文字列の分割をエクセルVBAで行う場合、一度VBAを記述してしまえばあとは自動的に何回も繰り返し処理を行うことが出来るので、大量のリストを分割する際にとても便利です。

また、記述を少し変更するだけで、一度に大量のリストを分割することもできるし、入力の都度、分割することもできるので自由度が高いです。

エクセルVBAで文字列分割の自動化にチャレンジしてみよう!

Dr.オフィス

それでは、エクセルVBAを使って県庁所在地一覧の住所から番地以下を分割してみましょう。

大量のリストを一度に分割する場合のエクセルVBA

I列にある住所を分割し、J列に番地より前の住所を、K列に番地以下を、それぞれ表示させます。

分かりやすく解説するためにセルを隣接させていますが、分割したセルを離れた場所に表示させても大丈夫ですよ!

なお、この記事はダウンロード版のエクセルでの解説となります。

この場合の「番地」とは、半角数字（0,1,2,3…）で入力された文字のことを指します。
番地が漢数字（〇、一、二、三…）で入力されている場合や全角数字、また、北海道独特の住所表示である「条丁目」の場合には、意図する結果となりませんのでご注意ください!

1. STEP

   開発タブから「Visual Basic」を選択

   

   1. 「開発」タブを選択
   2. 「VisualBasic」をクリック

   初期設定で開発タブが表示されていない場合は、こちらの記事「エクセル【開発タブ】を表示する方法と使い方！」をご参照のうえ、開発タブを表示させてください。

2. STEP

   標準モジュールを挿入

   

   1. 「挿入」をクリック
   2. 「標準モジュール」をクリック

   標準モジュールとは、VBAを記述するためのファイルです。
   ここに書いたマクロは、すべてのワークシートから実行することができます。

3. STEP

   プロシージャを挿入

   

   1. 「挿入」をクリック
   2. 「プロシージャ」をクリック

   プロシージャとは、プログラムの一連の処理をひとつにまとめたものです。

4. STEP

   追加するプロシージャの設定をする

   

   1. プロシージャに名前を付ける（日本語でOK）
   2. 「Subプロシージャ」をクリック
   3. 「Publicプロシージャ」をクリック
   4. 「OK」をクリック

   

   標準モジュールに「分割」というタイトルのプロシージャが追加されました。

5. STEP

   文字列分割のためのVBAを記述

   

   あとは、以下のようにVBAを記述するだけです。

   このマクロは、「住所データの文字を1文字ずつチェックして、初めて数値が出てきた場所で文字列を分割する」という作業を、1行ずつ、最終行まで繰り返し行っています。

   一見難しそうですが、記述それぞれの日本語訳を見てみると、それほど難しいことはしていないことが分かります。

   自動的に処理をするための記述が2種類ある以外は、LEFTやMIDなど見覚えのある関数が使われているのでイメージが湧きやすいのではないでしょうか。

   Public Sub 分割()
   
   Dim n As Long
   Dim i As String
   Dim r As Integer
   
   r = 4
   
   Do Until Cells(r, "I").Value = "" 
   
   i = Cells(r, "I").Value
   
       For n = 1 To Len(i)
   
        If IsNumeric(Mid(i, n, 1)) Then 
   
           Cells(r, "J").Value = (Left(i, n - 1)) 
   
           Cells(r, "K").NumberFormatLocal = "@" 
           Cells(r, "K").Value = Mid(i, n, Len(i))
           Exit For
   
         End If
   
       Next n
   
   
   r = r + 1 
   
   Loop
   
   MsgBox ("処理が終わりました") 
   
   End Sub

6. STEP

   開発タブから「マクロ」を選択

   

   1. 「開発」タブを選択
   2. 「マクロ」をクリック

   文字列を分割するセル・分割した文字列を表示させるセルをVBAですでに指定しているので、アクティブセルはどこにあってもOKです

7. STEP

   マクロの実行

   

   1. 実行するマクロ名（STEP4でつけたプロシージャの名前）を選択
   2. 「実行」をクリック
8. STEP

   文字列が自動的に分割された

   

   あとは最終行まで自動的に文字列を分割してくれます。

このように、VBAを書くのは少しだけ面倒ですが、あとはマクロを実行するだけで自動的に文字列を分割してくれますし、他のエクセルシートにVBAをコピーして使いまわすこともできるので便利です。

文字列の分割を入力した都度に行うエクセルVBA

先ほどの「大量のリストを一度に分割する場合のエクセルVBA」を少しアレンジして、住所を入力した都度、文字列を分割するエクセルVBAをご紹介します。

1. STEP

   ワークシートを指定してVBAを記述

   

   1. マクロを実行するワークシートを限定させるため、リストが入力されているワークシートをクリック
   2. プロシージャ名を
      「Private Sub Worksheet_Change(ByVal Target As Range)」
      と入力（ワークシートの内容が変更したときに実行するマクロであることを指定しています）
   3. あとは以下のとおりVBAを記述するだけ
      最初の部分が少し変更されていますが、それ以外は先にご紹介したVBAと同じです。

   Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim n As Long
   Dim i As String
   Dim r As Integer
   
   r = Target.Row
   
   p = "$I$" & r
   
   If Target.Address = p Then
   
   i = Cells(r, "I").Value
   
      For n = 1 To Len(i)
         If IsNumeric(Mid(i, n, 1)) Then
   
           Cells(r, "J").Value = (Left(i, n - 1))
   
           Cells(r, "K").NumberFormatLocal = "@"
           Cells(r, "K").Value = Mid(i, n, Len(i))
   
           Exit For
   
         End If
   
       Next n
   
   End If
   
   End Sub

2. STEP

   住所を入力したタイミングで文字列が分割されることを確認

   

   

   一度VBAを記述しておけば、あとはI列（住所）を入力するだけで自動的に文字列を分割してくれるので、とても便利です。

エクセルVBAで文字列を分割する場合の注意点

文字列分割を自動的にできて、とても便利なエクセルVBAですが、注意点があります!

エクセルVBAが書かれたファイルは、「マクロ有効ブック」という種類となり、拡張子（ファイルの種類を表すもの）も普段のエクセルファイルとは異なります。

「マクロ有効ブック」として保存したあとに拡張子を修正してしまうと、せっかく記述したVBAが使えなくなってしまいます。

複数人で同じファイルを使用する場合は、念のため、ファイル名に「拡張子変更不可」などと追加しておくと安心です。

エクセルの文字列の分割に関するQ＆A

エクセルの文字列の分割方法はこんなに簡単！

エクセルで文字列の分割を行う方法は、このように何種類もあり、操作はどれも簡単です！

一見すると分割が難しそうなリストでも、置換などの機能を併せて使うことで手作業の手間を少なくした状態で分割することができます。今回の記事も参考にしながら工夫してみてくださいね。

最後におさらいです。

分割したい内容に応じて使い分けて、効率よくリストの整理を行いましょう!

また、リストは複数人で管理することもあるのではないでしょうか。

文字列分割の関数をリストに入れた後は、他の方に関数が消されてしまわないよう、エクセルのシートを保護しておくと安心です。

エクセルのシートを保護したり解除する方法を徹底解説！の記事を参考にして、是非お試しください!

関連

エクセルのシートを保護したり解除する方法を徹底解説！