1

Excel「&」用於不同格式

20171201

=IF(B5<10,B5,LOOKUP(B5,{10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31},{"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Z"}))

A和C為通用格式、B和D為自訂格式，用&來連接起來後B跟D就會變成不是我要的

F1=A1&Text(B1,"yymd")&C1&Text(D1,"00000")

p04210421 iT邦新手 5 級 ‧ 2017-11-29 16:46:32 檢舉

3 個回答

0
froce
iT邦高手 2 級 ‧ 2017-11-29 15:53:27

1.所有的儲存格請用文字存。這樣才不會因為日期轉換跑掉。
2.B的轉換公式：=DEC2HEX(VALUE(LEFT(B1,2)))&DEC2HEX(VALUE(MID(B1,3,2)))&DEC2HEX(VALUE(RIGHT(B1,2)))

p04210421 iT邦新手 5 級 ‧ 2017-11-29 16:39:57 檢舉

16 =TEXT(B1,LEFT(B1,2))

0
erwinho
iT邦新手 5 級 ‧ 2017-11-29 16:37:04

D的部份改為 Text(D1, "00000")

p04210421 iT邦新手 5 級 ‧ 2017-11-29 16:47:31 檢舉

0

iT邦超人 1 級 ‧ 2017-11-29 18:08:22

A,C,D都有人回答了

``````H2=1
H3=2
H4=3
H5=4
H6=5
H7=6
H8=7
H9=8
H10=9
H11=A
H12=B
H13=C
H14=D
H15=E
H16=F
H17=G
H18=H
H19=J
H20=K
H21=L
H22=M
H23=N
H24=P
H25=Q
H26=R
H27=S
H28=T
H29=U
H30=V
H31=W
H32=X

F1=OFFSET(\$H\$1,MID(B1,1,2),0)&OFFSET(\$H\$1,MID(B1,3,2),0)&OFFSET(\$H\$1,MID(B1,5,2),0)
``````
erwinho iT邦新手 5 級 ‧ 2017-11-30 10:01:31 檢舉

=MID("123456789ABCDEFGHIJKLMNOPQRSTUVWX",(B1,1,2),1)&
MID("123456789ABCDEFGHIJKLMNOPQRSTUVWX",(B1,3,2),1) &
MID("123456789ABCDEFGHIJKLMNOPQRSTUVWX",(B1,5,2),1)