作者:乔山办公网日期:
返回目录:excel表格制作
点下面链接,看详细说明
文本合并公式
EXCEL中文本排序是按首字符进行来排序的,如要自以其它位置的字符进行排序,先要将其提取出来,再以该列进行排序,如提取第二字符,公式用:=MID(A2,2,1),并下拉填充;百
此时辅助列成选中状态,选择:开始--排序和筛选度--升序;
在跳出的知排序提醒中选择“扩展选定区域”;
点击排序后即得到需要的效果,再删除辅助列即道可。
添加一个辅助列,输入以下公式,然后向下填充
=--LEFT(A1,3)
以辅助列进行排序即可。
假定A1单元格存放需要排序e799bee5baa6e997aee7ad94e58685e5aeb9365的字符串,假定字符串长度小于等于10(大于10公式类推),用如下数组公式(用Shift+Ctrl+Enter输入)可对存放在A1单元格的字符串排序:
=CONCATENATE(IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),1),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),2),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),3),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),4),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),5),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),6),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),7),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),8),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),9),""),IFERROR(INDEX(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),10),""))