Skip to main content

sheetkit_core/formula/functions/
text.rs

1//! Text formula functions: CONCAT, FIND, SEARCH, SUBSTITUTE, REPLACE, REPT, EXACT, T, PROPER.
2
3use crate::cell::CellValue;
4use crate::error::{Error, Result};
5use crate::formula::ast::Expr;
6use crate::formula::eval::{coerce_to_number, Evaluator};
7use crate::formula::functions::conversion::checked_trunc_count;
8use crate::formula::functions::{check_arg_count, eval_text_arg, wildcard_search};
9
10/// Evaluate a text argument, returning its error value from the caller.
11macro_rules! text_arg {
12    ($expr:expr, $ctx:expr) => {
13        match eval_text_arg($expr, $ctx)? {
14            Ok(text) => text,
15            Err(error) => return Ok(error),
16        }
17    };
18}
19
20/// Largest REPLACE start position or length Excel accepts.
21const REPLACE_MAX_ARGUMENT: usize = 32_767;
22
23/// CONCAT(text1, \[text2\], ...) - concatenates multiple values.
24pub fn fn_concat(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
25    check_arg_count("CONCAT", args, 1, 255)?;
26    let mut result = String::new();
27    for arg in args {
28        result.push_str(&text_arg!(arg, ctx));
29    }
30    Ok(CellValue::String(result))
31}
32
33/// FIND(find_text, within_text, \[start_num\]) - case-sensitive search.
34pub fn fn_find(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
35    check_arg_count("FIND", args, 2, 3)?;
36    let find_text = text_arg!(&args[0], ctx);
37    let within_text = text_arg!(&args[1], ctx);
38    let start_value = if args.len() > 2 {
39        coerce_to_number(&ctx.eval_expr(&args[2])?)?
40    } else {
41        1.0
42    };
43    let Some(start_num) = checked_trunc_count(start_value) else {
44        return Ok(CellValue::Error("#VALUE!".to_string()));
45    };
46    let char_count = within_text.chars().count();
47    if start_num < 1 || start_num > char_count.saturating_add(1) {
48        return Ok(CellValue::Error("#VALUE!".to_string()));
49    }
50    let search_in = &within_text[character_position_to_byte_offset(&within_text, start_num)..];
51    match search_in.find(&find_text) {
52        Some(pos) => Ok(CellValue::Number(
53            (start_num + search_in[..pos].chars().count()) as f64,
54        )),
55        None => Ok(CellValue::Error("#VALUE!".to_string())),
56    }
57}
58
59/// SEARCH(find_text, within_text, \[start_num\]) - case-insensitive search with wildcards.
60pub fn fn_search(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
61    check_arg_count("SEARCH", args, 2, 3)?;
62    let find_text = text_arg!(&args[0], ctx);
63    let within_text = text_arg!(&args[1], ctx);
64    let start_value = if args.len() > 2 {
65        coerce_to_number(&ctx.eval_expr(&args[2])?)?
66    } else {
67        1.0
68    };
69    let Some(start_num) = checked_trunc_count(start_value) else {
70        return Ok(CellValue::Error("#VALUE!".to_string()));
71    };
72    let char_count = within_text.chars().count();
73    if start_num < 1 || start_num > char_count.saturating_add(1) {
74        return Ok(CellValue::Error("#VALUE!".to_string()));
75    }
76    let search_in = &within_text[character_position_to_byte_offset(&within_text, start_num)..];
77    match wildcard_search(search_in, &find_text, ctx)? {
78        Some(char_offset) => Ok(CellValue::Number((char_offset + start_num) as f64)),
79        None => Ok(CellValue::Error("#VALUE!".to_string())),
80    }
81}
82
83fn character_position_to_byte_offset(text: &str, position: usize) -> usize {
84    text.char_indices()
85        .nth(position - 1)
86        .map_or(text.len(), |(byte_offset, _)| byte_offset)
87}
88
89/// SUBSTITUTE(text, old_text, new_text, \[instance_num\]) - replaces occurrences.
90pub fn fn_substitute(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
91    check_arg_count("SUBSTITUTE", args, 3, 4)?;
92    let text = text_arg!(&args[0], ctx);
93    let old_text = text_arg!(&args[1], ctx);
94    let new_text = text_arg!(&args[2], ctx);
95    if old_text.is_empty() {
96        return Ok(CellValue::String(text));
97    }
98    if args.len() > 3 {
99        let instance_value = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
100        let Some(instance_num) = checked_trunc_count(instance_value) else {
101            return Ok(CellValue::Error("#VALUE!".to_string()));
102        };
103        if instance_num < 1 {
104            return Ok(CellValue::Error("#VALUE!".to_string()));
105        }
106        let occurrence = text.match_indices(&old_text).nth(instance_num - 1);
107        let replacement_count = usize::from(occurrence.is_some());
108        let output_len = checked_substitute_output_len(
109            text.len(),
110            old_text.len(),
111            new_text.len(),
112            replacement_count,
113        );
114        let Some(output_len) = output_len else {
115            ctx.check_output_bytes(u64::MAX)?;
116            unreachable!("an overflowing substitute result must exceed the formula budget");
117        };
118        ctx.check_output_bytes(u64::try_from(output_len).unwrap_or(u64::MAX))?;
119        let Some((position, _)) = occurrence else {
120            return Ok(CellValue::String(text));
121        };
122        let mut result = String::new();
123        result
124            .try_reserve_exact(output_len)
125            .map_err(|_| Error::Internal("substitute allocation failed".to_string()))?;
126        result.push_str(&text[..position]);
127        result.push_str(&new_text);
128        result.push_str(&text[position + old_text.len()..]);
129        Ok(CellValue::String(result))
130    } else {
131        let replacement_count = text.match_indices(&old_text).count();
132        let Some(output_len) = checked_substitute_output_len(
133            text.len(),
134            old_text.len(),
135            new_text.len(),
136            replacement_count,
137        ) else {
138            ctx.check_output_bytes(u64::MAX)?;
139            unreachable!("an overflowing substitute result must exceed the formula budget");
140        };
141        ctx.check_output_bytes(u64::try_from(output_len).unwrap_or(u64::MAX))?;
142        let mut result = String::new();
143        result
144            .try_reserve_exact(output_len)
145            .map_err(|_| Error::Internal("substitute allocation failed".to_string()))?;
146        let mut copied_until = 0;
147        for (position, matched) in text.match_indices(&old_text) {
148            result.push_str(&text[copied_until..position]);
149            result.push_str(&new_text);
150            copied_until = position + matched.len();
151        }
152        result.push_str(&text[copied_until..]);
153        Ok(CellValue::String(result))
154    }
155}
156
157fn checked_substitute_output_len(
158    text_len: usize,
159    old_len: usize,
160    new_len: usize,
161    replacement_count: usize,
162) -> Option<usize> {
163    let removed = old_len.checked_mul(replacement_count)?;
164    let added = new_len.checked_mul(replacement_count)?;
165    text_len.checked_sub(removed)?.checked_add(added)
166}
167
168/// REPLACE(old_text, start_num, num_chars, new_text) - replaces by position.
169///
170/// Both numbers are truncated toward zero. A start outside 1..=32767 or a
171/// length outside 0..=32767 returns `#VALUE!`, as in Excel.
172pub fn fn_replace(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
173    check_arg_count("REPLACE", args, 4, 4)?;
174    let old_text = text_arg!(&args[0], ctx);
175    let start_value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
176    let count_value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
177    let new_text = text_arg!(&args[3], ctx);
178    let (Some(start_num), Some(num_chars)) = (
179        checked_trunc_count(start_value),
180        checked_trunc_count(count_value),
181    ) else {
182        return Ok(CellValue::Error("#VALUE!".to_string()));
183    };
184    if !(1..=REPLACE_MAX_ARGUMENT).contains(&start_num) || num_chars > REPLACE_MAX_ARGUMENT {
185        return Ok(CellValue::Error("#VALUE!".to_string()));
186    }
187    let chars: Vec<char> = old_text.chars().collect();
188    let start = start_num - 1;
189    let end = start.saturating_add(num_chars).min(chars.len());
190    let mut result = String::new();
191    for &ch in &chars[..start.min(chars.len())] {
192        result.push(ch);
193    }
194    result.push_str(&new_text);
195    for &ch in &chars[end..] {
196        result.push(ch);
197    }
198    Ok(CellValue::String(result))
199}
200
201/// REPT(text, number_times) - repeats text.
202pub fn fn_rept(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
203    check_arg_count("REPT", args, 2, 2)?;
204    let text = text_arg!(&args[0], ctx);
205    let times_value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
206    let Some(times) = checked_trunc_count(times_value) else {
207        return Ok(CellValue::Error("#VALUE!".to_string()));
208    };
209    if text
210        .chars()
211        .count()
212        .checked_mul(times)
213        .is_none_or(|length| length > 32_767)
214    {
215        return Ok(CellValue::Error("#VALUE!".to_string()));
216    }
217    Ok(CellValue::String(text.repeat(times)))
218}
219
220/// EXACT(text1, text2) - case-sensitive comparison.
221pub fn fn_exact(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
222    check_arg_count("EXACT", args, 2, 2)?;
223    let text1 = text_arg!(&args[0], ctx);
224    let text2 = text_arg!(&args[1], ctx);
225    Ok(CellValue::Bool(text1 == text2))
226}
227
228/// T(value) - returns text if value is text, empty string otherwise.
229pub fn fn_t(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
230    check_arg_count("T", args, 1, 1)?;
231    let v = ctx.eval_expr(&args[0])?;
232    match v {
233        CellValue::String(s) => Ok(CellValue::String(s)),
234        CellValue::Error(_) => Ok(v),
235        _ => Ok(CellValue::String(String::new())),
236    }
237}
238
239/// PROPER(text) - capitalizes first letter of each word.
240pub fn fn_proper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
241    check_arg_count("PROPER", args, 1, 1)?;
242    let text = text_arg!(&args[0], ctx);
243    let mut result = String::with_capacity(text.len());
244    let mut capitalize_next = true;
245    for ch in text.chars() {
246        if ch.is_alphabetic() {
247            if capitalize_next {
248                result.extend(ch.to_uppercase());
249                capitalize_next = false;
250            } else {
251                result.extend(ch.to_lowercase());
252            }
253        } else {
254            result.push(ch);
255            capitalize_next = true;
256        }
257    }
258    Ok(CellValue::String(result))
259}
260
261#[cfg(test)]
262mod tests {
263    use super::*;
264    use crate::formula::eval::{evaluate, CellSnapshot};
265    use crate::formula::parser::parse_formula;
266
267    fn eval(formula: &str) -> CellValue {
268        let snap = CellSnapshot::new("Sheet1".to_string());
269        let expr = parse_formula(formula).unwrap();
270        evaluate(&expr, &snap).unwrap()
271    }
272
273    #[test]
274    fn test_concat() {
275        assert_eq!(
276            eval(r#"CONCAT("Hello"," ","World")"#),
277            CellValue::String("Hello World".to_string())
278        );
279    }
280
281    #[test]
282    fn test_find_basic() {
283        assert_eq!(eval(r#"FIND("B","ABCABC")"#), CellValue::Number(2.0));
284    }
285
286    #[test]
287    fn test_find_start_num() {
288        assert_eq!(eval(r#"FIND("B","ABCABC",3)"#), CellValue::Number(5.0));
289    }
290
291    #[test]
292    fn test_find_uses_character_positions_for_multibyte_text() {
293        assert_eq!(eval(r#"FIND("나","가나다",2)"#), CellValue::Number(2.0));
294    }
295
296    #[test]
297    fn test_find_not_found() {
298        assert_eq!(
299            eval(r#"FIND("Z","ABCABC")"#),
300            CellValue::Error("#VALUE!".to_string())
301        );
302    }
303
304    #[test]
305    fn test_search_case_insensitive() {
306        assert_eq!(eval(r#"SEARCH("b","ABCABC")"#), CellValue::Number(2.0));
307    }
308
309    #[test]
310    fn test_search_wildcard() {
311        assert_eq!(eval(r#"SEARCH("A*C","ABCABC")"#), CellValue::Number(1.0));
312        assert_eq!(eval(r#"SEARCH("*","ABC",4)"#), CellValue::Number(4.0));
313    }
314
315    #[test]
316    fn test_search_uses_character_positions_for_multibyte_text() {
317        assert_eq!(eval(r#"SEARCH("나","가나다",2)"#), CellValue::Number(2.0));
318    }
319
320    #[test]
321    fn test_substitute_all() {
322        assert_eq!(
323            eval(r#"SUBSTITUTE("aabbcc","b","X")"#),
324            CellValue::String("aaXXcc".to_string())
325        );
326    }
327
328    #[test]
329    fn test_substitute_instance() {
330        assert_eq!(
331            eval(r#"SUBSTITUTE("aabbcc","b","X",2)"#),
332            CellValue::String("aabXcc".to_string())
333        );
334    }
335
336    #[test]
337    fn substitute_output_length_detects_integer_overflow() {
338        assert_eq!(
339            checked_substitute_output_len(usize::MAX, 1, usize::MAX, 2),
340            None
341        );
342    }
343
344    #[test]
345    fn test_replace() {
346        assert_eq!(
347            eval(r#"REPLACE("ABCDEF",3,2,"XY")"#),
348            CellValue::String("ABXYEF".to_string())
349        );
350    }
351
352    #[test]
353    fn test_rept() {
354        assert_eq!(
355            eval(r#"REPT("AB",3)"#),
356            CellValue::String("ABABAB".to_string())
357        );
358    }
359
360    #[test]
361    fn test_rept_rejects_results_over_excel_character_limit() {
362        assert_eq!(
363            eval(r#"REPT("AB",16384)"#),
364            CellValue::Error("#VALUE!".to_string())
365        );
366    }
367
368    #[test]
369    fn test_exact_true() {
370        assert_eq!(eval(r#"EXACT("ABC","ABC")"#), CellValue::Bool(true));
371    }
372
373    #[test]
374    fn test_exact_false_case() {
375        assert_eq!(eval(r#"EXACT("ABC","abc")"#), CellValue::Bool(false));
376    }
377
378    #[test]
379    fn test_t_with_text() {
380        assert_eq!(
381            eval(r#"T("hello")"#),
382            CellValue::String("hello".to_string())
383        );
384    }
385
386    #[test]
387    fn test_t_with_number() {
388        assert_eq!(eval("T(42)"), CellValue::String(String::new()));
389    }
390
391    #[test]
392    fn test_proper() {
393        assert_eq!(
394            eval(r#"PROPER("hello world")"#),
395            CellValue::String("Hello World".to_string())
396        );
397    }
398}