Excel 文字数に制限あり？？

Question

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

MackyNo1 · Accepted Answer

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

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

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

参考までに。

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

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

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

Wendy02 · Answer

回答番号：No.4 の返事です。

昨日来、こちらのPCのトラブルより復旧するまで時間が掛かり、書きこもうとした時には、すでに締められてしまいましたが、回答を付けさせていただきます。

なお、私以外の人が作られたマクロや私の考えたものとはまったく違うマクロについては、必ずしもサポートするものではありませんから、予めご了承ください。

直接の回答とは言えませんが、

>B列からF列全てに何も入力（表示）されていない場合は「＃VALUE!」エラーが表示されていません。

これに関しては、ユーザー定義関数では、そのような条件の元では関数は作らないようにしています。私の作るものには、リクエストをいただかないと、そのような関数はつくりません。

>エラーが表示されたH列のセルをダブルクリック後に確定するときちんと表示されます。

そのような仕様というものは、いわゆる「揮発性関数」といいますが、VBAで作るユーザー定義関数では、引数を再定義しない限りは、再計算しません。ダブルクリックしないでもセル内容を変更することで、それに反映させることは可能ですが、今回は、すでに解決したものとして、見送りさせていただきます。

なお、私は、VBAを中心として回答している者で、既存の組み込み関数で解決しているものに関しては、特に注意を払うことはいたしておりません。組み込み関数で解決しない場合には、また相談に乗らせていただきます。また、本格的に関数を作るには、コンパイラーやレジストリ登録が必要になりますので、ここの場で提供するようにはできていません。

MackyNo1 · Answer

>＞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キー）。

Wendy02 · Answer

>「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文字までです。

Wendy02 · Answer

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

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

=B2&C2&D2&E2&F2

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

MackyNo1 · Answer

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

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

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

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

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

MackyNo1 · Answer

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

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

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

Excel 文字数に制限あり？？

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

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

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

>「CONCATENATE」を使用するのは「Ｈ列」に「０」（ゼロ）を表示させないためです。

この回答への補足

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

この回答への補足

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

この回答への補足

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

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング