アルファベットを含む数をエクセルでうまく並べ替えをしたい。

質問

エクセルのA列の中にある、数字で始まりアルファベットを含む桁数が一定ではない
1
2
3
1A1
2B30A3
3D
10A1
1A2
のようなデータを

1
1A1
1A2
2
2B30A3
3
3D
10A1

のように、アルファベットの前後の数で並べ替えをしたいのですが、
「データの並べ替え」を使っても
1
2
3
10A1
1A1
1A2
2B30A3
3D

のように並んでしまい困っています。

そこでB列を作業用・並べ替え用のキー列にしたいのですが、
どのような関数、またはマクロを使えば、
上記中段のような並びに出来るでしょうか？

前後に０をつけて桁数を揃えたり、数字とアルファベットを他の記号などに置き換えたりすればよいのかな・・・？
など色々試しているのですが、なかなか思うように行きません。

よろしくお願いします。

回答No.5

abe_onesel · ベストアンサー

No2,No4です。

●：数字
△：英字
●●●△△△●●●△△△●●●△△△
という並びに対応させました。

B2:
=IF(NOT(ISNUMBER(MID($A2,2,1)*1)),LEFT($A2,1),IF(NOT(ISNUMBER(MID($A2,3,1)*1)),LEFT($A2,2),IF(NOT(ISNUMBER(MID($A2,4,1)*1)),LEFT($A2,3),A2)))

C2:
=IF(ISNUMBER(MID($A2,2+LEN(B2),1)*1),MID($A2,1+LEN(B2),1),IF(ISNUMBER(MID($A2,3+LEN(B2),1)*1),MID($A2,1+LEN(B2),2),IF(ISNUMBER(MID($A2,4+LEN(B2),1)*1),MID($A2,1+LEN(B2),3),MID($A2,LEN(B2)+1,3))))

D2:
=IF(NOT(ISNUMBER(MID($A2,2+LEN(B2&C2),1)*1)),MID($A2,1+LEN(B2&C2),1),IF(NOT(ISNUMBER(MID($A2,3+LEN(B2&C2),1)*1)),MID($A2,1+LEN(B2&C2),2),IF(NOT(ISNUMBER(MID($A2,4+LEN(B2&C2),1)*1)),MID($A2,1+LEN(B2&C2),3),MID($A2,1+LEN(B2&C2),3))))

E2:
=IF(ISNUMBER(MID($A2,2+LEN(B2&C2&D2),1)*1),MID($A2,1+LEN(B2&C2&D2),1),IF(ISNUMBER(MID($A2,3+LEN(B2&C2&D2),1)*1),MID($A2,1+LEN(B2&C2&D2),2),IF(ISNUMBER(MID($A2,4+LEN(B2&C2&D2),1)*1),MID($A2,1+LEN(B2&C2&D2),3),MID($A2,LEN(B2&C2&D2)+1,3))))

F2:
=IF(NOT(ISNUMBER(MID($A2,2+LEN(B2&C2&D2&E2),1)*1)),MID($A2,1+LEN(B2&C2&D2&E2),1),IF(NOT(ISNUMBER(MID($A2,3+LEN(B2&C2&D2&E2),1)*1)),MID($A2,1+LEN(B2&C2&D2&E2),2),IF(NOT(ISNUMBER(MID($A2,4+LEN(B2&C2&D2&E2),1)*1)),MID($A2,1+LEN(B2&C2&D2&E2),3),MID($A2,1+LEN(B2&C2&D2&E2),3))))

G2:
=IF(ISNUMBER(MID($A2,2+LEN(B2&C2&D2&E2&F2),1)*1),MID($A2,1+LEN(B2&C2&D2&E2&F2),1),IF(ISNUMBER(MID($A2,3+LEN(B2&C2&D2&E2&F2),1)*1),MID($A2,1+LEN(B2&C2&D2&E2&F2),2),IF(ISNUMBER(MID($A2,4+LEN(B2&C2&D2&E2&F2),1)*1),MID($A2,1+LEN(B2&C2&D2&E2&F2),3),MID($A2,LEN(B2&C2&D2&E2&F2)+1,3))))

H2:
=RIGHT("000"&B2,3)&RIGHT("---"&C2,3)&RIGHT("000"&D2,3)&RIGHT("---"&E2,3)&RIGHT("000"&F2,3)&RIGHT("---"&G2,3)

少し式が長くなっていますが、同じことの繰り返しです。
B列～G列に数字、英字の部分を出し、
H列でソート用に３桁に揃えてくっつけています。

今さらですが、数字と英字の間にコンマ（,）とかが入っているデータであれば、
比較的簡単にできる方法もありますけどね。

使用している関数は、それぞれ難しいのはないと思いますが、組み合わせると幅が広がります。
IF関数とISNUMBER関数の組み合わせは理解するのが大変かもしれませんが、
MID関数、LEFT関数、RIGHT関数は覚えておくと応用範囲が広がると思います。

お礼

本当にありがとうございました。
おかげで希望通りの並び替えをすることができました！

これを機会に関数を覚え、条件が変わった時でも自力で処理できるようにしたいと思います。

回答No.4

abe_onesel

No2です。

＞のようにデータを数字とアルファベットに分け、それぞれの列を参照して並べ替えをする、というやり方が良いのだろうか。
＞と考えておりますが、この考えの方向はあってますでしょうか？

この考えで合っていると思います。

そして、私の解釈ですが、
１、数字は最大で３文字続く場合がある。
２、アルファベット（英字）は２文字以上続かず、英字と英字の間には必ず数字が入る。
３、数字＋英字＋数字＋英字＋数字＋英字という並びになり、どこかから以降のデータが無い場合もある。
このような感じでよろしいでしょうか？

実際の方法です。
Ｂ列だけでやろうとすると複雑になるので、Ｃ列とＤ列も使用します。

Ａ１～Ｄ１のセルに任意の項目名を入力しておきます。

Ｂ２セル（前と同じです）：
=IF(TYPE(A2)=1,TEXT(A2,"000"),IF(CODE(MID(A2,2,1))>64,"00"&A2,IF(CODE(MID(A2,3,1))>64,"0"&A2,A2)))

Ｃ２セル：
=IF(LEN(B2)>3,IF(NOT(ISNUMBER(MID(B2,6,1)*1)),LEFT(B2,4)&"00"&MID(B2,5,99),IF(NOT(ISNUMBER(MID(B2,7,1)*1)),LEFT(B2,4)&"0"&MID(B2,5,99),B2)),B2)

Ｄ２セル：
=IF(LEN(C2)>8,IF(NOT(ISNUMBER(MID(C2,10,1)*1)),LEFT(C2,8)&"00"&MID(C2,9,99),IF(NOT(ISNUMBER(MID(C2,11,1)*1)),LEFT(C2,8)&"0"&MID(C2,9,99),C2)),C2)

Ｂ２～Ｄ２を下方向へコピーし、Ｄ列で並び替えれば大丈夫ではないかと思います。
Ｎｏ３のhige_082様の数式も参考にさせて頂きました。

お礼

ありがとうございます。

abe_onesel様が挙げられた、データの条件の２番目の前半部を
「アルファベットは最大で３個まで連続する」
とさせて頂ければ、私が並べ替えをしたいデータの定義になります。
(A,AB,BA,DLC,DRC...のようなものです)

再度ご回答いただいた関数を試してみました。
NO,2で教えていただいたものより、より希望に近い並びになりました。
なるほど、このようにアルファベット間の数字の桁を揃えれば、アルファベットと数字が混在する文字列の並び変えが出来るのですね。

私の表現力不足で、データの定義が間違っていたため、下記A列の
データをＤ列で並べ替えをすると

Ａ列 , Ｂ列 , Ｃ列 , Ｄ列
14AB7, 014AB7, 014A0B7, 014A0B7
14AB8, 014AB8, 014A0B8, 014A0B8
14AB9, 014AB9, 014A0B9, 014A0B9
14AC1, 014AC1, 014A0C1, 014A0C1
14AC2, 014AC2, 014A0C2, 014A0C2
14AC3, 014AC3, 014A0C3, 014A0C3
14AC4, 014AC4, 014A0C4, 014A0C4
14AC5, 014AC5, 014A0C5, 014A0C5
14AC6, 014AC6, 014A0C6, 014A0C6
14AC7, 014AC7, 014A0C7, 014A0C7
14AC8, 014AC8, 014A0C8, 014A0C8
14AC9, 014AC9, 014A0C9, 014A0C9
14AB10, 014AB10, 014AB10, 014AB10
14AB11, 014AB11, 014AB11, 014AB11
14AB12, 014AB12, 014AB12, 014AB12

