今日を乗り切るExcel研究所

Excel に働かされていませんか

テキストを1文字ずつにバラしたい

今回は、テキストデータの文字列から1文字ずつをマス目のセルに入力する方法について調査します。

ネ申エクセル

セルを文字マスとして使うというシートに遭遇することがあります。

入力欄が、セル幅を狭めて作った文字マスになっていて、名前や文章を1セル1文字ずつ入力しなければならないという、いわゆる「ネ申エクセル」の一種です。

お役所で用意された申請書などの Excel ファイルにありがちなのですが、本当に意味がわかりません。

入力する方も使う方も大変な思いをするだけなのに、何の嫌がらせでしょうか。

このような無意味で理不尽なネ申の試練に耐える必要はありません。

テキストを文字に分解するには

テキスト(文字列)を文字単位に分解する方法はないのでしょうか。

ネットで検索するといろいろありますが、おおかた MID 関数や SEQUENCE 関数など使った数式による方法が一般的なようです。

ただ、数式を考えるのはどうも頭を使います。 嫌がらせのために頭を使いたくはありません。

本記事では、数式を使わずに単純な手作業による方法を3つほど紹介します。

「文字の割付」を使う

一番お手軽そうなのは「文字の割付」機能を使う方法です。 名前や1行程度の文言なら、これで簡単に文字単位に分解できます。

文字の割付は、長いテキストをセル幅に収まる文字幅に分割して、複数行のセルに折り返す機能です。

f:id:shego:20210415101453p:plain

このとき、セル幅を1文字分も取れないくらい極端に狭めておくと、1行1文字に分解された割り付けになります。

f:id:shego:20210415103045p:plain

これを利用します。

【手順例】

  1. セルにテキストを入力し列幅を1文字の幅より小さく狭めておきます
    • 列幅を変更するにはシートの列見出しの境界線をドラックします
  2. 「文字の割付」を実行します
    • マウス操作:「ホーム」タブ ⇒「フィル」メニュー ⇒「文字の割付」
    • アクセスキー:Alt + HFIJ
      f:id:shego:20210416064334p:plain
  3. 警告ダイアログが表示されますが、「OK」ボタンを押します
  4. 1セル1文字で縦に割り付けられます
  5. 文字が割り付けられたセル範囲を選択し、コピーします
  6. f:id:shego:20210416071441p:plain「行と列の入れ替え」で任意のセルに張り付けます
    • マウス操作:右クリック ⇒ 「貼り付けのオプション」⇒ 「行/列の入れ替え」
    • アクセスキー: Alt + HVT
    • ショートカット: アプリケーションキー + TEnter

f:id:shego:20210501162415g:plain

この手順はお手軽ですが、以下のような欠点もありますので注意が必要です。

  • 英単語など半角の英数字の文字列を文字に分解でません
  • 一度に1セルのテキストしか処理できません

日時や住所などの半角の数字はバラバラにはならず、英文でも単語ごとに割り付けられてしまいます。 (これはこれで別に何か使いようがありそうですが)

全角の英数字なら問題ありません。

また、文字の割付は1セルずつしか処理できないので、住所のような複数行になるデータを一括変換することはできません。

ところで、逆に、バラバラの文字を連結して文字列に戻したいときがあるかもしれません。

その場合も、文字の割付が使えます。 文字を縦に並べ、セル幅を大きめに広げてから文字の割付を実行します。

f:id:shego:20210416062315p:plain

「フラッシュフィル」を使う

住所などのように、複数セルに分けられたテキストデータを文字ごとに分解したい場合、「フラッシュフィル」を応用する方法があります。

フラッシュフィルとは、セル範囲の行データからを値を自動抽出する機能で、Excel 2013 からサポートされています。

フラッシュフィルでは、1行分だけ「お手本」を示すことで、どのセルの何から抽出すべきかのパターンを Excel が自動で判断してくれます。

お手本で、何番目の文字を取り出せばいいのかを示せれば、残りのデータは Excel が自動でやってくれるはずです。

