excelでの改行制御

質問

例えば、123456-789abcdefg
をセルに入力し、「折り返して表示」の状態で、セル幅が例えば9とaの間で切れるくらいの幅である場合でも、"-"のところで改行され、

123456-
789abcdefg

と表示されます。

これは、"-"が英文における単語の改行時のつなぎ文字であることから、自動的に"-"があるところで改行表示される設定になっているからだと思うのですが、

これをあえて、列幅どおりに

123456-789
abcdefg

と表示させるようにすることはできないものでしょうか。

ALT+ENTERを使えばできるのですが、このようなデータを大量に入力し、かつ、セル幅が定まっていない場合、セルごとに改行位置を修正していくのは避けたいので、ALT+ENTERは使わないで表示させる方法があれば教えてください。

回答No.2

nishi6 · ベストアンサー

Sheet1で行う例です。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、 表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示します。
プロジェクトエクスプローラのSheet1をダブルクリック。出てきたコードウインドウに下記マクロを貼り付けます。

マクロを貼り付けた後は、普通に入力するだけです。
今の設定は、Ｄ列がURLの入力列で、文字幅を20文字に揃えるようにしています。好きなように変えてください。
機能としては、指定した桁数で改行コードを挿入するので指定通りに改行されます。ただし、セル幅がある場合です。
また、この機能を使っていると、Alt+Enterが意味をなさないのでこれを無効にしています。

一旦、桁数nで設定して入力したあと、桁数n+αに変更する場合は『Const 桁数 =』の値を変えて、
該当列を他の場所に貼り付け、コピーしなおすと、新しい設定の桁数になるはずです。

ご参考に。

Const urlColumn = "D" 'URLを入力する列
Const 桁数 = 20 'URLを入力する列の桁数

Private Sub Worksheet_Change(ByVal Target As Range)
　　On Error GoTo ErrorHandler

　　'入力が指定した列なら書き換える
　　If Target.Column = Range(urlColumn & "1").Column Then
　　　　Dim srcText As String '元の入力
　　　　Dim Orikaeshi As String '強制的な折り返し
　　　　Dim L As Integer '文字カウンタ

　　　　'Alt+Enterを無効にする
　　　　srcText = Application.Substitute(Target.Text, vbLf, "")
　　　　'指定桁で折り返す
　　　　Orikaeshi = Left(srcText, 桁数)
　　　　For L = 桁数 + 1 To Len(srcText) Step 桁数
　　　　　　Orikaeshi = Orikaeshi & vbLf & Mid(srcText, L, 桁数)
　　　　Next
　　　　'書き直したテキスト
　　　　Application.EnableEvents = False
　　　　Target = Orikaeshi
　　　　Application.EnableEvents = True
　　End If

　　Exit Sub

ErrorHandler:
　　Application.EnableEvents = True
End Sub

お礼

返事が大変おそくなりました。

入力したいデータがe-mailアドレスやURLなのでalt+enterやラインフィード文字などを挿入して改行位置を設定するのは、その後の用途に多少の支障をきたすのでできれば避けたいと思っていました。エクセルの設定のどこかにいじるところはないのかなと思って質問しました。

vbLfのような定数を使うところは大変勉強になりました。
ありがとうございました。

回答No.1

imogasi

理屈とかは判りませんが下記はどうでしょうか。余分な列を１列使う欠点はありますが。
Ａ列にコードらしき１２３４５６－７８９abcdeの類が入っているものとします。Ｂ列が空いている列として、セルＢ１に
=mid(a1,1,9) & " " & mid(a1,10,5)
と入れ1スペースを入れる。そしてＢ列の式を複写する。
Ｂ列の書式を「おり返して表示」に設定する。
すると１スペースを入れたところでおりかえしますね。
Ａ列にセットしたいときは、一旦Ａ－＞Ｘ列に複写し、Ａ１に
=mid(ｘ1,1,9) & " " & mid(ｘ1,10,5)とすれば良いし、１スペースを取り除き、元に戻す時は、=mid(a1,1,9) & mid(a1,1１,5)を入れることで戻ります。

お礼

返事が大変おそくなりました。

入力したいデータがe-mailアドレスやURLなのでalt+enterやラインフィード文字などを挿入して改行位置を設定するのは、その後の用途に多少の支障をきたすのでできれば避けたいと思っていました。エクセルの設定のどこかにいじるところはないのかなと思って質問しました。

でも、スペースでも改行するなんて知らなかったです。
ありがとうございました。