旮旯里的小老鼠 发表于 2021-11-17 11:24:00

单元格中文本一至九，快速替换为数字1-9

我想把杭州一办替换成杭州1办，北京九办替换成北京9办
=SUBSTITUTE(A1,{"一","二","三","四","五","六","七","八","九","十"},{"1","2","3","4","5","6","7","8","9","10"})
异想天开写了个公式翻车了==
请问大佬，除了CTRL+H，如何快速将单元格中文本一至九，快速替换为数字1-9呢？

昌恒 发表于 2021-11-18 00:35:27

=LOOKUP(,0/(SUBSTITUTE(A1,{"一","二","三","四","五","六","七","八","九","十"},{"1","2","3","4","5","6","7","8","9","10"})<>B4),SUBSTITUTE(A1,{"一","二","三","四","五","六","七","八","九","十"},{"1","2","3","4","5","6","7","8","9","10"}))在你的基础上改一下

费小娥 发表于 2021-11-23 03:26:45

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"一",1),"二",2),"三",3),"四",4),"五",5),"六",6),"七",7),"八",8),"九",9),"十",10)

郭智天 发表于 2021-11-30 12:55:28

=REPLACE(A1,SUM(IFERROR(FIND({"一","二","三","四","五","六","七","八","九"},A1),0)),1,MATCH(1=1,ISNUMBER(FIND({"一","二","三","四","五","六","七","八","九"},A1)),))

蘭陵笑笑生 发表于 2021-12-5 13:38:44

杭州一办替换没问题，但是二、三、四……都提示#VALUE!，不过还是要谢谢您

生出来就没打算活着回去 发表于 2021-12-10 22:14:45

=CONCAT(IFERROR(FIND(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),"一二三四五六七八九十"),MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)))数组最多10b5=杭州十办如果是地名有数字就不好办了香港九龙？贵州六盘水？

无商不奸 发表于 2021-12-15 00:46:57

上传附件应该能解决

每天好好的 发表于 2021-12-18 14:10:16

=SUMPRODUCT((A2={"一","二","三","四","五","六","七","八","九"})*{1,2,3,4,5,6,7,8,9})

李成明 发表于 2021-12-19 09:45:19

=LOOKUP(,0/(FIND(TEXT(ROW($1:$9),""),A2)),SUBSTITUTE(A2,TEXT(ROW($1:$9),""),ROW($1:$9)))

鹿先森 发表于 2021-12-20 11:35:02

B1{=REPLACE(A1,LOOKUP(9,FIND(MID("一二三四五六七八九",ROW($1:$9),1),A1)),1,MATCH(,0/FIND(MID("一二三四五六七八九",ROW($1:$9),1),A1),))
另 解 B1 右拉到J1 (J1是结果)=SUBSTITUTE(A1,MID("一二三四五六七八九",COLUMN(A1),1),COLUMN(A1))
页: [1] 2 3 4
查看完整版本: 单元格中文本一至九，快速替换为数字1-9