[已解决]格式转换

李建军

更具A1的数据 转换成B列效果   如附件 ~ 如果是技巧的话麻烦老师有个动图 便是极好 ！~~  感谢先！！~~

最佳答案

月排行榜 / 总排行榜

baksy

2014-12-7 19:42

=IF(ROW()=2,LEFT(A1,FIND("-",A1)-1),IF(COUNTIF($B$1:B1,MID(A$1,FIND("-",A$1)+1,99)),"",TEXT(EDATE(LEFT(B1,LEN(B1)-2),(RIGHT(B1)="下")*1),"e/m")&"/"&LOOKUP(RIGHT(B1),{"上","下","中"},{"中","上","下"})))

跳转到最佳答案楼层

baksy

B2公式，下拉；
=IF(ROW()=2,LEFT(A1,FIND("-",A1)-1),IF(COUNTIF($B$1:B1,MID(A$1,FIND("-",A$1)+1,99)),"",IF(RIGHT(B1)<>"下",LEFT(B1,LEN(B1)-1)&LOOKUP(RIGHT(B1),{"上","下","中"},{"中","上","下"}),TEXT(EDATE(LEFT(B1,LEN(B1)-2),1),"e/m")&"/"&"上")))

baksy

=IF(ROW()=2,LEFT(A1,FIND("-",A1)-1),IF(COUNTIF($B$1:B1,MID(A$1,FIND("-",A$1)+1,99)),"",TEXT(EDATE(LEFT(B1,LEN(B1)-2),(RIGHT(B1)="下")*1),"e/m")&"/"&LOOKUP(RIGHT(B1),{"上","下","中"},{"中","上","下"})))

李建军

baksy 发表于 2014-12-7 19:42
=IF(ROW()=2,LEFT(A1,FIND("-",A1)-1),IF(COUNTIF($B$1:B1,MID(A$1,FIND("-",A$1)+1,99)),"",TEXT(EDATE(LE ...

流弊呀！！！！！  刚被一伙计打击非vba搞不定。。。膜拜！！！！！！！{:2812:}

刀羊

用公式是极好，技巧麻烦。

pipe_201320083

用这个公式，应该可以弄好
=IF(ROW()=2,LEFT(A1,FIND("-",A1)-1),IF(COUNTIF($B$1:B1,MID(A$1,FIND("-",A$1)+1,99)),"",TEXT(EDATE(LEFT(B1,LEN(B1)-2),(RIGHT(B1)="下")*1),"e/m")&"/"&LOOKUP(RIGHT(B1),{"上","下","中"},{"中","上","下"})))