Excelで英数字を昇順で並び替えたい

質問

Excelで、A列に下記のようなデータが入力されています。

A10B10
A1B1
A1B5
A2B1
A10B1
A2B5
B1A5
A1B13
B1A10
B10A2
B4A3
B1A1　・・・

これを、下記のように「記号順と記号の後ろの数字を昇順」にして並べ替えたいです。

A1B1
A1B5
A1B13
A2B1
A2B5
A10B1
A10B10
B1A1
B1A5
B1A10
B4A3
B10A2

単に昇順化するとA10B1などが先に来てしまい理想通りになりません。
隣の列にSUBSTITUTE関数などで英字を数字化させたり消して並び替えてもうまくいきません。
アドバイスをいただければと思います。

回答No.9

masnoske

[No.1] です．
条件の後出しは，あなたのデータ処理能力が疑われるだけですよ．
あなたはデータ処理をする立場にあるのですから，そのデータの範囲を知っていることは最低条件です．

> アルファベットはAとB、数字は1桁または2桁という前提です。
これが私があなたの例から読み取った条件です．

あなたの追加条件は，Bに-がある場合もあるということですね．
おそらくAにも-があるのではないかと推測します．
ABだけでCはないのか？
-の後ろは1桁だけなのか？
アルファベットは1文字だけなのか？
-以外の文字はないのか？

条件を提示してください．

回答No.8

Mathmi

No.3です
まだ回答が付いていないようなので、試しにStrCmpLogicalWを使ったコードを組んでみました。
基準セルを変更すれば、元データの位置及びソート後のデータを出力する位置を変更できます(現在は同じ場所に上書きするようになっています)。

Option Explicit

Private Declare Function StrCmpLogicalW Lib "SHLWAPI.DLL" ( _
　　　　　　　　　　ByVal lpStr1 As Long, _
　　　　　　　　　　ByVal lpStr2 As Long) As Long

Sub test()
　Dim i As Long, j As Long, cnt As Long, tmp As Variant
　Dim SourceRange As Range
　Dim SortCell As Range
　Dim Ary() As String

　'基準セルを設定
　Set SourceRange = Cells(1, 1)
　Set SortCell = Cells(1, 1)

　'元データを取得
　Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
　cnt = SourceRange.Count
　ReDim Ary(1 To cnt)
　For i = 1 To cnt
　　Ary(i) = SourceRange(i)
　Next i

　'バブルソート
　For i = 1 To cnt - 1
　　For j = i + 1 To cnt
　　　If StrCmpLogicalW(StrPtr(Ary(j)), _
　　　　　　　 StrPtr(Ary(i))) < 0 Then
　　　　tmp = Ary(i)
　　　　Ary(i) = Ary(j)
　　　　Ary(j) = tmp
　　　End If
　　Next
　Next

　'出力
　SortCell.Resize(cnt, 1).Value = WorksheetFunction.Transpose(Ary)
End Sub

回答No.7

Chiquilin

A1からデータがあるとして B1に

=CONCAT(IFERROR(TEXT(FILTERXML("<a><b>"&CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&IF(ISNUMBER(-MID(A1,SEQUENCE(LEN(A1)),1))*ISERR(-MID(A1,SEQUENCE(LEN(A1),,2),1))+ISERR(-MID(A1,SEQUENCE(LEN(A1)),1))*ISNUMBER(-MID(A1,SEQUENCE(LEN(A1),,2),1)),"</b><b>",""))&"</b></a>","//b"),"000"),""))
データ最終行までコピーして B列を基準にソート

回答No.6

msMike

B1: =LEFT(A1,1)
C1: =MID(A1,2,5)
D1: =LOOKUP(10^17,LEFT(C1,COLUMN($1:$1))*1)
E1: =TEXT(D1,"00")
F1: =SUBSTITUTE(A1,B1&D1,"")
G1: =LEFT(F1,1)
H1: =SUBSTITUTE(F1,G1,"")*1
I1: =TEXT(H1,"00")
J1: =B1&E1&G1&I1
範囲 B1:J1 を下方にオートフィル⇒範囲 A:J 選択⇒J列をキーにして[昇順]に[並べ替え]
【お断り】過去に登場された回答者が考案された数式を無断借用しています。m(_._)m

回答No.5

imogasi

#4です。
参考に読んでください。
VBAで、私製関数を作ってみました。
標準モジュールというところへ、下記をコピペ。
Function keymk(x As Range)
s = ""
n = 0
fstchr = "y"
For i = 1 To Len(x)
y = Mid(x, i, 1)
If Asc(y) >= 48 And Asc(y) <= 57 Then
'数字文字
n = n * 10 + Val(y)
Else
'文字列
If fstchr = "y" Then GoTo P1
s = s & Format(n, "00")
fstchr = "y"
n = 0
P1:

