I wish to sort the following mixed strings in Excel:
我希望在Excel中对以下混合字符串进行排序:
A987G
D301N
A809V
N408K
so they run:
所以他们跑:
D301N
N408K
A809V
A987G
They're always in the format letter-number-letter(s). I wish them to be sorted smallest to largest. Can someone please tell me how to do this? [I don't have a Linux environment running on my work PC so can't solve it that way!]
它们总是采用字母 - 数字 - 字母的格式。我希望它们从最小到最大排序。有人可以告诉我该怎么做? [我没有在我的工作PC上运行Linux环境,所以无法解决这个问题!]
2 个解决方案
#1
1
With data in column A, in B1 enter:
对于A列中的数据,在B1中输入:
=--MID(A1,2,3)
and copy down. Then sort cols A and B by B
并复制下来。然后用B对cols A和B进行排序
#2
2
Simplest way may be, to a copy of the column containing your data, to apply Text to Columns Fixed width splitting on second and fifth characters. Then sort on those three columns:
对于包含数据的列的副本,最简单的方法是将文本应用于列固定宽度分割第二个和第五个字符。然后对这三列进行排序:
#1
1
With data in column A, in B1 enter:
对于A列中的数据,在B1中输入:
=--MID(A1,2,3)
and copy down. Then sort cols A and B by B
并复制下来。然后用B对cols A和B进行排序
#2
2
Simplest way may be, to a copy of the column containing your data, to apply Text to Columns Fixed width splitting on second and fifth characters. Then sort on those three columns:
对于包含数据的列的副本,最简单的方法是将文本应用于列固定宽度分割第二个和第五个字符。然后对这三列进行排序: