Question

excel中试题选项用$；$隔开，如何在每个选项前分别填入ABCD，也就是将A2内容变成B2形式？

Answer 1

excel中试题选项$;$隔开，在每个选项前分别填入ABCD，把A2单元格内容变成B2单元格的形式，这个可以使用SUBSTITUTE文本替换函数公式来完成。
在B2单元格输入公式并向下填充，如下：
="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$;$"," B",1),"$;$"," C",1),"$;$"," D")
公式及效果图如下：

SUBSTITUTE函数参数说明：
1、需要被替换的文本，也就是A2单元格；
2、需要被替换的字符串，也就是$;$；
3、需要换成新的字符串，也就是（空格）+BCD这些；
4、替换的序号，因为有三组，而每次替换后都是替换的第一组，所以为1。

Answer 2

在B2中输入或复制粘贴下列公式
="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$;$"," B",1),"$;$"," C",1),"$;$"," D",1)
或
="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$;$"," B",1),"$;$"," C",1),"$;$"," D")
或
="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$;$"," D",3),"$;$"," C",2),"$;$"," B",1)

或
="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$;$"," D",3),"$;$"," C",2),"$;$"," B")
下拉填充