转化内容

xiaomils

请各位大佬帮忙，在B列中输入函数公式（不要用替换），将A列的内容转化为B列，如前三行。
1、规律是“物理”变为“理”，“化学”变化为“化”，“历史”变为“史”，“技术”变为“技”等等。

2、所有单元格里都字顺序按照“理化生政史地技”的顺序排序。比如A1单元格是“生物，物理，技术”，最后弄成“理生技”。谢谢！

hugeinfo

=CONCAT(SUBSTITUTE(SUBSTITUTE(LEFT(TEXTSPLIT(TEXTJOIN(",",,A1),","),1),"历","史"),"物","理"))

夏天的风shh4695

=VLOOKUP(LEFT(A1,2),{"政治","政";"历史","史";"地理","地";"物理","理";"化学","化";"生物","生";"技术","技"},2,0)&VLOOKUP(MID(A1,4,2),{"政治","政";"历史","史";"地理","地";"物理","理";"化学","化";"生物","生";"技术","技"},2,0)&VLOOKUP(RIGHT(A1,2),{"政治","政";"历史","史";"地理","地";"物理","理";"化学","化";"生物","生";"技术","技"},2,0)

w4275

1. =SUBSTITUTE(SUBSTITUTE(CONCAT(MID(A1,{1,2,3}*3-2,1)),"历","史"),"物","理")

复制代码

xiaomils

hugeinfo 发表于 2023-6-17 16:40
=CONCAT(SUBSTITUTE(SUBSTITUTE(LEFT(TEXTSPLIT(TEXTJOIN(",",,A1),","),1),"历","史"),"物","理"))

输入后显示“#NAME?”  ，请问为什么？

xiaomils

w4275 发表于 2023-6-17 16:46

厉害，感谢！！

xiaomils

夏天的风shh4695 发表于 2023-6-17 16:43
=VLOOKUP(LEFT(A1,2),{"政治","政";"历史","史";"地理","地";"物理","理";"化学","化";"生物","生";"技术", ...

感谢大佬，解决。

静听溪流

xiaomils 发表于 2023-6-17 16:48
输入后显示“#NAME?”  ，请问为什么？

说明公式中有某个函数是你的excel版本所不支持的

quqiyuan

=CONCAT(HLOOKUP(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),COLUMN($A:$C)*99-98,99)),IF({1;0},{"物理","化学","生物","地理","技术","政治","历史"},{"理","化","生","地","技","政","史"}),2,))

lpf127

xiaomils 发表于 2023-6-17 16:48
输入后显示“#NAME?”  ，请问为什么？

TEXTSPLIT、TEXTJOIN是Excel365版本的函数，你的Excel版本应该是不支持