表のばらばらの文字列をまとめるマクロ 関数

質問

以前、同じ内容の質問を聞いたのですが、少し条件を変えて質問です。

(1)左の表には数式が入っていて、この他にもう一つある表のデータに該当すると1列ごとにSMとかNPとか表示されるようになっています。

(2)この左の表は実際には136列あるので、結果を印刷すると一枚に収まりきらないのです。なので、右の表のように、最短の列に表示したいのです。

(3)この例ではたまたま1行に1個ずつしか文字列が入ってないので右の表は1列ですが、1行に付き2個以上文字列が入ることもあります。5個以上はほとんどないです。

(4)関数でできませんか？　またはマクロでもいいです。

以前、回答でいただいた関数は
=IFERROR(HLOOKUP("*?",$A1:$D1,1,FALSE),"")
又は
=IF(COUNTIF($A1:$D1,"*?"),HLOOKUP("*?",$A1:$D1,1,FALSE),"")
でしたが、これだと1行につき1個文字列があるときのみ可能です。

1行に付き2個以上文字列がある場合に、例えば空欄を削除して左に詰めるような感じでマクロでできないでしょうか？

回答No.5

bunjii · ベストアンサー

>この例ではたまたま1行に1個ずつしか文字列が入ってないので右の表は1列ですが、1行に付き2個以上文字列が入ることもあります。
2個以上のときは1つのセルに文字列を連結して代入したいのですか？

>5個以上はほとんどないです。
「ほとんどない」と「絶対無い」では処理方法が異なります。
最大の個数を提示された方が良いでしょう。

関数でも処理可能ですがとても長い数式になります。
複数の連続したセルへ個々に代入するのであれば比較的簡単です。
元データがA列からZ列までに限定した検証ですが画像を添付します。
各行にはランダムに2文字の文字列を最大5個を設定しました。
AD1=IF(COUNTA($A1:$Z1)>=COLUMNS($AD1:AD1),INDEX($A1:$Z1,SMALL(INDEX(($A1:$Z1<>"")*COLUMN($A1:$Z1)+($A1:$Z1="")*COLUMN($AB1),1,0),COLUMNS($AD1:AD1))),"")
AD1セルを右にAH1セルまでオートフィルコピーしました。
AB1=CONCATENATE(AD1,AE1,AF1,AG1,AH1)
AB1:AH1を選択した状態で下へオートフィルコピーをすれば添付画像のようになります。
元データの文字列の前後に空白があるときはAD1の数式で抽出した文字列をTRIM関数で空白を除去すれば良いでしょう。
Excel 2013で検証しましたがExcel 2007でも再現できるはずです。
AD列からAH列を使いたくないときはCONCATENATE関数の第1引数から第5引数を夫々のセルの数式を直接AB列へ入力することになり非常に長い数式になります。

回答No.6

ushi2015

こんにちは
Alt+F11キーでVBE画面出して、メニューの挿入で標準モジュール追加して、
先のコードをコピペして、例えばセルEG1に
=Conc(A1:EF1)
と入れればA1:EF1の範囲のデータを纏めて表示します。
ご自分なりにやって出来なかったとは、どのように試したのでしょうか？

回答No.4

kagakusuki

>以前、回答でいただいた関数

を回答した者です。
　その

>以前、同じ内容の質問

に対して私は、その関数以外にも

>表のばらばらの文字列をまとめるマクロ

によるユーザー定期関数も回答した筈ですが、その方法では何か不都合があったのでしょうか?
　下記のURLのページの回答にあるユーザー定義関数を使って

=JOIN2D($A1:$D1)

とセルに入力すれば、

SN

という結果が得られますし、もしA1セルに「SN」、C1セルに「C1」、D1セルに「D1」と入力されていた場合には、

SNC1D1

という結果が得られます。
　更には、

=JOIN2D($A1:$D1,",")

とセルに入力すれば、

SN,C1,D1

という具合に指定した文字列(この場合は「,」)で区切った形で結果を得る事も出来ます。

【参考URL】
　表のばらばらの文字列を1列にまとめたい　関数 【OKWAVE】 > 回答No.3
　　http://okwave.jp/qa/q9256531/a25800564.html

お礼

できました。ありがとうございました！

回答No.3

ushi2015

こんにちは
ユーザー定義関数で、
Function Conc(t As Variant) As Variant
　　Dim r As Variant
　　Dim v As Variant
　　For Each r In t
　　　　v = v & r
　　Next
　　Conc = v
End Function
とかを作って、セルに=CONC(A1:EF1)のようにセットしてはどうですか？

補足

その辺あまり詳しくないのですが、自分なりにやって出来ませんでした。

回答No.2

Chiquilin

Excelのバージョンがいくつなのか分かりませんが Office365サブスクライバーなら
TEXTJOIN関数でおしまいです。

> またはマクロでもいいです。
だったらユーザー定義関数を自分で作ればいいでしょう。For～Nextで文字列を繋
ぐだけです。

> 関数でできませんか？
左表を値に戻して PHONETICで範囲指定するか 作業シートを用意して １セルずつ
繋いだものを参照するか。

補足

すみません、知識がなくて分かりません。 エクセルは2007です。

回答No.1

ushi2015

こんにちは
普通に
=A1&B1&C1&D1
とか
=CONCATENATE(A1,B1,C1,D1)
ではダメなのですか？

補足

136列あり、スペースかカンマを入れるとさらに長くなります。短いのありませんか？