楽天リーベイツで楽天ポイント還元最大20%!今なら登録して初回購入で500ポイントもGET!

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

13 min 758 views
エクセル初心者

エクセル初心者

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

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

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

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

Dr.オフィス

Dr.オフィス

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

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

エクセルの文字列を分割するポイント
  1. 「データ」タブ→「区切り位置」機能を使って簡単に分割
  2. LEFT・MID・LEN・FINDの関数で分割
    新関数「TEXTSPLIT」関数なら横だけでなく縦の分割も簡単にできる
  3. エクセルVBAなら分割するタイミングも自由

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

Dr.オフィス

Dr.オフィス

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

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

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

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

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

文字列分割サンプル
文字列分割サンプル

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

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

Dr.オフィス

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.オフィス

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」と、年月に見えなくもない紛らわしいものとなっています。

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

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

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

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

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

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

  1. STEP

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

    表示形式を文字列に変更
    表示形式を文字列に変更

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

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

    1行目は法則性を手入力

    1行目は手動で入力
    1行目は手動で入力

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

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

  3. STEP

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

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

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

    自動的に分割された
    自動的に分割された

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

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

    フラッシュフィル機能で自動的に分割
    フラッシュフィル機能で自動的に分割

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

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

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

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

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

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

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

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

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

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

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

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

  1. STEP

    FIND関数を入力

    FIND関数の挿入
    FIND関数の挿入

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

  2. STEP

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

    『@』は5文字目
    『@』は5文字目

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

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

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

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

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

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

  1. STEP

    LEFT関数・FIND関数を入力

    FIND関数も含めてみよう

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

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

  2. STEP

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

    2文字も3文字の名字も抽出完了
    2文字も3文字の名字も抽出完了

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

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

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

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

MID関数を入力

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

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

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

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

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

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

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

  1. STEP

    RIGHT関数・FIND関数を入力

    LEN関数とFIND関数も含めてみよう

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

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

  2. STEP

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

    名前の抽出
    名前の抽出

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

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

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

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

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

文字列を縦に分割
文字列を縦に分割
  1. STEP

    TEXTSPLIT関数を呼び出す

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

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

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

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

  3. STEP

    縦方向に分割できた

    縦方向に分割された
    縦方向に分割された

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

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

    コピーして完成
    コピーして完成

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

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

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

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

Dr.オフィス

Dr.オフィス

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

初めて数字が現れる位置で分割
初めて数字が現れる位置で分割

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

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

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

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

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

  1. STEP

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

    開発タブからVisualBasicEditorを起動
    開発タブからVisualBasicEditorを起動
    1. 「開発」タブを選択
    2. 「VisualBasic」をクリック

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

  2. STEP

    標準モジュールを挿入

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

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

  3. STEP

    プロシージャを挿入

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

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

  4. STEP

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

    プロシージャの設定
    プロシージャの設定
    1. プロシージャに名前を付ける(日本語でOK)
    2. 「Subプロシージャ」をクリック
    3. 「Publicプロシージャ」をクリック
    4. 「OK」をクリック
    VBAを書く準備が出来た
    VBAを書く準備が出来た

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

  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

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

    住所を入力する
    住所を入力する
    Enterで文字列分割
    Enterで文字列分割

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

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

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

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

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

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

マクロがあると拡張子が変わる
マクロがあると拡張子が変わる

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

Q

3文字しか入力されてない列を分割する方法はありますか?

A

LEFT関数で、文字数を指定して分割することができます。
また、すべて全角文字(またはすべて半角文字)で文字の横幅がすべて同じ場合は、「区切り位置」機能の「固定長フィールド」で分割する位置をマウスで指定することができます。

Q

関数を使わないで文字列の分割をセルの上下にすることはできますか?

A

関数を使う場合はTEXTSPLIT関数を使えば良いですが、関数を使わない場合は「区切り位置」機能を使って文字列を分割したあとにコピー→形式を選択して貼り付け→「行列を入れ替えて貼り付け」をすることでセルの上下に文字列を分割することができます。

Q

コンマを区切り文字にして「区切り位置」機能を使ったのに、文字列が分割されているセルと分割されていないセルがあります。どうしてですか?

A

区切り文字に半角と全角が混在していると、分割されるセルとされないセルが出てきてしまいます。
エクセルの「置換」機能を使って、全角コンマを半角に置き換えましょう!
もしくは、「区切り文字」を「コンマ」と「その他」両方にチェックを入れ、その他の欄に全角コンマを入力します。

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

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

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

最後におさらいです。

おさらい
  • 区切り位置機能を使って簡単に分割
    置換などの工夫で区切り文字なしでもOK
  • LEFT・MID・LEN・FINDの文字列操作関数で分割
    新関数「TEXTSPLIT」関数なら横だけでなく縦の分割もできる
  • エクセルVBAで自動化にチャレンジ!

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

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

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

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

カテゴリー:

40万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に40万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事