| 1 | package calc |
| 2 | |
| 3 | import ( |
| 4 | "strconv" |
| 5 | "strings" |
| 6 | ) |
| 7 | |
| 8 | // fn_text_find.go: VALUE, SUBSTITUTE, FIND and SEARCH. |
| 9 | // |
| 10 | // The four the slice spec named and the first pass cut for time. Split |
| 11 | // from fn_text.go rather than added to it: that file is at 166 lines and |
| 12 | // these four bring their own conventions — a 1-BASED character index |
| 13 | // that is Excel's and nothing else in this package uses, and a |
| 14 | // case-sensitivity split that is the only difference between two of |
| 15 | // them. |
| 16 | // |
| 17 | // FIND and SEARCH differ in exactly two ways, and both are easy to get |
| 18 | // backwards: FIND is case-SENSITIVE and takes no wildcards; SEARCH is |
| 19 | // case-insensitive and does. They are written as one body with two |
| 20 | // parameters so the difference is one line rather than two functions |
| 21 | // that drift. |
| 22 | |
| 23 | func init() { |
| 24 | Register("VALUE", fnValue) |
| 25 | Register("SUBSTITUTE", fnSubstitute) |
| 26 | Register("FIND", fnFind) |
| 27 | Register("SEARCH", fnSearch) |
| 28 | } |
| 29 | |
| 30 | // fnValue implements VALUE(text): the number a string represents. |
| 31 | // |
| 32 | // Excel returns #VALUE! for text that is not a number, which is the |
| 33 | // whole point of the function — it is what somebody wraps an imported |
| 34 | // column in to find the rows that did not parse. A number passed |
| 35 | // straight through comes back unchanged rather than being reformatted. |
| 36 | func fnValue(args []Value) Value { |
| 37 | if len(args) != 1 { |
| 38 | return errValue(ErrValue) |
| 39 | } |
| 40 | switch args[0].Kind { |
| 41 | case KindErr: |
| 42 | return args[0] |
| 43 | case KindNum: |
| 44 | return args[0] |
| 45 | case KindEmpty: |
| 46 | // Excel's VALUE("") is #VALUE!, and a blank cell coerces the |
| 47 | // same way. Zero would be a number somebody acts on. |
| 48 | return errValue(ErrValue) |
| 49 | } |
| 50 | s := strings.TrimSpace(toStr(args[0])) |
| 51 | // A trailing percent is the one piece of formatting Excel reads |
| 52 | // here, because "50%" is what a person copies out of a report. |
| 53 | if pct := strings.TrimSuffix(s, "%"); pct != s { |
| 54 | n, err := strconv.ParseFloat(strings.TrimSpace(pct), 64) |
| 55 | if err != nil { |
| 56 | return errValue(ErrValue) |
| 57 | } |
| 58 | return Value{Kind: KindNum, Num: n / 100} |
| 59 | } |
| 60 | n, err := strconv.ParseFloat(s, 64) |
| 61 | if err != nil { |
| 62 | return errValue(ErrValue) |
| 63 | } |
| 64 | return Value{Kind: KindNum, Num: n} |
| 65 | } |
| 66 | |
| 67 | // fnSubstitute implements SUBSTITUTE(text, old, new, [instance]). |
| 68 | // |
| 69 | // Replaces every occurrence, or only the nth when instance is given. |
| 70 | // Case-SENSITIVE, unlike almost everything else in Excel's text family |
| 71 | // — SUBSTITUTE matches exactly and REPLACE works by position, and |
| 72 | // neither of them is the case-insensitive one. |
| 73 | func fnSubstitute(args []Value) Value { |
| 74 | if len(args) < 3 || len(args) > 4 { |
| 75 | return errValue(ErrValue) |
| 76 | } |
| 77 | for _, a := range args { |
| 78 | if a.Kind == KindErr { |
| 79 | return a |
| 80 | } |
| 81 | } |
| 82 | text, old, replacement := toStr(args[0]), toStr(args[1]), toStr(args[2]) |
| 83 | // Excel returns the text unchanged when old is empty, rather than |
| 84 | // splicing new between every character, which is what a naive |
| 85 | // Replace does and what makes SUBSTITUTE(A1,"","x") a very long |
| 86 | // string instead of a no-op. |
| 87 | if old == "" { |
| 88 | return Value{Kind: KindStr, Str: text} |
| 89 | } |
| 90 | if len(args) == 3 { |
| 91 | return Value{Kind: KindStr, Str: strings.ReplaceAll(text, old, replacement)} |
| 92 | } |
| 93 | nth, ek := toNumber(args[3]) |
| 94 | if ek != ErrNone { |
| 95 | return errValue(ek) |
| 96 | } |
| 97 | want := int(nth) |
| 98 | if want < 1 { |
| 99 | return errValue(ErrValue) |
| 100 | } |
| 101 | // Walk occurrences rather than splitting: a split loses which |
| 102 | // separator matched when old and new overlap. |
| 103 | at := 0 |
| 104 | for i := 1; ; i++ { |
| 105 | j := strings.Index(text[at:], old) |
| 106 | if j < 0 { |
| 107 | return Value{Kind: KindStr, Str: text} |
| 108 | } |
| 109 | j += at |
| 110 | if i == want { |
| 111 | return Value{Kind: KindStr, Str: text[:j] + replacement + text[j+len(old):]} |
| 112 | } |
| 113 | at = j + len(old) |
| 114 | } |
| 115 | } |
| 116 | |
| 117 | func fnFind(args []Value) Value { return findIn(args, false) } |
| 118 | func fnSearch(args []Value) Value { return findIn(args, true) } |
| 119 | |
| 120 | // findIn is FIND and SEARCH. |
| 121 | // |
| 122 | // The returned index is 1-BASED and counts RUNES, not bytes. Both halves |
| 123 | // matter: 1-based because that is what Excel returns and what MID's own |
| 124 | // argument expects, and runes because a byte index into a cell holding |
| 125 | // "café" would put MID's cut inside a character. |
| 126 | // |
| 127 | // Not found is #VALUE!, which is Excel's answer and is why |
| 128 | // `=IFERROR(FIND(…),0)` is such a common shape — the error is the |
| 129 | // signal, so returning 0 instead would break every formula written |
| 130 | // around it. |
| 131 | func findIn(args []Value, fold bool) Value { |
| 132 | if len(args) < 2 || len(args) > 3 { |
| 133 | return errValue(ErrValue) |
| 134 | } |
| 135 | for _, a := range args { |
| 136 | if a.Kind == KindErr { |
| 137 | return a |
| 138 | } |
| 139 | } |
| 140 | needle, hay := toStr(args[0]), toStr(args[1]) |
| 141 | start := 1 |
| 142 | if len(args) == 3 { |
| 143 | n, ek := toNumber(args[2]) |
| 144 | if ek != ErrNone { |
| 145 | return errValue(ek) |
| 146 | } |
| 147 | start = int(n) |
| 148 | } |
| 149 | r := []rune(hay) |
| 150 | if start < 1 || start > len(r)+1 { |
| 151 | return errValue(ErrValue) |
| 152 | } |
| 153 | from := r[start-1:] |
| 154 | |
| 155 | if fold { |
| 156 | // SEARCH takes Excel's wildcards, and reuses fn_cond.go's |
| 157 | // matcher rather than growing a second one — a * that means |
| 158 | // something different in two places is the bug that class of |
| 159 | // duplication always produces. |
| 160 | if idx, ok := searchWildcard(needle, from); ok { |
| 161 | return Value{Kind: KindNum, Num: float64(start + idx)} |
| 162 | } |
| 163 | return errValue(ErrValue) |
| 164 | } |
| 165 | idx := strings.Index(string(from), needle) |
| 166 | if idx < 0 { |
| 167 | return errValue(ErrValue) |
| 168 | } |
| 169 | // Byte offset back to a rune offset. |
| 170 | return Value{Kind: KindNum, Num: float64(start + len([]rune(string(from)[:idx])))} |
| 171 | } |
| 172 | |
| 173 | // searchWildcard finds the first rune offset in hay at which needle |
| 174 | // matches, case-insensitively, honouring * and ?. |
| 175 | // |
| 176 | // An empty needle matches at the start, which is Excel's answer and the |
| 177 | // one a loop starting at length 1 would miss. |
| 178 | func searchWildcard(needle string, hay []rune) (int, bool) { |
| 179 | pat := strings.ToUpper(needle) |
| 180 | if pat == "" { |
| 181 | return 0, true |
| 182 | } |
| 183 | for i := 0; i <= len(hay); i++ { |
| 184 | for j := i; j <= len(hay); j++ { |
| 185 | if wildcardMatch(pat, strings.ToUpper(string(hay[i:j]))) { |
| 186 | return i, true |
| 187 | } |
| 188 | } |
| 189 | } |
| 190 | return 0, false |
| 191 | } |
| 192 | |
amadan