【手順例】

  1. お手本にする五十音の文字列を1行あたりに必要な文字数(列数)だけ用意します
    • たとえば1行最大 10 文字でいいなら「あいうえおかきくけこ」とします
  2. テキストデータの列にその文字列を2個入力します
    • 「あいうえおかきくけこ」なら「あいうえおかきくけこあいうえおかきくけこ」と入力します
    • 上でも下でもいいですが、元データのセル範囲に隣接している必要があります
  3. 同じ行の文字マスに「お手本」として分解した文字を入力します
    • 「文字の割付」を使いましょう
    • 右でも左でもいいですが、お手本は元データのセル範囲に隣接している必要があります
  4. 分解文字の展開先となるセル範囲の「表示形式」を「文字列」に設定します
    • 「文字列」以外のままだと、フラシュフィルでエラーになることがあります
    • お手本も含めて設定します。そうしないとエラーになることがあります
  5. データ行の先頭行の各文字マスで、1列ずつフラッシュフィルを実行します
    • ショートカット:Ctrl + E

f:id:shego:20210501172559g:plain

この方法なら、半角の英数文字であってもちゃんと文字単位に分割することができます。

もし Ctrl + E でエラーになったら、そのセルは手入力したうえで、一つ下のセルで再度 Ctrl + E してみてください。

注意点として、

  • 絵文字は壊れます
  • 旧字や異体字など難しい一部の漢字に壊れるものがあります

「😀」のような絵文字と、「𠮷(つちよし)」のような一部の漢字のなかにはフラッシュフィルから見ると2文字に見えるものがあって、別々の文字として分解されて結果的に2つのセルにゴミデータが入ります。

お役所の書類に絵文字を使うことはさすがにないと思いますが、人名や地名に旧字や異体字の方が使われる可能性は無きにしも非ずなので注意する必要があります。 そのような文字が含まれていた場合は、いったん別の通常文字に置換してから文字分解し、展開後に再度置換して戻すなどします。

またそうでなくても Excel がお手本を誤解する可能性もゼロではないので、結果に間違いがないか必ずよく確認してください。

さて、この方法でもサイズ的には 100 文字程度のテキストデータが限界でしょう。 文章テキストのような長い文字列を分解するには作業的に無理がありますので、その場合次の三つめの方法を検討します。

ちなみに、このフラッシュフィルでも逆に文字を連結をして文字列を作成することができます。 ただしスペース(空白文字)が削除されてしまうので、英文テキストなどでは注意が必要です。

 

 



 

テキストエディタの置換機能を使う

もし、ご使用のテキストエディタが正規表現による置換をサポートしていたら、長い文章でも文字に分解するのに利用できます。

「正規表現」とは、文字の並びをパターンで表現するための特別な式です。

正規表現を使ってテキストを文字単位の TAB 区切りテキストに変換すれば、そのままワークシートにコピー&ペーストするだけで1セル1文字にできます。

文字列を文字単位の TAB 区切りに変換する一番簡単なパターンは以下のようになります。

  • 検索:()
  • 置換:\t

テキストエディタの置換ダイアログで「検索」入力には半角の空括弧「()」を入力します。これは、文字境界を表す正規表現パターンです。 「置換」入力には半角で「\t」と入力します。円マーク「¥」と英小文字の「t」で、TAB 文字を表します。 (画面では「¥」がバックスラッシュ「\」で表示されているかもしれません。)

そんなに難しくはないですね、やってみましょう。

とその前に、ご使用のテキストエディタが正規表現による置換が使えるのか確認してください。

Windows 標準のテキストエディタである メモ帳 は正規表現をサポートしていません。 情シスがPCにインストールしてくれていそうなエディタでいうと、秀丸サクラエディタEmEditor が正規表現をサポートしています。 残念ながら TeraPad はサポートしていません。

【手順例】

  1. テキストエディタを開き対象テキストをコピー&ペーストします
  2. 「置換」ダイアログを開きます
  3. 置換ダイアログのオプションで「正規表現」をチェックします
  4. 「検索」と「置換」の入力欄に以下のパターン文字列を入力します(コピペしてください)
    • 「検索」入力:()
    • 「置換」入力:\t
  5. 「全て置換」を実行します
    • 全ての文字が TAB 区切りになります
  6. タブ区切りになったテキスト全体をコピー(Ctrl+ACtrl+C)します
  7. Excel ワークシートに貼り付け(Ctrl+P)します
    • 1セル1文字で貼り付けられます

f:id:shego:20210426125353p:plain

注意点としては、単純に張り付けしただけでは1列ズレて入力されることです。 列位置を合わせるには入力したいセルの1列左の位置から貼り付けします。 それがシートレイアウト的に無理なら、いったん別シートに張り付けして、そこから正しい範囲を再度コピー&ペーストするなどします。

1列ズレてしまう原因は、行頭と行末にも TAB が挿入されてしまっているからです。

もし必要なら、そうはならない正規表現パターンというのも考えられるのですが、ちょっと複雑になります。

以下は、行頭や行末の余分な TAB なしで文字分解するパターンの例です。

  • 検索:(?<=.)(?=.)
  • 置換:\t

こんな記号の羅列は覚えていられませんね。 コピペして使ってください。

拡張文字

さらにもう一つの注意点として、正規表現を使った場合も、絵文字や一部の漢字が壊れる可能性があるという問題があります。

テキストエディタによっては、「😀」や「𠮷(つちよし)」のような拡張文字が2文字として扱われてしまうからです。

秀丸やサクラエディタでは問題ありませんが、 EmEditor の正規表現では壊されます。

EmEditor でこれらの文字を壊さず文字分解し行頭行末 TAB も付けないという正規表現パターンを考えると以下のようになります。

  • 検索:(?!^|$|[\x{DC00}-\x{DFFF}])
  • 置換:\t

だいぶ複雑になりました。

家族文字

それでも完全ではありません。

テキストデータの各文字への分解というのは単純そうで実はけっこうややこしい問題なのです。

ここまで紹介した正規表現で実用上、少なくとも Excel で扱うデータ上でははほとんど問題ないはずですが、 理屈で言えば他にも考慮すべき状況がいろいろあり得ます。

例えば絵文字のいわゆる家族文字「👨‍👨‍👧‍👦」などは、1文字に見えて実際には複数の文字コードを組み合わせた合成文字です。 上記正規表現パターンを使うとそれを判定できずに家族がバラバラにされてしまいます。

まあそれでも実用上大した問題にはならないとは思うのですが、どうしても一家離散を防ぎたいというのなら、以下のようなパターンが考えられます。

  • 秀丸
    • 検索:(?<=.)(?<![\u200D])(?=.)(?![\u200D])
    • 置換:\t
  • サクラエディタ
    • 検索:(?<!^|\r|\n|[\x{200D}])(?!$|\r|\n|[\x{200D}])
    • 置換:\t
  • EmEditor Free
    • 検索:(?<![\x{200D}])(?!^|$|[\x{DC00}-\x{DFFF}\x{200D}])
    • 置換:\t

もう何が何だかわかりませんね。

しかもテキストエディタによってパターンが異なっています。

正規表現には、テキストエディタの実装による微妙な解釈の違いや機能のサポート状況によって「方言」があり、複雑なパターンほど違いが現れます。

上記以外のテキストエディタではまた違うでしょう。 本ブログではそこまで面倒を見切れませんので、もし必要なら、情シスにいる同期やプログラマをやっている弟さんに頼んでみてください。

上記正規表現の検証は以下のバージョンのエディタで行いました。バージョンによっても違いがあるかもしれません。

  • 秀丸 Version 8.97 32bit edition / HMJRE.DLL V5.10
  • サクラエディタ v2.4.1.2849 32bit (tag v2.4.1) / bregonig.dll Ver.4.20 with Onigmo 6.2.0
  • EmEditor Free (64-bit) Version 19.7.0 / Regex++ Boost 1.66.0

 



テキストの文字をマス目に入力するマクロ

テキストを分解して1セルに1文字ずつ入力するマクロを作成しました、

本マクロは、クリップボードにコピーされているテキストデータを文字単位に分解し、選択されたセル範囲のセルに1文字ずつ貼り付けするものです。

Option Explicit

