今回は、テキストデータの文字列から１文字ずつをマス目のセルに入力する方法について調査します。
ネ申エクセル
セルを文字マスとして使うというシートに遭遇することがあります。
入力欄が、セル幅を狭めて作った文字マスになっていて、名前や文章を1セル1文字ずつ入力しなければならないという、いわゆる「ネ申エクセル」の一種です。
お役所で用意された申請書などの Excel ファイルにありがちなのですが、本当に意味がわかりません。
入力する方も使う方も大変な思いをするだけなのに、何の嫌がらせでしょうか。
このような無意味で理不尽なネ申の試練に耐える必要はありません。
テキストを文字に分解するには
本記事では、テキストを文字単位に分解するお手軽な手順をいくつか紹介します。
「文字の割付」を使う
一番お手軽そうなのは「文字の割付」機能を使う方法です。 名前や１行程度の文言なら、これで簡単に文字単位に分解できます。
文字の割付は、長いテキストをセル幅に収まる文字幅に分割して、複数行のセルに折り返す機能です。
このとき、セル幅を１文字分も取れないくらい極端に狭めておくと、１行１文字に分解された割り付けになります。
これを利用します。
【手順例】
- セルにテキストを入力し列幅を１文字の幅より小さく狭めておきます
- 列幅を変更するにはシートの列見出しの境界線をドラックします
- 「文字の割付」を実行します
- マウス操作：「ホーム」タブ ⇒「フィル」メニュー ⇒「文字の割付」
- アクセスキー：
Alt + H⇒FI⇒J
- 警告ダイアログが表示されますが、「OK」ボタンを押します
- １セル１文字で縦に割り付けられます
- 文字が割り付けられたセル範囲を選択し、コピーします
「行と列の入れ替え」で任意のセルに張り付けます
- マウス操作：右クリック ⇒ 「貼り付けのオプション」⇒ 「行/列の入れ替え」
- アクセスキー：
Alt+H⇒V⇒T - ショートカット： アプリケーションキー +
T⇒Enter
この手順はお手軽ですが、以下のような欠点もありますので注意が必要です。
- 英単語など半角の英数字の文字列を文字に分解でません
- 一度に1セルのテキストしか処理できません
日時や住所などの半角の数字はバラバラにはならず、英文でも単語ごとに割り付けられてしまいます。 （これはこれで別に何か使いようがありそうですが）
全角の英数字なら問題ありません。
また、文字の割付は１セルずつしか処理できないので、住所のような複数行になるデータを一括変換することはできません。
ところで、逆に、バラバラの文字を連結して文字列に戻したいときがあるかもしれません。
その場合も、文字の割付が使えます。 文字を縦に並べ、セル幅を大きめに広げてから文字の割付を実行します。
さて一個程度の短いテキストなら「文字の割付」はお手軽で簡単ですが、長い文章や、 住所などのように複数セルに分けられたテキストデータを処理するとなるとちょっと面倒です。
動的配列数式を使う
もし最新の Excel（ Microsoft 365 か Excel 2021）を使っているなら、動的配列数式で自動で文字をスピルさせることができます。
たとえば A1 セルにテキストデータがあるとすると、以下のような数式を使えば文字単位でスピルできます。
=MID(A1, SEQUENCE(,LEN(A1)),1)
これなら、半角英数字でも分解できますし、複数セルでも数式のコピーすれば一括で処理できます。
ここで、SEQUENCE関数は連番を動的配列として返す関数です。文字列の長さ分の連番を生成し、MID 関数に与えて文字位置と指定します。MID 関数のように通常の（配列でない）値を受け取る関数に配列を渡すと、結果も配列内の各値を個別に適用した配列となることを利用しています。
よく知られている通常の数式を使う方法にくらべ、動的配列数式を使う利点は、テキストが変更されれば長さも自動でスピル範囲が調整されるなど、展開されるセルの長さを気にする必要がない点です。
ただし、動的配列数式は最新の Excel でしかサポートされた機能で、Excel 2016 までの古いバージョンでは残念ながら利用できません。
「フラッシュフィル」を使う
古い Excel でも、住所などのように複数セルにあるテキストデータを一括で文字分解したい場合、「フラッシュフィル」を応用するという方法があります。
フラッシュフィルとは、セル範囲の行データからを値を自動抽出する機能で、これなら Excel 2013 からサポートされています。
フラッシュフィルでは、１行分だけお手本を示すことで、どのセルの何から抽出すべきかのパターンを Excel が自動で判断してくれます。
お手本で、何番目の文字を取り出せばいいのかを示せれば、残りのデータは Excel が自動でやってくれるはずです。
【手順例】
- お手本にする五十音の文字列を１行あたりに必要な文字数（列数）だけ用意します
- たとえば１行最大 10 文字でいいなら「あいうえおかきくけこ」とします
- テキストデータの列にその文字列を２個入力します
- 「あいうえおかきくけこ」なら「あいうえおかきくけこあいうえおかきくけこ」と入力します
- 上でも下でもいいですが、元データのセル範囲に隣接している必要があります
- 同じ行の文字マスに「お手本」として分解した文字を入力します
- 「文字の割付」を使いましょう
- 右でも左でもいいですが、お手本は元データのセル範囲に隣接している必要があります
- 分解文字の展開先となるセル範囲の「表示形式」を「文字列」に設定します
- 「文字列」以外のままだと、フラシュフィルでエラーになることがあります
- お手本も含めて設定します。そうしないとエラーになることがあります
- データ行の先頭行の各文字マスで、１列ずつフラッシュフィルを実行します
- ショートカット：
Ctrl+E
- ショートカット：
この方法なら、半角の英数文字であってもちゃんと文字単位に分割することができます。
もし Ctrl + E でエラーになったら、そのセルは手入力したうえで、一つ下のセルで再度 Ctrl + E してみてください。
注意点として、
- 絵文字は壊れます
- 旧字や異体字など難しい一部の漢字に壊れるものがあります
「😀」のような絵文字と、「𠮷（つちよし）」のような一部の漢字のなかにはフラッシュフィルから見ると2文字に見えるものがあって、別々の文字として分解されて結果的に２つのセルにゴミデータが入ります。
お役所の書類に絵文字を使うことはさすがにないと思いますが、人名や地名に旧字や異体字の方が使われる可能性は無きにしも非ずなので注意する必要があります。 そのような文字が含まれていた場合は、いったん別の通常文字に置換してから文字分解し、展開後に再度置換して戻すなどします。
またそうでなくても Excel がお手本を誤解する可能性もゼロではないので、結果に間違いがないか必ずよく確認してください。
さて、この方法でもサイズ的には 100 文字程度のテキストデータが限界でしょう。 文章テキストのような長い文字列を分解するには作業的に無理がありますので、その場合次の三つめの方法を検討します。
ちなみに、このフラッシュフィルでも逆に文字を連結をして文字列を作成することができます。 ただしスペース（空白文字）が削除されてしまうので、英文テキストなどでは注意が必要です。
テキストエディタの置換機能を使う
もし、ご使用のテキストエディタが正規表現による置換をサポートしていたら、長い文章でも文字に分解するのに利用できます。
「正規表現」とは、文字の並びをパターンで表現するための特別な式です。
正規表現を使ってテキストを文字単位の TAB 区切りテキストに変換すれば、そのままワークシートにコピー＆ペーストするだけで1セル1文字にできます。
文字列を文字単位の TAB 区切りに変換する一番簡単なパターンは以下のようになります。
- 検索：
() - 置換：
\t
テキストエディタの置換ダイアログで「検索」入力には半角の空括弧「()」を入力します。これは、文字境界を表す正規表現パターンです。
「置換」入力には半角で「\t」と入力します。円マーク「¥」と英小文字の「t」で、TAB 文字を表します。
（画面では「¥」がバックスラッシュ「＼」で表示されているかもしれません。）
そんなに難しくはないですね、やってみましょう。
とその前に、ご使用のテキストエディタが正規表現による置換が使えるのか確認してください。
Windows 標準のテキストエディタである メモ帳 は正規表現をサポートしていません。 情シスがPCにインストールしてくれていそうなエディタでいうと、秀丸、サクラエディタ、EmEditor が正規表現をサポートしています。 残念ながら TeraPad はサポートしていません。
【手順例】
- テキストエディタを開き対象テキストをコピー＆ペーストします
- 「置換」ダイアログを開きます
- 置換ダイアログのオプションで「正規表現」をチェックします
- 「検索」と「置換」の入力欄に以下のパターン文字列を入力します（コピペしてください）
- 「検索」入力：
() - 「置換」入力：
\t
- 「検索」入力：
- 「全て置換」を実行します
- 全ての文字が TAB 区切りになります
- タブ区切りになったテキスト全体をコピー（
Ctrl+A⇒Ctrl+C）します - Excel ワークシートに貼り付け（
Ctrl+P）します- 1セル1文字で貼り付けられます
注意点としては、単純に張り付けしただけでは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
テキストの文字をマス目に入力するマクロ
テキストを分解して１セルに１文字ずつ入力するマクロを作成しました、
本マクロは、クリップボードにコピーされているテキストデータを文字単位に分解し、選択されたセル範囲のセルに１文字ずつ貼り付けするものです。
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
【使い方】
- 標準モジュールに上記 VBA マクロをコピー＆ペーストします
- 文字に分解したいテキストデータをクリップボードにコピーします
- 文字を入力したいセル範囲を選択します
- 本マクロを実行します
- 選択範囲の１セルに１文字ずつ貼り付けされます
文章のような長いテキストは、原稿用紙のように選択範囲の列数で折り返されます。
絵文字「😀」や漢字拡張文字「𠮷（つちよし）」などは壊れずに、1文字として扱われます。
家族文字「👨👨👧👦」などの合成文字はバラバラになります。
テキストのセルコピーして「マクロの表示」から実行するとセルの選択が解除されてうまく行かないようです。 セル範囲ではなく、入力モードにして中のテキストのみをコピーしてください。 あるいはマクロをボタンやショートカットに割り付けたり、VBEから直接実行すればセルコピーでも問題ありません。
まとめ
今回、テキストを１文字ずつセルに入力する操作手順をいくつか紹介しました。
「文字の割付」を使うと頭を使わずに手作業のみで文字分解でき、氏名のような数文字程度のテキストの処理に向いています。
動的配列数式を使うやり方も簡単ですが、最新のExcelでないと使えません。
フラッシュフィルを使う方法はもう少し手間になりますが、複数セルに渡るテキストデータを一括で処理できます。
概要や説明の文章など長めのテキストは、正規表現で文字の TAB 区切りに変換してしまう方法が使えます。
文字で分割する程度なら簡単なパターン()だけ覚えれば実用上問題ないはずです。
これで、理不尽な作業による生産性の低下と精神的苦痛をある程度防ぐことができるでしょう。
そもそも、ネ申エクセルがなくなればこんな苦労はしなくてよかったのです。
すこし前に河野太郎氏がこれを問題視されたのが話題になっていました。
行革推進本部で文科省をよんで、こういう神エクセルを至急、全廃することにしました。また、科研費関係の問題提起の窓口をつくり、順次、対応することにしました。 https://t.co/g497j0ZQgu
— 河野太郎 (@konotarogomame) 2016年11月2日
ネ申エクセルのもたらす害悪はお役所でも認識されているようです。 今はもう廃絶されているのかもしれません。
本記事の内容は Excel 2013 と Office 365 版 Excel で検証しました。