となります。

14AB9の後に14AB10、14AB11,14AB12,14AC1,14AC2...となればベストです。

こちらの勉強不足からくる言葉足らずのところを、意を酌んでいただき、重ねてのわかり易く丁寧なご回答、本当にありがとうございました。

恥ずかしながら、教えていただいた関数をエクセルのヘルプを見ながら式の意味を参照している段階ですが、回答をお手本にしてケースに応じて自力で関数を使えるように勉強していきます。
ありがとうございました！

回答No.3

hige_082

一例です
Ａ列にデータがあるとして
Ｂ１に
=IF(OR(LEN(A1)=1,NOT(ISNUMBER(MID(A1,2,1)*1))),"0"&A1,A1)
を入力し、下方向にコピー

お礼

ありがとうございます、こちらの関数も参考にさせていただきました。

回答No.2

abe_onesel

A1セル、B1セルに任意の項目名があるとして、
A2～A9セルに上記のデータがあるものとします。

B2セルに以下の数式を入力します。
=IF(TYPE(A2)=1,TEXT(A2,"00"),IF(CODE(MID(A2,2,1))>64,"0"&A2,A2))
これをB9セルまでコピーし、B列で並びかえれば大丈夫だと思います。

ただ、例では２桁までの数字しか使用していませんので、
123AB1みたいな３桁の数字が入る場合は、
=IF(TYPE(A2)=1,TEXT(A2,"000"),IF(CODE(MID(A2,2,1))>64,"00"&A2,IF(CODE(MID(A2,3,1))>64,"0"&A2,A2)))
で大丈夫かと思います。

数字が４桁以上になっても少し非効率ですが、
上記２つの式を参考にIF関数を増やし、0を増やしていけばなんとか対応はできます。

お礼

アルファベットの前の数字が最高で３桁のデータでしたので、非常に参考になりました。ありがとうございます。

私の質問で挙げた例が曖昧でいけなかったのですが、
データは必ず数字から始まり、整数のみのもの、１～３個のアルファベット（２個以上は必ず連続します）を含み、アルファベットか数字どちらかがデータの終わりに入ります。
一番長いもので１２桁ほど、アルファベットの塊は０～３個入ります。

abe_onesel様に教えていただいた方法でやってみましたが、
1
1A10A1
1A1A
1A2A1
2

となり、1と2の間に1の後ろにアルファベットが続くものが入ってきて、非常にリストが見やすくなりました。
しかしアルファベットの後の数字の並べ替えがうまくいきませんでした。

こうなりますと、

1 → 1,
1A10A1 → 1, A,10, A, 1
1A1A → 1, A, 1, A
1A2A1 → 1, A, 2, A, 1
2 → 2,

のようにデータを数字とアルファベットに分け、それぞれの列を参照して並べ替えをする、というやり方が良いのだろうか。
と考えておりますが、この考えの方向はあってますでしょうか？

ともかくお答えを参考にして、以前よりリストの参照が楽になりました
ありがとうございました。

回答No.1

web2525

エクセル上では数字よりも文字列が大きいからそのままでは質問の並び替えにはなりません。
B1に
=MID($A1,COLUMN()-1,1)
と入力しD3迄フィル、下方向にフィルして、並び替えをB列、C列、D列の順位で優先順位をつけて並び替えすると
1
10A1
1A1
1A2
2
2B30A3
3
3D
こんな順番に。
目的の並べ替えのためには、文字列の最初のアルファベットまでの文字数を数えて、その文字数の一つ手前までの文字列での並べ替えが必要。
関数だけでの実現は難しいかも。

お礼

教えていただいた関数を会社で試してみましたが、おっしゃる通り希望どおりの並べ替えができませんでした。

最初のアルファベットまでの並べ替えは解答番号：no2の方のやり方で
うまくいったのですが、

1A1A1
1A10A1
1A2A1

とアルファベットの次からの並べ替えでつまづいてしまいました。

マクロでの並べ替えを勉強してみます。
ありがとうございます。