在Excel中使用结构化引用对AlphaNumeric进行排序 [英] Sort AlphaNumeric with structured references in Excel

问题描述

 数据
x = 1.1 
x = 11.2 
x = 10.3 
x = 1.4 
x = 2.5; 2.6 
x = 2.1 
x = 4.7 
x = 6.8 
x = 6.2; 6.3 
x = 1.10 
  

我想要做的是排序它看起来像这样的List。

  DataSort 
x = 1.1 
x = 1.4 
x = 1.10 
x = 2.1 
x = 2.5; 2.6 
x = 4.7 
x = 6.2; 6.3 
x = 6.8 
x = 10.3 
x = 11.2 
  

我尝试用该公式执行此操作

  = LEFT（Tabelle1 [[＃row]; [Data]]; 2）& TEXT（SUBSTITUTE（Tabelle1 [[＃this row]; [Data]]; LEFT（Tabelle1 [[＃row]; [Data]]; 2）;）;＃0.0＃）
  

但是没有工作。
有人可以给我一个提示吗？

解决方案

问题的解决方案是：

  = LEFT（IF（ISNUMBER（SEARCH（;; A1））; LEFT（A1; FIND（;; A1; 1）-1）; A1）; 2）及; TEXT（ -  MID（IF（ISNUMBER（SEARCH（ ;; A1））; LEFT（A1; FIND（ ;; A1; 1）-1） ; A1）; 3; FIND（。 ; IF（ISNUMBER（SEARCH（; ; A1））; LEFT（A1; FIND（; ; A1; 1）-1）; A1）及。 ）-3）;000; @）& TEXT（ -  MID（IF（ISNUMBER（SEARCH（;; A1））; LEFT（A1; FIND（;; A1; 1）-1 ）; A1）; FIND（  ; IF（ISNUMBER（SEARCH（; ; A1））; LEFT（A1; FIND（; ; A1; 1）-1）; A1）及。 ）+1; 99）;000; @）
  

I Have an Excel Sheet with data that looks like that.

Data
x=1.1
x=11.2
x=10.3
x=1.4
x=2.5;2.6
x=2.1
x=4.7
x=6.8
x=6.2;6.3
x=1.10

What i want to do is, to sort the List that it Looks like that.

DataSort
x=1.1
x=1.4
x=1.10
x=2.1
x=2.5;2.6
x=4.7
x=6.2;6.3
x=6.8
x=10.3
x=11.2

I tried to do that with that Formula

=LEFT(Tabelle1[[#this row];[Data]];2) & TEXT(SUBSTITUTE(Tabelle1[[#this row];[Data]];LEFT(Tabelle1[[#this row];[Data]];2);"");"#0.0#")

But that did not work. Can someone give me a hint to the right Direction?

解决方案

The Solution to the Problem is:

=LEFT(IF(ISNUMBER(SEARCH(";";A1));LEFT(A1;FIND(";";A1;1)-1);A1);2)&TEXT(--MID(IF(ISNUMBER(SEARCH(";";A1));LEFT(A1;FIND(";";A1;1)-1);A1);3;FIND(".";IF(ISNUMBER(SEARCH(";";A1));LEFT(A1;FIND(";";A1;1)-1);A1)&".")-3);"000;@") &TEXT(--MID(IF(ISNUMBER(SEARCH(";";A1));LEFT(A1;FIND(";";A1;1)-1);A1);FIND(".";IF(ISNUMBER(SEARCH(";";A1));LEFT(A1;FIND(";";A1;1)-1);A1)&".")+1;99);"000;@")

这篇关于在Excel中使用结构化引用对AlphaNumeric进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！