求助:利用公式将excel单元格中的字符串重新排序?

发布网友 发布时间：2022-04-23 14:40

我来回答

共2个回答

热心网友 时间：2023-10-19 00:40

C2单元格输入公式=IF(AND(ISNUMBER(FIND("A",A2)),ISNUMBER(FIND("B",A2))),LEFT(A2,1)&LEFT(B2,1)&RIGHT(A2,1)&RIGHT(B2,1),IF(AND(ISNUMBER(FIND("A",A2)),ISERR(FIND("B",A2))),LEFT(A2,1)&LEFT(B2,1)&RIGHT(B2,1)&RIGHT(A1,1),IF(AND(ISERR(FIND("A",A2)),ISNUMBER(FIND("B",A2))),LEFT(B2,1)&LEFT(A2,1)&RIGHT(A2,1)&RIGHT(B2,1),IF(AND(ISERR(FIND("A",A2)),ISERR(FIND("B",A2))),LEFT(B2,1)&LEFT(A2,1)&RIGHT(B2,1)&RIGHT(A2,1),""))))

下拉公式。 如图：

热心网友 时间：2023-10-19 00:40

若都是字母c2=CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,1),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,2),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,3),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,4),1)*1000,0))数组公式，同时按ctrl+shift+回车三键结束，下拉，本公式通用所有字母组合

热心网友 时间：2023-10-19 00:40

C2单元格输入公式=IF(AND(ISNUMBER(FIND("A",A2)),ISNUMBER(FIND("B",A2))),LEFT(A2,1)&LEFT(B2,1)&RIGHT(A2,1)&RIGHT(B2,1),IF(AND(ISNUMBER(FIND("A",A2)),ISERR(FIND("B",A2))),LEFT(A2,1)&LEFT(B2,1)&RIGHT(B2,1)&RIGHT(A1,1),IF(AND(ISERR(FIND("A",A2)),ISNUMBER(FIND("B",A2))),LEFT(B2,1)&LEFT(A2,1)&RIGHT(A2,1)&RIGHT(B2,1),IF(AND(ISERR(FIND("A",A2)),ISERR(FIND("B",A2))),LEFT(B2,1)&LEFT(A2,1)&RIGHT(B2,1)&RIGHT(A2,1),""))))

下拉公式。 如图：

热心网友 时间：2023-10-19 00:40

若都是字母c2=CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,1),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,2),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,3),1)*1000,0))&CHAR(ROUNDUP(MOD(SMALL(MATCH(MID(A2&B2,ROW($1:$4),1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)+CODE(MID(A2&B2,ROW($1:$4),1))/1000,4),1)*1000,0))数组公式，同时按ctrl+shift+回车三键结束，下拉，本公式通用所有字母组合