提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
注册账户
x

or

如何在Excel中对字母数字数据进行排序?

原始数据 通常对结果进行排序 你想要的排序结果
doc排序字母数字1  2 doc排序字母数字2  2 doc排序字母数字3

如果您有一个与数字和文本字符串混合的数据列表,当您在Excel中正常排序此列数据时,所有纯数字都排在顶部,混合文本字符串排在底部。 但是,您需要的结果就像显示的最后一个截图。 本文将提供一种有用的方法,您可以使用它来对Excel中的字母数字数据进行排序,以便您可以获得所需的结果。

用公式帮助栏对字母数字数据进行排序


箭头蓝色右泡 用公式帮助栏对字母数字数据进行排序


在Excel中,可以创建一个公式帮助器列,然后通过这个新列对数据进行排序,请按照以下步骤进行操作:

1。 输入这个公式 =文字(A2,“###”) 进入一个空白的单元格,除了你的数据,B2,例如,看截图:

doc排序字母数字4

2。 然后将填充手柄向下拖动到您要应用此公式的单元格,请参阅截图:

doc排序字母数字5

3。 然后按这个新列对数据进行排序,选择你创建的帮助者列,然后点击 数据 > 排序,并在弹出的提示框中选择 展开选择,看截图:

doc排序字母数字6  2 doc排序字母数字7

4。 并点击 排序 按钮打开 排序 对话框下 部分中,选择 帮手列 名称,你想排序,并使用 价值排序 部分,然后选择你想要的排序顺序,看截图:

doc排序字母数字8

5。 然后点击 OK,在弹出的排序警告对话框中,请选择 将数字和数字分开存储为文本,看截图:

doc排序字母数字9

6. 然后点击 OK 按钮,你可以看到,数据已经被分类到你的需要。

doc排序字母数字10

7。 最后,您可以根据需要删除帮助程序列的内容。



推荐的Excel生产力工具

Kutools for Excel帮助您提前完成工作,并从人群中脱颖而出

  • 超过300强大的高级功能,专为1500工作场景设计,通过70%提高生产力,让您有更多时间照顾家庭和享受生活。
  • 不再需要记忆公式和VBA代码,从现在起让你的大脑休息一下。
  • 成为3分钟的Excel专家,复杂和重复的操作可以在几秒钟内完成,
  • 每天减少成千上万的键盘和鼠标操作,现在告别职业病。
  • 110,000高效人才和300 +世界知名公司的选择。
  • 60-day full功能免费试用。 60天退款保证。 2多年的免费升级和支持。

将选项卡式浏览和编辑带到Microsoft Office,远比浏览器的选项卡强大

  • Office选项卡专为Word,Excel,PowerPoint和其他Office应用程序设计:Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Lorne Rowe · 4 months ago
    Thanks for the tip on using a "helper column", but here's my question:

    Is there a way to get the "related " columns to "tag along" with the sort precipitated by the helper column?

    For example, column A contains a mixed-string product code that I can now sort into sequence using the "helper column" technique but, attached to that same product code are product descriptions, dimensions and other characteristics (some of which are also mixed-string in nature) in several other columns, and I need those to follow the "helper column" sort.

    Any suggestions?

    Much appreciated!

    Lorne
  • To post as a guest, your comment is unpublished.
    val · 5 months ago
    I only see 1 method. Top of the article mentions that we will see 2 methods.
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hello, val,
      Thank you for your comment, it is my mistake, I have deleted the second method from this article, and now, I have updated the article.

      Thank you once more!
  • To post as a guest, your comment is unpublished.
    Vinod Saini · 2 years ago
    Trying to sort Alphanumeric with text. It is a fairly large list but below is an example. I can't strip the off the letter in a different column and sort by that because I have matching numbers that should come before them. I also can't really add a column to sort by and then hide that column - or delete it because I am constantly sorting the data. Any ideas?
    Will there be some formula?

    My current list: ---- What I want it to look like:
    1 ---- 1
    2 ---- 2
    3 ---- 3
    4 ---- 4
    A1 ---- A1
    A10 ---- A2
    A100 ---- A3
    A2 ---- A4
    A20 ---- A5
    A200 ---- A6
    A3 ---- A7
    A4 ---- A8
    A5 ---- A9
    A6 ---- A10
    A60 ---- A20
    A7 ---- A60
    A8 ---- A100
    A9 ---- A200
    • To post as a guest, your comment is unpublished.
      VBA Dave · 1 years ago
      The easiest way I can think is to split the column into the Alpha part ("A") and the numeric part ("1") and then sort first by alpha then by numeric.
      Your original 1,2,3,4 would need to stay in the alpha column to sort properly .

      N.B. this might be a bit more difficult to achieve when the alpha part (the "A") is not a consistent length in your data. But definitely still possible.
  • To post as a guest, your comment is unpublished.
    Turfa · 2 years ago
    I am running in the same problem. I have 1200 part numbers that I need to sort the way it's shown above but excel will put V15 after V1 if V14 is absent. I need V14 to appear after V9 or V8, where ever the part number ends.
  • To post as a guest, your comment is unpublished.
    TC · 2 years ago
    Trying to sort Alphanumeric with text. It is a fairly large list but below is an example. I can't strip the off the letter in a different column and sort by that because I have matching numbers that should come before them. I also can't really add a column to sort by and then hide that column - or delete it because I am constantly sorting the data. Any ideas?

    My current list: ---- What I want it to look like:
    1 ---- 1
    2 ---- 2
    3 ---- 3
    4 ---- 4
    A1 ---- A1
    A10 ---- A2
    A100 ---- A3
    A2 ---- A4
    A20 ---- A5
    A200 ---- A6
    A3 ---- A7
    A4 ---- A8
    A5 ---- A9
    A6 ---- A10
    A60 ---- A20
    A7 ---- A60
    A8 ---- A100
    A9 ---- A200