对于这个问题,不同的人会有不同的方法。正所谓:“条条大路通罗马。”那么你会用以下哪种方法呢? 先来看下提取数字的公式:在B3单元格输入下面的公式,按ctrl+shift+enter三键结束,向下填充。
MID("a"&A3,ROW($1:$10),1)这部分,先在A3前面连接一个"a",然后用mid函数分别从第1位提取一个字符,第2位提取一个字符,。。。一直到第10位提取一个字符。结果为{"a";"P";"Q";"1";"5";"4";"A";"B";"";""}。 TEXT(MID("a"&A3,ROW($1:$10),1),"0;;0;\1\0")这部分,是用text将上一步结果中的字母和空文本转为10,数字还保持原样不变。结果为{"10";"10";"10";"1";"5";"4";"10";"10";"10";"10"}。 16^(10-ROW($1:$10))这部分就是16的{9;8;7;6;5;4;3;2;1;0}次方。然后用text的部分和这部分相乘就是一个关键。就是将一个16进制的数转换为10进制的数,当然外面还得套个sum。 所以这个题的思路就是进制的转换。如果对进制转换不太了解的话,这个公式还是不好理解的。当然我对进制的理解也是很初浅的。下面就举几个例子:
SUM(TEXT(MID("a"&A3,ROW($1:$10),1),"0;;0;\1\0")*16^(10-ROW($1:$10)))这部分就相当于将16进制的"AAA154AAAA"转为10进制的732851120810。这个数字已经很大了,如果进制更大,或者字符串更长的话,得到的结果可能都超过15位了,那么精度就可能会丢失了。 然后用base函数将10进制的732851120810再转换为16进制的"AAA154AAAA",最后用substitute将"A"替换为空就得到了最后的数字。 提取字母
这个公式的思路和之前是一样的,只不过用到36进制。 MID(A3,ROW($1:$9),1)这部分,用mid函数将每个字符单独提取出来,结果为{"P";"Q";"1";"5";"4";"";"";"";""}。 TEXT(MID(A3,ROW($1:$9),1),";;;@")这部分,用text函数将上一步结果中的数字转为空文本,字母和空文本保持不变,结果为{"P";"Q";"";"";"";"";"";"";""}。 DECIMAL(TEXT(MID(A3,ROW($1:$9),1),";;;@"),36)这部分,用decimal函数将字母转化为10进制的数字,可以将字母看做36进制的数。36进制是0-9,A-Z共36个字符。A相当于10,Z相当于35。而且decimal正好可以把空文本转为0,这一步的结果为{25;26;0;0;0;0;0;0;0}。 36^(9-ROW($1:$9))这部分就是36的{8;7;6;5;4;3;2;1;0}次方。decimal部分和这部分相乘,就相当于将36进制的数(这个数是什么,你应该能想出来)转为10进制的数,当然还要用sum把它们加起来才是10进制的数。 SUM(DECIMAL(TEXT(MID(A3,ROW($1:$9),1),";;;@"),36)*36^(9-ROW($1:$9)))这部分得到的10进制的数为72565215952896。好长一个数。 然后用base函数将10进制的72565215952896转为36进制的数,结果为"PQ0000000"。到了这里,你应该就看出来了,实际就是将0-9的数字全变为0。 最后用substitute将0替换为空,得到字母的结果。 总结一下: 提取思路是利用进制的转换。base将10进制的数转为其他进制的数。decimal将其他进制的数转为10进制的数。 提取数字的时候,要将字母全部转为A,最后用替换函数将A替换掉。提取字母的时候,要将数字全部转为0,最后用替换函数将0替换掉。中间都是进制转换的过程。将其他进制转为10进制是为了聚合,也就是将数字连在一起,将10进制转为其他进制是为了得到字符串。 https://pan.baidu.com/s/13htKCFv3Zg4XV_bQfc-1Ug |
|