Excel 文字数に制限あり？？

質問

Excel 「CONCATENATE」文字数に制限あり？？

「B列からF列」までに入力された数字・文字・数式を、同じシート内の「H列」にて1行に表示させるため数式を入れていますが、文字数が255文字を（スペース含む）超えると【#VALUE!】（値のエラー）が出ます。

このエラーを回避し、正しく表示させる方法はありますでしょうか？

========================================================
＊B列、D列、F列
　数字、文字など、直接入力されています（空のセルあり）

＊C列、E列（参照先のセルが空白の場合あり）
　=IF(ISERROR(INDIRECT("'シート名'!D2")),"",INDIRECT("'シート名'!D2"))

＊H列
　=CONCATENATE(TEXT(B2,";;;@"),TEXT(C2,";;;@"),TEXT(D2,";;;@"),TEXT(E2,";;;@"),TEXT(F2,";;;@"))
========================================================

分かりにくい説明ですみません。
よろしくお願いいたします。

環境 Win XP /Excel 2003

回答No.6

MackyNo1 · ベストアンサー

＞調子に乗って追加で質問なのですが、この方法だと文字数制限はどれくらいなんでしょうか？

エクセルのヘルプで「仕様」と入力してワークシートとブックの仕様を調べてみてください。

セルの内容の長さ (文字列)は 32,767 文字。
ただし、セルに表示できるのは 1,024 文字まで。
数式バーでは 32,767 文字すべての表示が可能。

参考までに。

＞また、エラーが表示されたH列のセルをダブルクリック後に確定するときちんと表示されます。

このパターンは、おそらく計算方法が「手動」になっているときの状態です。

「ツール」「オプション」の計算方法タブで「手動」になっていたら「自動」にチェックを入れてください。

お礼

何度もご回答頂きまして、本当にありがとうございます。

＞エクセルのヘルプで「仕様」と入力してワークシートとブックの仕様を調べる

初めて見ました。なるほど！ありがとうございました。
いつも質問する前に今回こそは自力で！と決意の元に挑むのですが、無知すぎて何をどうすればいいのかも分からず…皆様にすがってばかりです(>_<;) 道のりは険しすぎますが、諦めずに頑張ります。

＞また、エラーが表示されたH列のセルをダブルクリック後に確定すると表示可能
＞このパターンは、おそらく計算方法が「手動」になっているときの状態です。

私も、もしや？？と思い確認したのですが、ちゃんと「自動」なんですよね。かなり不思議です。

回答No.7

Wendy02

回答番号：No.4 の返事です。

昨日来、こちらのPCのトラブルより復旧するまで時間が掛かり、書きこもうとした時には、すでに締められてしまいましたが、回答を付けさせていただきます。

なお、私以外の人が作られたマクロや私の考えたものとはまったく違うマクロについては、必ずしもサポートするものではありませんから、予めご了承ください。

直接の回答とは言えませんが、

>B列からF列全てに何も入力（表示）されていない場合は「＃VALUE!」エラーが表示されていません。

これに関しては、ユーザー定義関数では、そのような条件の元では関数は作らないようにしています。私の作るものには、リクエストをいただかないと、そのような関数はつくりません。

>エラーが表示されたH列のセルをダブルクリック後に確定するときちんと表示されます。

そのような仕様というものは、いわゆる「揮発性関数」といいますが、VBAで作るユーザー定義関数では、引数を再定義しない限りは、再計算しません。ダブルクリックしないでもセル内容を変更することで、それに反映させることは可能ですが、今回は、すでに解決したものとして、見送りさせていただきます。

なお、私は、VBAを中心として回答している者で、既存の組み込み関数で解決しているものに関しては、特に注意を払うことはいたしておりません。組み込み関数で解決しない場合には、また相談に乗らせていただきます。また、本格的に関数を作るには、コンパイラーやレジストリ登録が必要になりますので、ここの場で提供するようにはできていません。

回答No.5

MackyNo1

>＞IF(ISTEXT(A2),A2,"")

>試してみましたがダメでした。
>セルを繋ぐとエラーが…なぜでしょうか(>_<)/