Sub 貼り付け_テキストを1文字ずつに分解()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    
    Dim rng As Range
    Dim lineCount As Long
    Dim colCount As Long
    Set rng = Selection.Cells
    lineCount = rng.Rows.Count
    colCount = rng.Columns.Count
        
    Dim txt As String
    Dim buff() As Variant
    Dim lines As Variant
    Dim chars As Variant
    
    txt = getClipboardText
    If txt = "" Then Beep: Exit Sub

    lines = splitToLines(txt, colCount)
    
    lineCount = minimum(lineCount, UBound(lines) + 1)
    ReDim buff(lineCount - 1)
    
    Dim i As Long
    For i = 0 To lineCount - 1
        chars = splitToChars(lines(i))
        ReDim Preserve chars(colCount)
        buff(i) = chars
    Next
    buff = convArrayFromJaggedTo2D(buff)
    
    Application.ScreenUpdating = False
    rng.ClearContents
    rng.NumberFormatLocal = "@"
    rng.Resize(lineCount).Value = buff
    Application.ScreenUpdating = True
End Sub

Private Function getClipboardText() As String
    Dim cf As Variant
    For Each cf In Application.ClipboardFormats
        If cf = xlClipboardFormatText Then
            With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
                .GetFromClipboard
                getClipboardText = .GetText
            End With
            Exit Function
        End If
    Next
End Function

Private Function splitToLines(ByVal txt As String, Optional wrapSize As Long = 0) As String()
    With CreateObject("VBScript.RegExp")
        .Global = True
        If 0 < wrapSize Then
            .Pattern = "((.[\uDC00-\uDFFF]?){1," & wrapSize & "})(\r?\n)?"
            txt = Mid(.Replace(txt, vbLf & "$1"), 2)
        End If
        .Pattern = "\r?\n"
        splitToLines = Split(.Replace(txt, vbNullChar), vbNullChar)
    End With
End Function

Private Function splitToChars(ByVal txt As String) As String()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(?!^|$)(?![\uDC00-\uDFFF])"
        splitToChars = Split(.Replace(txt, vbNullChar), vbNullChar)
    End With
End Function

Private Function convArrayFromJaggedTo2D(jagged() As Variant) As Variant()
    convArrayFromJaggedTo2D = WorksheetFunction.Transpose(WorksheetFunction.Transpose(jagged))
End Function

Private Function minimum(a As Variant, b As Variant)
    minimum = IIf(a <= b, a, b)
End Function

【使い方】

  1. 標準モジュールに上記 VBA マクロをコピー&ペーストします
  2. 文字に分解したいテキストデータをクリップボードにコピーします
  3. 文字を入力したいセル範囲を選択します
  4. 本マクロを実行します
  5. 選択範囲の1セルに1文字ずつ貼り付けされます

文章のような長いテキストは、原稿用紙のように選択範囲の列数で折り返されます。

絵文字「😀」や漢字拡張文字「𠮷(つちよし)」などは壊れずに、1文字として扱われます。

家族文字「👨‍👨‍👧‍👦」などの合成文字はバラバラになります。

テキストのセルコピーして「マクロの表示」からすると実行セルの選択が解除されてうまくいきません。 セルを入力モードにして中のテキストのみをコピーしてください。 あるいはマクロをボタンやショートカットに割り付けたり、VBEから直接実行すればセルコピーでも問題ありません。

まとめ

今回、テキストを1文字ずつセルに入力する操作手順をいくつか紹介しました。

文字の割付を使うと頭を使わずに手作業のみで文字分解でき、名称のような数文字程度のテキストの処理に向いています。

フラッシュフィルを使う方法はもう少し手間になりますが、複数セルに渡るテキストデータを一括で処理できます。

概要や説明の文章など長めのテキストは、正規表現で文字の TAB 区切りに変換してしまう方法が使えます。 文字で分割する程度なら簡単なパターン()だけ覚えれば実用上問題ないはずです。

これで、理不尽な作業による生産性の低下と精神的苦痛をある程度防ぐことができるでしょう。

そもそも、ネ申エクセルがなくなればこんな苦労はしなくてよかったのです。

すこし前に河野太郎氏がこれを問題視されたのが話題になっていました。

ネ申エクセルのもたらす害悪はお役所でも認識されているようです。 今はもう廃絶されているのかもしれません。

本記事の内容は Excel 2013 と Office 365 版 Excel で検証しました。

参考文献