formualizer_eval/builtins/text/
mid_sub_replace.rs

1use super::super::utils::ARG_ANY_ONE;
2use crate::args::ArgSchema;
3use crate::function::Function;
4use crate::traits::{ArgumentHandle, FunctionContext};
5use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
6use formualizer_macros::func_caps;
7
8fn scalar_like_value(arg: &ArgumentHandle<'_, '_>) -> Result<LiteralValue, ExcelError> {
9    Ok(match arg.value()? {
10        crate::traits::CalcValue::Scalar(v) => v,
11        crate::traits::CalcValue::Range(rv) => rv.get_cell(0, 0),
12        crate::traits::CalcValue::Callable(_) => LiteralValue::Error(
13            ExcelError::new(ExcelErrorKind::Calc).with_message("LAMBDA value must be invoked"),
14        ),
15    })
16}
17
18// MID(text, start_num, num_chars)
19#[derive(Debug)]
20pub struct MidFn;
21/// Returns a substring starting at a 1-based position.
22///
23/// # Remarks
24/// - `start_num` is 1-based; values below `1` return `#VALUE!`.
25/// - `num_chars` must be non-negative; negatives return `#VALUE!`.
26/// - If start is beyond the end of the text, returns an empty string.
27/// - Non-text inputs are coerced to text.
28///
29/// # Examples
30///
31/// ```yaml,sandbox
32/// title: "Extract middle segment"
33/// formula: '=MID("spreadsheet", 3, 5)'
34/// expected: "reads"
35/// ```
36///
37/// ```yaml,sandbox
38/// title: "Start past end returns empty"
39/// formula: '=MID("abc", 10, 2)'
40/// expected: ""
41/// ```
42///
43/// ```yaml,docs
44/// related:
45///   - LEFT
46///   - RIGHT
47///   - REPLACE
48/// faq:
49///   - q: "How does MID handle out-of-range start positions?"
50///     a: "If start_num is beyond the text length, MID returns an empty string."
51/// ```
52/// [formualizer-docgen:schema:start]
53/// Name: MID
54/// Type: MidFn
55/// Min args: 3
56/// Max args: 1
57/// Variadic: false
58/// Signature: MID(arg1: any@scalar)
59/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
60/// Caps: PURE
61/// [formualizer-docgen:schema:end]
62impl Function for MidFn {
63    func_caps!(PURE);
64    fn name(&self) -> &'static str {
65        "MID"
66    }
67    fn min_args(&self) -> usize {
68        3
69    }
70    fn arg_schema(&self) -> &'static [ArgSchema] {
71        &ARG_ANY_ONE[..]
72    }
73    fn eval<'a, 'b, 'c>(
74        &self,
75        args: &'c [ArgumentHandle<'a, 'b>],
76        _: &dyn FunctionContext<'b>,
77    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
78        if args.len() != 3 {
79            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
80                ExcelError::new_value(),
81            )));
82        }
83        let s = to_text(&args[0])?;
84        let start = number_like(&args[1])?;
85        let count = number_like(&args[2])?;
86        if start < 1 || count < 0 {
87            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
88                ExcelError::new_value(),
89            )));
90        }
91        let chars: Vec<char> = s.chars().collect();
92        if (start as usize) > chars.len() {
93            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(
94                String::new(),
95            )));
96        }
97        let end = ((start - 1) + count) as usize;
98        let end = min(end, chars.len());
99        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(
100            chars[(start as usize - 1)..end].iter().collect(),
101        )))
102    }
103}
104
105// SUBSTITUTE(text, old_text, new_text, [instance_num]) - limited semantics
106#[derive(Debug)]
107pub struct SubstituteFn;
108/// Replaces matching text within a string.
109///
110/// `SUBSTITUTE` can replace all occurrences or only a specific instance.
111///
112/// # Remarks
113/// - Matching is case-sensitive.
114/// - If `old_text` is empty, the original text is returned unchanged.
115/// - With `instance_num`, only that 1-based occurrence is replaced.
116/// - Non-positive `instance_num` returns `#VALUE!`.
117///
118/// # Examples
119///
120/// ```yaml,sandbox
121/// title: "Replace all matches"
122/// formula: '=SUBSTITUTE("a-b-a", "a", "x")'
123/// expected: "x-b-x"
124/// ```
125///
126/// ```yaml,sandbox
127/// title: "Replace only second match"
128/// formula: '=SUBSTITUTE("2024-01-2024", "2024", "FY24", 2)'
129/// expected: "2024-01-FY24"
130/// ```
131///
132/// ```yaml,docs
133/// related:
134///   - REPLACE
135///   - TEXTBEFORE
136///   - TEXTAFTER
137/// faq:
138///   - q: "Is SUBSTITUTE case-sensitive?"
139///     a: "Yes. It matches old_text with exact case and replaces either all or the requested instance."
140/// ```
141/// [formualizer-docgen:schema:start]
142/// Name: SUBSTITUTE
143/// Type: SubstituteFn
144/// Min args: 3
145/// Max args: variadic
146/// Variadic: true
147/// Signature: SUBSTITUTE(arg1...: any@scalar)
148/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
149/// Caps: PURE
150/// [formualizer-docgen:schema:end]
151impl Function for SubstituteFn {
152    func_caps!(PURE);
153    fn name(&self) -> &'static str {
154        "SUBSTITUTE"
155    }
156    fn min_args(&self) -> usize {
157        3
158    }
159    fn variadic(&self) -> bool {
160        true
161    }
162    fn arg_schema(&self) -> &'static [ArgSchema] {
163        &ARG_ANY_ONE[..]
164    }
165    fn eval<'a, 'b, 'c>(
166        &self,
167        args: &'c [ArgumentHandle<'a, 'b>],
168        _: &dyn FunctionContext<'b>,
169    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
170        if args.len() < 3 || args.len() > 4 {
171            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
172                ExcelError::new_value(),
173            )));
174        }
175        let text = to_text(&args[0])?;
176        let old = to_text(&args[1])?;
177        let new = to_text(&args[2])?;
178        if old.is_empty() {
179            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(text)));
180        }
181        if args.len() == 4 {
182            let instance = number_like(&args[3])?;
183            if instance <= 0 {
184                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
185                    ExcelError::new_value(),
186                )));
187            }
188            let mut idx = 0;
189            let mut count = 0;
190            let mut out = String::new();
191            while let Some(pos) = text[idx..].find(&old) {
192                out.push_str(&text[idx..idx + pos]);
193                count += 1;
194                if count == instance {
195                    out.push_str(&new);
196                    out.push_str(&text[idx + pos + old.len()..]);
197                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)));
198                } else {
199                    out.push_str(&old);
200                    idx += pos + old.len();
201                }
202            }
203            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(text)))
204        } else {
205            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(
206                text.replace(&old, &new),
207            )))
208        }
209    }
210}
211
212// REPLACE(old_text, start_num, num_chars, new_text)
213#[derive(Debug)]
214pub struct ReplaceFn;
215/// Replaces part of a text string by position.
216///
217/// `REPLACE(old_text, start_num, num_chars, new_text)` works by character index.
218///
219/// # Remarks
220/// - `start_num` is 1-based and must be at least `1`.
221/// - `num_chars` must be non-negative.
222/// - If start is beyond the end, the original text is returned unchanged.
223/// - Non-text inputs are coerced to text.
224///
225/// # Examples
226///
227/// ```yaml,sandbox
228/// title: "Replace middle segment"
229/// formula: '=REPLACE("abcdef", 3, 2, "ZZ")'
230/// expected: "abZZef"
231/// ```
232///
233/// ```yaml,sandbox
234/// title: "Insert at start"
235/// formula: '=REPLACE("report", 1, 0, "Q1-")'
236/// expected: "Q1-report"
237/// ```
238///
239/// ```yaml,docs
240/// related:
241///   - SUBSTITUTE
242///   - MID
243///   - LEFT
244/// faq:
245///   - q: "Does REPLACE match text patterns?"
246///     a: "No. REPLACE is position-based and replaces by start_num and num_chars, not by searching old text."
247/// ```
248/// [formualizer-docgen:schema:start]
249/// Name: REPLACE
250/// Type: ReplaceFn
251/// Min args: 4
252/// Max args: 1
253/// Variadic: false
254/// Signature: REPLACE(arg1: any@scalar)
255/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
256/// Caps: PURE
257/// [formualizer-docgen:schema:end]
258impl Function for ReplaceFn {
259    func_caps!(PURE);
260    fn name(&self) -> &'static str {
261        "REPLACE"
262    }
263    fn min_args(&self) -> usize {
264        4
265    }
266    fn arg_schema(&self) -> &'static [ArgSchema] {
267        &ARG_ANY_ONE[..]
268    }
269    fn eval<'a, 'b, 'c>(
270        &self,
271        args: &'c [ArgumentHandle<'a, 'b>],
272        _: &dyn FunctionContext<'b>,
273    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
274        if args.len() != 4 {
275            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
276                ExcelError::new_value(),
277            )));
278        }
279        let text = to_text(&args[0])?;
280        let start = number_like(&args[1])?;
281        let num = number_like(&args[2])?;
282        let new = to_text(&args[3])?;
283        if start < 1 || num < 0 {
284            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
285                ExcelError::new_value(),
286            )));
287        }
288        let mut chars: Vec<char> = text.chars().collect();
289        let len = chars.len();
290        let start_idx = (start as usize).saturating_sub(1);
291        if start_idx > len {
292            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(text)));
293        }
294        let end_idx = (start_idx + num as usize).min(len);
295        chars.splice(start_idx..end_idx, new.chars());
296        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(
297            chars.into_iter().collect(),
298        )))
299    }
300}
301
302fn to_text<'a, 'b>(arg: &ArgumentHandle<'a, 'b>) -> Result<String, ExcelError> {
303    let v = scalar_like_value(arg)?;
304    Ok(match v {
305        LiteralValue::Text(s) => s,
306        LiteralValue::Empty => String::new(),
307        LiteralValue::Boolean(b) => {
308            if b {
309                "TRUE".into()
310            } else {
311                "FALSE".into()
312            }
313        }
314        LiteralValue::Number(f) => {
315            let mut s = f.to_string();
316            if s.ends_with(".0") {
317                s.truncate(s.len() - 2);
318            }
319            s
320        }
321        LiteralValue::Int(i) => i.to_string(),
322        LiteralValue::Error(e) => return Err(e),
323        other => other.to_string(),
324    })
325}
326fn number_like<'a, 'b>(arg: &ArgumentHandle<'a, 'b>) -> Result<i64, ExcelError> {
327    let v = scalar_like_value(arg)?;
328    Ok(match v {
329        LiteralValue::Int(i) => i,
330        LiteralValue::Number(f) => f as i64,
331        LiteralValue::Text(t) => t.parse::<i64>().unwrap_or(0),
332        LiteralValue::Boolean(b) => {
333            if b {
334                1
335            } else {
336                0
337            }
338        }
339        LiteralValue::Empty => 0,
340        LiteralValue::Error(e) => return Err(e),
341        other => other.to_string().parse::<i64>().unwrap_or(0),
342    })
343}
344
345use std::cmp::min;
346
347pub fn register_builtins() {
348    use std::sync::Arc;
349    crate::function_registry::register_function(Arc::new(MidFn));
350    crate::function_registry::register_function(Arc::new(SubstituteFn));
351    crate::function_registry::register_function(Arc::new(ReplaceFn));
352}
353
354#[cfg(test)]
355mod tests {
356    use super::*;
357    use crate::test_workbook::TestWorkbook;
358    use crate::traits::ArgumentHandle;
359    use formualizer_common::LiteralValue;
360    use formualizer_parse::parser::{ASTNode, ASTNodeType};
361    fn lit(v: LiteralValue) -> ASTNode {
362        ASTNode::new(ASTNodeType::Literal(v), None)
363    }
364    #[test]
365    fn mid_basic() {
366        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(MidFn));
367        let ctx = wb.interpreter();
368        let f = ctx.context.get_function("", "MID").unwrap();
369        let s = lit(LiteralValue::Text("hello".into()));
370        let start = lit(LiteralValue::Int(2));
371        let cnt = lit(LiteralValue::Int(3));
372        let out = f
373            .dispatch(
374                &[
375                    ArgumentHandle::new(&s, &ctx),
376                    ArgumentHandle::new(&start, &ctx),
377                    ArgumentHandle::new(&cnt, &ctx),
378                ],
379                &ctx.function_context(None),
380            )
381            .unwrap()
382            .into_literal();
383        assert_eq!(out, LiteralValue::Text("ell".into()));
384    }
385    #[test]
386    fn substitute_all() {
387        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SubstituteFn));
388        let ctx = wb.interpreter();
389        let f = ctx.context.get_function("", "SUBSTITUTE").unwrap();
390        let text = lit(LiteralValue::Text("a_b_a".into()));
391        let old = lit(LiteralValue::Text("_".into()));
392        let new = lit(LiteralValue::Text("-".into()));
393        let out = f
394            .dispatch(
395                &[
396                    ArgumentHandle::new(&text, &ctx),
397                    ArgumentHandle::new(&old, &ctx),
398                    ArgumentHandle::new(&new, &ctx),
399                ],
400                &ctx.function_context(None),
401            )
402            .unwrap()
403            .into_literal();
404        assert_eq!(out, LiteralValue::Text("a-b-a".into()));
405    }
406    #[test]
407    fn replace_basic() {
408        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ReplaceFn));
409        let ctx = wb.interpreter();
410        let f = ctx.context.get_function("", "REPLACE").unwrap();
411        let text = lit(LiteralValue::Text("hello".into()));
412        let start = lit(LiteralValue::Int(2));
413        let num = lit(LiteralValue::Int(2));
414        let new = lit(LiteralValue::Text("YY".into()));
415        let out = f
416            .dispatch(
417                &[
418                    ArgumentHandle::new(&text, &ctx),
419                    ArgumentHandle::new(&start, &ctx),
420                    ArgumentHandle::new(&num, &ctx),
421                    ArgumentHandle::new(&new, &ctx),
422                ],
423                &ctx.function_context(None),
424            )
425            .unwrap()
426            .into_literal();
427        assert_eq!(out, LiteralValue::Text("hYYlo".into()));
428    }
429}
formualizer_eval/builtins/text/mid_sub_replace.rs

formualizer_eval/builtins/text/
mid_sub_replace.rs