fstchr = "n"
s = s & y
End If
Next i
s = s & Format(n, "00")
keymk = s
End Function
＝＝＝＝
シートのA列に下記データを入れる。
B2に、式を　=keymk(A2)　と入れて、下方向に式を複写。
結果は下記。
A1:A14のセル範囲に

A列　　　B列
原データ 変換後
A1B1 A01B01
A1B5 A01B05
A1B13 A01B13
A2B1 A02B01
A2B5 A02B05
A10B1 A10B01
A10B10 A10B10
B1A1 B01A01
B1A5 B01A05
B1A10 B01A10
B4A3 B04A03
B10A2 B10A02
C12A3 C12A03
色々な場合でエラーが出ないかテスト必要。
（注）数字部分は２桁以下に限る。
数字は半角に限る。
もっと簡単なVBAコードにならないかと思いつつ、力不測で・・。関数の難しい式の組み合わせと変わらない？

回答No.4

imogasi

・全体的には文字列のデータです。＞英数字を昇順、というのは本件に関しては内容を表してません。
・文字の何番目かの１文字を左番目から比較していくのが、コンユーターの大小比較の根底にあります。
・だから数字の部分は、現れる最大桁数を考慮して、修正ソートキーを作らなければならない。エクセルでは別列に作らざるを得ない。
（ただし、アルファベット（本件ではAやB）などの出現位置は、各データで一定で、連なりの文字数の長さも一定でないとこういう議論はできません。）
・修正ソートキーを、１列に作る方法と、本件では４列を使う方法があります。
・１列に修正ソートキーを作るなら、（最大２桁しか数字が表れないとして）、１桁数字は、０１のように変換しなければなりません。
ーー
・文字列の数字部分と文字部分の分離は、結構、関数で出すのは、技巧的な方法しかなく、初心者は何日考えても思いつかないものです。
「数字と文字を分ける エクセル」でGoogle照会せよ。
・だからVBAででもやって、関数化し、まる写ししなければしようがないだろう。
・入力の段階から、１は０１と入力するよう、設計して、入力者に徹底すべきなんだ。データベース設計の、コード設計などは似たことを考えるもの。
とりあえず、考え方のみ。

回答No.3

Mathmi

VBAとAPIが使用できるなら、エクスプローラー順に並べ替えるという方法もあります。
参考URL:https://blog.goo.ne.jp/end-u/e/cdd7ec993d294636981b90cd407a2f97

ファイル名に上記の記号を入れて試してみましたが、エクスプローラではご希望通りの順番に並んでいました。

回答No.2

bunjii

>隣の列にSUBSTITUTE関数などで英字を数字化させたり消して並び替えてもうまくいきません。
英字より数字の桁合わせをする方が良いでしょう。
先頭の英字に続く数字が1桁のとき英字の次に"0"を付加して2桁数字に合わせ、中間の英字の右側も同様に2桁に合わせてすべての文字列を6桁に合わせる必要があります。
中間の英字の位置をC列へ抽出すると並べ替え用のデータをB列へ設定し易くなります。
C1=MIN(INDEX(FIND({"A";"B"},A1&"AB",2),0))
B1=LEFT(A1,1)&TEXT(MID(A1,2,C1-2),"00")&MID(A1,C1,1)&TEXT(MID(A1,C1+1,2),"00")
C1とB1を下へ必要数コピーしてBを基準に並べ替えをしてください。

回答No.1

masnoske

アルファベットはAとB、数字は1桁または2桁という前提です。
以下の数式を各セルに入力
入力した数式を下までコピー
F列とG列で昇順に並べる

セルB1 =FIND("A",A1)
セルC1 =FIND("B",A1)
セルD1 =IF(B1<C1,LEFT(A1,C1-1),LEFT(A1,B1-1))
セルE1 =IF(B1<C1,MID(A1,C1,3),MID(A1,B1,3))
セルF1 =LEFT(D1,1) & RIGHT("0" & MID(D1,2,2),2)
セルG1 =LEFT(E1,1) & RIGHT("0" & MID(E1,2,2),2)

補足

かなり理想的な動きをしてくれました。ご回答ありがとうございます。
数字が2桁のデータでは問題なく機能しました。
ただ、こちらの想定外だったこととして、ハイフン交じりの3桁・4桁のデータが存在したため、対応に難儀しています。
例：A2B5-2、A10B10-1等