B2からF2セルに文字列が表示(または入力）されているなら、以下の式でエラーがでないと思うのですが本当にH列だけエラーが出ているのでしょうか？

=IF(ISTEXT(B2),B2,"")&IF(ISTEXT(C2),C2,"")&IF(ISTEXT(D2),D2,"")&IF(ISTEXT(E2),E2,"")
&IF(ISTEXT(F2),F2,"")

念のため、数式バーで上記の数式の一部（例えばIF(ISTEXT(B2),B2,"")の部分）をドラッグして選択してF9キーを押して、どの部分でエラーとなるか調べてみてください(計算結果の解除はEscキー）。

お礼

引き続きご回答本当にありがとうございます。

出来ました！
すみません。完全に私が原因でした。
途中「IF」が１つ抜けてました。（かなりアホですね。すみません。）

マクロを動かしても、セルに1,000字ほど文字を入れても問題ありませんでした。本当にありがとうございます。調子に乗って追加で質問なのですが、この方法だと文字数制限はどれくらいなんでしょうか？そんなに長文は入力しないのですが、今後の参考にご存知でしたらお教え下さい。

回答No.4

Wendy02

>「CONCATENATE」を使用するのは「Ｈ列」に「０」（ゼロ）を表示させないためです。
>（&(アンパサンド）では表示されてしまうので）

0を表示させないのは、CONCATENATE関数ではなくて、TEXT関数ですね。CONCATENATEには、0を省くような機能はないはずです。数値を抜く関数は、同じくLotus123共有関数のT()ぐらいしかありませんが、256文字の壁を超えることはありません。

例　= T(H2)　 ....H2 が数字なら、空白になります。

しかし、「&」でも、CONCATANATEでも、H列に0が入っていなくて、Emptyなら、どちらも「0」は表示しません。しかし、ここではそうではないようです。

こんな方法があります。256語の壁を超えるのは、テキスト系関数を使うしかありません。

=LEFT(B2,LEN(B2)*(1-COUNT(B2)))&LEFT(C2,LEN(C2)*(1-COUNT(C2)))&LEFT(D2,LEN(D2)*(1-COUNT(D2)))&LEFT(E2,LEN(E2)*(1-COUNT(E2)))&LEFT(F2,LEN(F2)*(1-COUNT(F2)))&LEFT(G2,LEN(G2)*(1-COUNT(G2)))&LEFT(H2,LEN(H2)*(1-COUNT(H2)))

私自身なら、関数を作ってしまいますが、それは、万民向けとは言えません。

'//標準モジュールへ
Function cat(rng As Range) As String
　Dim buf As String * 32767 '32,767文字まで
　Dim c As Variant
　Dim i As Long
　i = 1
　For Each c In rng
　　If Not IsNumeric(c.Value) And VarType(c) = vbString Then
　　　Mid(buf, i, Len(c.Text)) = c.Text
　　　i = i + Len(c.Text)
　　End If
　Next
　cat = Left(buf, InStr(buf, vbNullChar) - 1)
End Function

入力例：
=cat(B2:H2)

ただし、32,767文字が必ずしも表示出きるわけではありません。シートの表示は、セルひとつに対して、1,024文字までのはずです。つまり、セルが5個なら、5,120文字までです。

お礼

再びありがとうございます。

>0を表示させないのは、CONCATENATE関数ではなくて、TEXT関数ですね。
>CONCATENATEには、0を省くような機能はないはずです。

&(アンパサンド）を使用すると表示された「０」が、CONCATENATE関数使用で回避されたので、CONCATENATE関数のおかげかと思ってました。違うのですね (>_<;)/ 失礼しました。

>'//標準モジュールへ(以下省略)
>入力例：=cat(B2:F2)

教えていただいた上記を実行し、全ての行において表示できるようになりました。
（使用セルは最大でも2列、100行以内、１セル10～500文字以内なので問題ないと思います）
ですが、下記の質問で教えていただいたマクロを実行すると、全て「＃VALUE!」エラーになります。二つのマクロは共存できないのでしょうか？

過去の質問⇒Excel　入力して置換するマクロ
<http://questionbox.jp.msn.com/qa6278534.html>

補足

追加補足

よくみて見ると、B列からF列全てに何も入力（表示）されていない場合は「＃VALUE!」エラーが表示されていません。また、エラーが表示されたH列のセルをダブルクリック後に確定するときちんと表示されます。（以下のセルは上からコピーして表示させるようにしました。）

これは毎回しなければならないのでしょうか？
まったく素人の私よりビギナーさんが使用するので出来たら回避したいのですが…(>_<;)

オタスケ下さい。

回答No.3

Wendy02

素朴な疑問ですが、なぜ、CONCATENATE を使うのでしょうか？
CONCATENATE って、Lotus123との互換性を持たせるための関数で、Excelでは、&(アンパサンド）でよかったはずです。

TEXT(B2,";;;@")、それに、テキスト化する意味って、数値を排除する目的ですか？
お話の様子なら必要性がないと思います。「&」でつなげば、そのままテキスト化してしまいます。ブランクセルがあっても、関係ないはずです。

=B2&C2&D2&E2&F2

例えば、書式を変更するというならわかりますが、必要ありますか？

補足

ご回答ありがとうございます。

＞素朴な疑問ですが、なぜ、CONCATENATE を使うのでしょうか？

「CONCATENATE」を使用するのは「Ｈ列」に「０」（ゼロ）を表示させないためです。
（&(アンパサンド）では表示されてしまうので）

「Lotus123」との互換性の話ははじめて聞きました。ありがとうございます。

回答No.2

MackyNo1

＞上記の点から文字数？？と思い、エラーが出ていないセル（仮にA）の文章をコピーし、そのAセルの文章のお尻にコピーし文字数を増やすと同じエラーがでました

CONCATENATE関数の制限ではなく、TEXT関数の制限です。

すなわち、TEXT関数では最大256文字までの文字列しか返すことができません。

したがって、1つのセルで256文字を超えるセルを参照する場合には、TEXT関数ではなく、以下のようなIF関数で文字列のみ連結するようにしてください(もちろん「＆」で結合してもOKです）。

IF(ISTEXT(A2),A2,"")

お礼

ご回答ありがとうございます。
やはり制限があったのですね！また、ひとつ勉強になりました。

＞IF(ISTEXT(A2),A2,"")

明日朝一番に試してみます。
「文字列かどうかをチェック」にしか使用したことがなかったので盲点でした。

本当にありがとうございます。

補足

報告

＞IF(ISTEXT(A2),A2,"")

試してみましたがダメでした。
セルを繋ぐとエラーが…なぜでしょうか(>_<)/

回答No.1

MackyNo1

H列に入力されている数式は、256文字の文字数に関係なく連結することをができますので、#VALUE!エラーが出るのは別の原因です。

すなわち「=CONCATENATE(TEXT(B2,";;;@"),TEXT(C2,";;;@"),TEXT(D2,";;;@"),TEXT(E2,";;;@"),TEXT(F2,";;;@"))」のいずれかのテキスト関数がVALUEエラーとなっていることを示しています。

今回のケースでエラーが表示されるとしたら、数式を含むC列とE列のINDIRECT関数でエラーを返す可能性がありますが、エラーが出ている行には正常な文字列または数値が表示されているのでしょうか？

お礼

ご回答ありがとうございます。

不備な点を補足いたしましたので、よろしかったら引き続きご回答お願いいたします。

補足

早々のご回答ありがとうございます。
私も何か入っているの？？と思い確認してみましたが、エラーが出る行の、C列とE列が参照しているセルのにはテキストのみでした。

＊拡大しましたが文字のみでした（ちなみにドイツ語です。）
＊エラーが出ているのは２行で、どちらもテキスト数が多い
＊Excel以外のソフトにコピーしテキストをチェックしても文字のみでした。
＊テキストエディタに該当するテキストをコピー後、セルを空にして
　 再度エディタより貼り付けましたが結果は同じでした

上記の点から文字数？？と思い、エラーが出ていないセル（仮にA）の文章をコピーし、そのAセルの文章のお尻にコピーし文字数を増やすと同じエラーがでました。

とても混乱しています。(>_<;)