CONCATENATE 公式問題

SEBI7248
個人積分：1932分
文章編號：90090532

1932分

樓主

2024-08-02 11:34

目前公式如下
=CONCATENATE('R產線key in'!A3&"-"&'R產線key in'!B3," ",'R產線key in'!A4&"-"&'R產線key in'!B4," ",'R產線key in'!A5&"-"&'R產線key in'!B5," ",'R產線key in'!A6&"-"&'R產線key in'!B6," ",'R產線key in'!A7&"-"&'R產線key in'!B7," ",'R產線key in'!A8&"-"&'R產線key in'!B8," ",'R產線key in'!A9&"-"&'R產線key in'!B9," ",'R產線key in'!A10&"-"&'R產線key in'!B10)

但未輸入的空格會顯示- ,如果拿掉- 則時間將不會顯示09:00-09:10 →會變成09:00 09:10
想請問公式要如何修改才能將沒有輸入數值的單元格不顯示0 或 -

CONCATENATE 公式問題

2024-08-02 11:34 發佈

文章關鍵字 CONCATENATE 問題公式

caoq

caoq
個人積分：4266分
文章編號：90090693

4266分

2樓

2024-08-02 11:55

SEBI7248 wrote:
目前公式如下 =CONCATENATE...(恕刪)

=CONCATENATE(
IF('R產線key in'!A3&"-"&'R產線key in'!B3="-","",'R產線key in'!A3&"-"&'R產線key in'!B3&" "),
IF('R產線key in'!A4&"-"&'R產線key in'!B4="-","",'R產線key in'!A4&"-"&'R產線key in'!B4&" "),
IF('R產線key in'!A5&"-"&'R產線key in'!B5="-","",'R產線key in'!A5&"-"&'R產線key in'!B5&" "),
IF('R產線key in'!A6&"-"&'R產線key in'!B6="-","",'R產線key in'!A6&"-"&'R產線key in'!B6&" "),
IF('R產線key in'!A7&"-"&'R產線key in'!B7="-","",'R產線key in'!A7&"-"&'R產線key in'!B7&" "),
IF('R產線key in'!A8&"-"&'R產線key in'!B8="-","",'R產線key in'!A8&"-"&'R產線key in'!B8&" "),
IF('R產線key in'!A9&"-"&'R產線key in'!B9="-","",'R產線key in'!A9&"-"&'R產線key in'!B9&" "),
IF('R產線key in'!A10&"-"&'R產線key in'!B10="-","",'R產線key in'!A10&"-"&'R產線key in'!B10))

SEBI7248 樓主

感謝指導

2024-08-02 13:18

Yaude Huang

Yaude Huang
個人積分：5284分
文章編號：90132417

5284分

3樓

2024-08-10 16:23

如果你有使用365，給你參考

用filter過濾你不要的空白欄衛，用textjoin組合字串，
最前面的TOROW({"-"," "})，是要按照"-"，" "依序組合字串，因為需要array，所以用torow動態產生

這樣就不用一直隨著資料增加，要一直修改內容，A3 A4 A5 .... A9

=TEXTJOIN(TOROW({"-"," "}),TRUE,FILTER(A:B,(A:A<>"")*(B:B<>"")))

從 APP 打開