5文字以上の文字列だけを、他の場所に上詰めで表示させたい

質問

"ABCD"AAABB""EFG""DF.KO!#"などと文字列があったときに、5文字以上のものだけを他のセルに上に詰めて表示させる方法はあるでしょうか。LENで文字数を表示させて並び替えをすれば対象の文字列はすぐに分かりますが、その結果を組み込んでまた次の計算に使うので、手順を自動化できるように関数でできる方法があれば嬉しいです。また文字数で並び替えをすると文字列の順番が入れ替わってしまいますが、可能なら元の順番通りに他のセルに表示させたいです（対象の文字列の文字数が、上から7,5,9,8,6となっていたら、このままの順番で表示させたい）。でもこれが難しいなら、上に詰めて表示させる方法だけでもどうにかできないかと思っています。注文が多くて申し訳ありませんが、良い知恵をお持ちの方がいらっしゃれば、どうかご教授お願いします。

回答No.4

imogasi · ベストアンサー

（例データ）Ａ２：Ａ１０
第１行はデータを避けてください。
（右の１，２，３、４の数字はＢ列で関数式の結果をだしたもの、初めは出てません）

asddd 1
as
asdf
sdfgh 2
sdfghh 3
s
erttt 4
sd
f
（関数式）
こう言うタイプの問題は、私見では作業列が必須です。
どこでも良いがＢ列を作業列とします。実際は目立たないずっと右端の列を指定してはどうでしょう。後刻に、列非表示にしてしまうのも良いかも。
Ｂ１セルに
=IF(LEN(A2)>=5,COUNT($B$1:B1)+1,"")
といれ、Ｂ１０まで複写する。
結果は上記の（例データ）のＢ列の通り。
（抜き出し）
別シートに出す方法も簡単ですが、同一シートの
Ｅ２から出します。
Ｅ２に=OFFSET($A$1,MATCH(ROW()-1,$B$1:$B$10,0)-1,0)といれ、Ｅ５まで４件複写します。なぜ４件かは
Ｂ列数字のＭＡＸの４から由来します。
さてＢ列の関数式をデータ行より沢山入れておきたい場合
のエラー表示を出さない、（Ｅ列も同じだが）方法は本質でなく説明が長くなるので略。
元の順序は「保存されて」（ソートの熟語）います。
ＶＢＡでやれば、技巧的なことをせずに済みますが。

お礼

回答ありがとうございます。セルに位置が違うので教えていただいた式を解読して、訂正するのに手間取りましたが、おかげさまで思い描いていたものを作成することができました（エラーを出さないのも自力でなんとかできました！）。しかしすごいですねー。エクセルって下手な推理小説やパズルよりも謎解きが楽しめそうな（笑）　なにはともあれ本当にありがとうございました。

回答No.3

hakone

#2です。
間違いました。

>A3:A10までにもとのリストがあるとして、
としたので、

C3は
=COUNT(B:B)
か
=COUNT(B3:B10)

でした。

回答No.2

hakone

A3:A10までにもとのリストがあるとして、

B3に
=IF(LEN(A3)>=5,ROW(A3),"")
と書いて、下の方にコピー。

C3に
=COUNT(B3:B6)

D3に
=IF(ROW(A1)<=$C$3,INDEX(A:A,SMALL(B:B,ROW(A1))),"")
と書いて、下の方にコピーしてみて下さい。

作業列を使わない方法もあるんですが、
元のリストの数が多いと、激重になりますのでやめた方が良いです。

お礼

回答ありがとうございます。ちょっと私の頭がついていかなくて、理解できた他の方の案を使わせてもらったのですが、機会があれば教えていただいた式も参考にしたいと思います。

回答No.1

inte-nori

少し面倒くさいかもしれませんが、VBAという機能を用いた方法をご説明します。

文字列の入っている列がA列、上詰めで表示させる列がB列として説明します。

１．「ツール」＞「マクロ」＞「Visual Basic Editor」を開きます。

２．「挿入」＞「標準モジュール」を実行し、右側に表示されたウィンドウに下記のコードを貼り付けます。

Sub 上詰め()
　　j = 1
　　For i = 1 To Cells(65535, "A").End(xlUp).Row
　　　　If Len(Cells(i, "A")) >= 5 Then
　　　　　　Cells(j, "B").Value = Cells(i, "A").Value
　　　　　　j = j + 1
　　　　End If
　　Next
End Sub

３．画面下のタスクバーでエクセルの画面に戻します。

４．「ツール」＞「マクロ」＞「マクロ」を実行し、「上詰め」を選択して[実行]を押します。

もしも一行目に項目名が入っている場合は、上記のコードの二行目の「j = 1」と三行目の「i = 1」の部分をそれぞれ「j = 2」と「i = 2」にしてください。

また、文字列の入っている列や、上詰めで表示されるセルが他の列の場合には、上記のコードの三行目、四行目、五行目の"A"の部分（三箇所）と五行目の"B"の部分（一箇所）を、それぞれ該当する列のアルファベットに変更してください。

お礼

回答ありがとうございます。多少なりとも自分で理解できるよう、VBAを使うのは見合わせたのですが、回答をいただいたことには感謝いたします。