DTP作業者にとっての難易度 ★☆☆☆☆（1）
※2016.7.11にサンプルデータを追加しました（改行コードを〓に置換するマクロを作成）。
※2017.6.15にサンプルデータを更新しました（改行コードを〓に置換するマクロを改良）。
InDesignのデータ結合機能を使用する際、Excelデータにセル内改行が残っていると不具合が生じます。
しかしMac版のExcelでは、通常の検索／置換機能を使ってセル内改行を他の文字（たとえば「〓」）に置換したり削除したりすることはできません。
そこで、これまで私は「LibreOffice」というソフトを使ってセル内改行を〓などに置換してきました。
※LibreOfficeを使う方法については当ブログの「定形フォーマットが並ぶレイアウトを作成する（データ結合）」でご紹介しています。
ただ、環境によってはMac版Excelだけで完結する必要もあるだろうと思い、今回はその方法を考えることにしました。
手順としては、
●データを入力したシートとは別に空白シートを用意する（同ブック内）。
●空白シートのA1セルに「=SUBSTITUTE(Sheet1!A1,CHAR(13),"〓")」と入力する。
●空白シートのA1セルをコピーし、空白シートの他セルにペーストする。
というものです。では動画をご覧ください。
※SUBSTITUTE関数は、あるセル内の特定の文字を別の文字に置き換える関数です。
※「Sheet1!A1」は「Sheet1!」というシートの「A1」セルを示しています（シート名が違う場合はこの部分を書き換えてください）。
※「CHAR(13)」はセル内改行のことです。
※「〓」は置換文字です。必要に応じて書き換えてください。
今回の方法については、以下のサイトを参考にしました。
http://web-memo.co/mac/excel/excel-mac-br-replace/
サンプルデータはこちら《minimum_2015_0930.zip》です。
【2015.9.30追記】
※どうやらCHAR(13)はCRで、CHAR(10)はLFのようです。念のため両方とも対応するよう「=SUBSTITUTE(SUBSTITUTE(Sheet1!A1,CHAR(13),"〓"),CHAR(10),"〓")」とし、データを更新しました。
2015.9.30更新サンプルデータはこちら《minimum_2015_0930b.zip》です。
【2016.7.11追記】
※改行コードを検索し、〓に置換するマクロを作成しました。
2016.7.11追加サンプルデータはこちら《minimum20160711.zip》です。
【2017.6.16追記】
※改行コードを〓に置換するExcelのマクロを更新しました。
2016.7.11追加サンプルデータはこちら《minimum20170615.zip》です。
過去にDTP駆け込み寺さんと、InDesignの勉強部屋さんの掲示板にSUBSTITUTE関数を使った計算式をコメントしてまして懐かしく思い書き込ませて頂きました。
Mac判エクセルのセル内改行の検索置換の問題は「もういいかげん、Windows判みたいに置き換えさせてよ！」って感じですが、Excel X、2008、2011とダメなんで、一般的にはあまり必要ないんでしょうね。
ただ、関数式を通すと値に表示形式が適用されず、数値で小数点以下の桁揃えしてある最後の「0」が無くなってしまい、例えば1.50は1.5。また、負の数の表示形式を使った(1.50)は-1.5となってしまいます。
現在はこれを逃れるために、データが数値と文字列の判定をして、数値ならTEXT関数で表示形式をセットし、文字列ならSUBSTITUTE関数で置き換えてます。
=IF(ISNUMBER(Sheet1!A1),TEXT(Sheet1!A1,"###.00;(###.00)"),SUBSTITUTE(Sheet1!A1,CHAR(13),"@"))
長くなってしまい、すみませんでした。
コメントをありがとうございます。
元が「数値」の場合、こういった方法が有効なんですね。
Excelは、あまり使うことがないのでまだまだ勉強不足です。
これからもよろしくお願いします。
感謝感謝です。ありがとうございます。
ちなみに、手持ちのデータで検証していて、「LFでもCRでもないが、セル内改行に見えるもの」がありました。
「垂直タブ」というもので、CHAR(11) です。
「垂直タブ」がどのようなものでどう使用するのかは存じません。
ただ、もしかしたらこれもプログラムに追加したほうがよいかもと思い、情報提供いたします。
コメントをありがとうございます。
「垂直タブ」については全く知らなかったので試してみました。
PowerPointでshift＋enterにより入力される制御文字のようですね。
当方の環境で、そのテキストをExcel（Windows10＋Office365）にペーストすると
セル内改行されず制御文字らしきものが表示されました。
MacOS11.7＋Office365でもセル内改行されず制御文字は表示されませんでした。
（見えないだけで制御文字は存在するためマクロで置換できるようです）
うめざわ様の環境ではExcel上で改行表示されるようですが、
当方と何か設定のようなものが違うのでしょうか。
Excelにつきましてはあまりよく知らないのですが
もしご存知のことがありましたらお知らせいただけますと助かります。
マクロでの処理としては
①制御文字を削除する
②制御文字を（通常の改行と同様）〓に置換する
③制御文字を♪など他とは異なる文字に置換する
④置換しない
が考えられますが、Excel上での見え方に合わせたいと思っております。
レスが遅くなり大変失礼しました。
> うめざわ様の環境ではExcel上で改行表示されるようですが、
> 当方と何か設定のようなものが違うのでしょうか。
いえ、表示はされないです。
「セル内改行に見えるもの」でなく「セル内改行のようなもの」ですね。誤解を与える書き方をしてすみません。
CRとLFと同様に処理すればよいのかなと、【2015.9.30追記】を参考にしつつ、下記の関数で試したところ、無事ゲタに置き換えができました。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!A1,CHAR(13),"〓"),CHAR(11),"〓"),CHAR(10),"〓”)
見様見真似でいれたので、この表記が正しいかどうかわかりません。
ご指摘をいただけるとありがたいです。
コメントをありがとうございます。
関数で処理するのであれば、お書き頂いた内容で良いと思います。
目的に応じてそれぞれの記号を使い分けることも考えられますね。
マクロで処理するのであれば従来のものに
shA.Cells.Replace vbVerticalTab, "〓"
を加えると良いと思います。
勉強になりました。
ありがとうございました。