特定日期转换

ph378 · 发表于 2019-12-6 21:46

如图，不用VAB，如何实现？

祝洪忠- · 发表于 2019-12-6 22:06

替换.zip (7.88 KB, 下载次数: 7)

ph378 · 发表于 2019-12-6 22:17

本帖最后由 ph378 于 2019-12-6 22:52 编辑

祝洪忠- 发表于 2019-12-6 22:06

谢了！

有个问题

换了个值25后如下图
=SUBSTITUTE(SUBSTITUTE(A9,LOOKUP(1,-FIND(G$2:G$25,A9),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A9),ROW($1:$25))),IFERROR(LOOKUP(1,-FIND(G$2:G$25,SUBSTITUTE(A9,LOOKUP(1,-FIND(G$2:G$25,A9),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A9),ROW($1:$20)))),G$2:G$25),""),IFERROR(LOOKUP(1,-FIND(G$2:G$25,SUBSTITUTE(A9,LOOKUP(1,-FIND(G$2:G$25,A9),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A9),ROW($1:$25)))),ROW($1:$25)),""))

第二次改了又个值25=SUBSTITUTE(SUBSTITUTE(A10,LOOKUP(1,-FIND(G$2:G$25,A10),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A10),ROW($1:$25))),IFERROR(LOOKUP(1,-FIND(G$2:G$25,SUBSTITUTE(A10,LOOKUP(1,-FIND(G$2:G$25,A10),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A10),ROW($1:$25)))),G$2:G$25),""),IFERROR(LOOKUP(1,-FIND(G$2:G$25,SUBSTITUTE(A10,LOOKUP(1,-FIND(G$2:G$25,A10),G$2:G$25),LOOKUP(1,-FIND(G$2:G$25,A10),ROW($1:$25)))),ROW($1:$25)),""))

可能25限制了，改为26后正常

=SUBSTITUTE(SUBSTITUTE(A11,LOOKUP(1,-FIND(G$2:G$26,A11),G$2:G$26),LOOKUP(1,-FIND(G$2:G$26,A11),ROW($1:$26))),IFERROR(LOOKUP(1,-FIND(G$2:G$26,SUBSTITUTE(A11,LOOKUP(1,-FIND(G$2:G$26,A11),G$2:G$26),LOOKUP(1,-FIND(G$2:G$26,A11),ROW($1:$26)))),G$2:G$26),""),IFERROR(LOOKUP(1,-FIND(G$2:G$26,SUBSTITUTE(A11,LOOKUP(1,-FIND(G$2:G$26,A11),G$2:G$26),LOOKUP(1,-FIND(G$2:G$26,A11),ROW($1:$26)))),ROW($1:$26)),""))

YZC51 · 发表于 2019-12-6 23:26

请参考
=MATCH(A38,TEXT(ROW($1:$100),"[<32][dbnum1]d;[dbnum1]"),)
数组

静听溪流 · 发表于 2019-12-7 00:39

数组公式
=IFERROR(INDEX(ROW($1:$99),MATCH(1=1,LEFT(A2,FIND("年",A2)-1)=SUBSTITUTE(TEXT(ROW($1:$99),"[DBNum1]"),"一十","十"),0),)&"年","")&IFERROR(INDEX(ROW($1:$99),MATCH(1=1,MID(A2,IFERROR(FIND("年",A2)+1,1),10)=SUBSTITUTE(TEXT(ROW($1:$99),"[DBNum1]"),"一十","十")&"个月",0),)&"个月","")

ph378 · 发表于 2019-12-7 19:41

本帖最后由 ph378 于 2019-12-7 20:20 编辑

YZC51 发表于 2019-12-6 23:26
请参考
=MATCH(A38,TEXT(ROW($1:$100),"[

谢了！
测试不行
！

YZC51 · 发表于 2019-12-7 20:16

ph378 发表于 2019-12-7 19:41
谢了！
测试不行
超级版主方法可行。只需改几个数！

=IF(ISERR(FIND("年",A2)),"",MATCH(LEFT(A2,FIND("年",A2)-1),TEXT(ROW($1:$100),"[<32][dbnum1]d;[dbnum1]"),)&"年")&IF(ISERR(FIND("个月",A2)),"",MATCH(SUBSTITUTE(MID(A2,FIND("年",A2)+1,99),"个月",""),TEXT(ROW($1:$100),"[<32][dbnum1]d;[dbnum1]"),)&"个月")

数组

YZC51 · 发表于 2019-12-7 20:22

本帖最后由 YZC51 于 2019-12-7 21:32 编辑

可以自己变通下

中文数字转阿拉伯数字-.zip (8.41 KB, 下载次数: 5)

ph378 · 发表于 2019-12-7 20:25

本帖最后由 ph378 于 2019-12-7 20:28 编辑

YZC51 发表于 2019-12-7 20:16
=IF(ISERR(FIND("年",A2)),"",MATCH(LEFT(A2,FIND("年",A2)-1),TEXT(ROW($1:$100),"[

谢了！
无“年”的不行

YZC51 · 发表于 2019-12-7 20:27

参看图片

		自动登录	找回密码
密码			免费注册

[求助] 特定日期转换

评分

评分

最新热点 /1