1package sheet23import "testing"45func TestTextFunctions(t *testing.T) {6 checkFormulas(t, fixture(t), []fnCase{7 {`=CONCATENATE("a", 1, TRUE)`, txt("a1TRUE")},8 {"=CONCATENATE(D1:D3)", txt("AppleBananaCherry")},9 {`=CONCAT(A1, "x")`, txt("10x")},10 {`=TEXTJOIN(", ", TRUE, A3:A5)`, txt("30, apple")},11 {`=TEXTJOIN("-", FALSE, A3:A5)`, txt("30-apple-")},12 {`=LEFT("hello", 2)`, txt("he")},13 {`=LEFT("hello")`, txt("h")},14 {`=LEFT("hi", 10)`, txt("hi")},15 {`=LEFT("hi", -1)`, ErrValue},16 {`=RIGHT("hello", 3)`, txt("llo")},17 {`=MID("spreadsheet", 7, 5)`, txt("sheet")},18 {`=MID("abc", 5, 2)`, txt("")},19 {`=MID("abc", 0, 2)`, ErrNum}, // as Sheets; Excel says #VALUE!20 {`=LEN("héllo")`, num(5)},21 {"=LEN(A1)", num(2)},22 {`=UPPER("abc")`, txt("ABC")},23 {`=LOWER("ABC")`, txt("abc")},24 {`=PROPER("hello wORLD it's")`, txt("Hello World It'S")},25 {`=TRIM(" a b ")`, txt("a b")},26 {`=SUBSTITUTE("a-b-c", "-", "+")`, txt("a+b+c")},27 {`=SUBSTITUTE("a-b-c", "-", "+", 2)`, txt("a-b+c")},28 {`=SUBSTITUTE("a-b-c", "-", "+", 5)`, txt("a-b-c")},29 {`=SUBSTITUTE("abc", "", "x")`, txt("abc")},30 {`=SUBSTITUTE("abc", "b", "x", 0)`, ErrValue},31 {`=REPLACE("abcdef", 2, 3, "X")`, txt("aXef")},32 {`=REPLACE("abc", 10, 1, "X")`, txt("abcX")},33 {`=REPLACE("abc", 0, 1, "X")`, ErrValue},34 {`=FIND("b", "abcb")`, num(2)},35 {`=FIND("b", "abcb", 3)`, num(4)},36 {`=FIND("B", "abc")`, ErrValue}, // case-sensitive37 {`=FIND("", "abc")`, num(1)},38 {`=FIND("a", "abc", 5)`, ErrValue},39 {`=SEARCH("B", "abc")`, num(2)},40 {`=SEARCH("b?d", "abcbxd")`, num(4)},41 {`=SEARCH("c*", "abc")`, num(3)},42 {`=SEARCH("z", "abc")`, ErrValue},43 {`=TEXT(1234.567, "$#,##0.00")`, txt("$1,234.57")},44 {`=TEXT(0.256, "0.0%")`, txt("25.6%")},45 {`=TEXT(F1, "yyyy-mm-dd")`, txt("2026-09-26")},46 {`=TEXT(F1, "dddd")`, txt("Saturday")},47 {`=TEXT(F3, "h:mm AM/PM")`, txt("2:30 PM")},48 {`=TEXT("12", "000")`, txt("012")},49 {`=TEXT("abc", "000")`, txt("abc")},50 {`=VALUE("$1,200")`, num(1200)},51 {`=VALUE("12%")`, num(0.12)},52 {`=VALUE("2026-09-26")`, num(46291)},53 {`=VALUE("")`, num(0)},54 {`=VALUE("abc")`, ErrValue},55 {"=VALUE(A1)", num(10)},56 {`=REPT("ab", 3)`, txt("ababab")},57 {`=REPT("ab", -1)`, ErrValue},58 {`=EXACT("a", "A")`, boolean(false)},59 {`=EXACT("a", "a")`, boolean(true)},60 {`=EXACT(10, "10")`, boolean(true)},61 })62}