How do you replace in rich text while preserving formatting?

1

Both the Substitute and RegexReplace function removes the formatting. While other functions such as Concatenate preserves the formatting. Is there a version of replace that allows formatting to be preserved?

1 Like
2

That’s a great question.

We don’t have a formula that would support that right now and I’m not sure of a workaround to make it happen either.

I’m sorry I couldn’t be of more help here.

3

You can achieve it using hidden formula (at your own risk)

Instead of:

thisRow.[Column 1].RegexReplace(thisRow.[Column 2], thisRow.[Column 3])

You can use:

ParseJSON(
  '{"o":' +
    _merge(thisRow.[Column 1]).ToText()
      .RegexReplace(
        thisRow.[Column 2], thisRow.[Column 3]
      ) +
    '}'
)
  ._Deref_object(
    "o"
  )
2 Likes
4

Dont forget about AI!

I think this can work for you, you would just have to fine-tune it a bit

Screen Cast 2023-05-18 at 2.06.04 PM

5

hi @Tony_Xiao1 ,

May AI be flawless, that would be great news. Hélas, it is not yet (?). We may need a bit of code as @Breno_Nunes showed us. His example _ I like and appreciate a lot - works only in case you have a text that at least contains one rich text element. To make it work in a broader context, you need an if statement that deals with rich texts (Breno’s solution) and with normal text - the standard regexReplace().

I guess that the Coda bet is on the AI part meaning that we as humans have to learn how to write instructive prompts for bots…

Cheers, Christiaan

1 Like
6

Interesting riddle. I found a way to do this without object modification

image
image1562×193 17.8 KB

If you need to replace only the first occurrence, use:

WithName("level", WordToReplace,
  thisRow.Name.Splice(
    thisRow.Name.Find(WordToReplace),
    WordToReplace.Length(),
    "13v31"
  )
)

If you want to replace all occurrences, use the Split+Splice approach:

thisRow.Name.Split("level").WithName(Splits,
  // For each of the splits but the last
  Sequence(1, Splits.Count() - 1).ForEach(
    // append the replacement word using Splice() to apply formatting
    Splits.Nth(CurrentValue).Splice(
      Splits.Nth(CurrentValue).Length() + 1, 0,
      "13v31"
    )
  // finally combine those together along with the last
  ).Concatenate(Splits.Last())
)

Unlike Concatenate() that takes the formatting of the added string, Splice retains the formatting of the destination point where text is inserted.

In both cases you have to ensure the sought text is present at least once, so wrap it in an If() or something.

I’m not a big fan of splitting/joining for the sake of replacement, but other options are worse. JSON will only work if the whole text to replace is formatted the same, otherwise it will be separated into multiple child nodes. This one is more bulletproof.

Generally it’s better to prepare your strings with {1} {2} placeholders etc, and run them through Format():

image
image768×252 21.3 KB

4 Likes
7

P.S. As another fun challenge to myself, I solved the RegexReplace with a regex replacement (i.e. so that both level and leeeeevel would be replaced given the le+vel regex.

image
image1319×993 109 KB

Warning: advanced zone lol:

WithName("le+vel", Regex,
  // First we need to split at all points having the occurrence
  thisRow.Name.RegexExtract(Format(
    ".*?(?:{1})|(?<={1}).*$",
    Regex
  ), "gis").WithName(Portions,
    // Map to cut points
    ListCombine(0, Sequence(1, Portions.Count()).ForEach(CurrentValue.WithName(I,
      Portions.Slice(1, I).Concatenate().Length()
    ))).WithName(CutPoints,
      // Cut the input text by cut points
      Sequence(2, CutPoints.Count()).ForEach(
        thisRow.Name.Slice(CutPoints.Nth(CurrentValue - 1) + 1, CutPoints.Nth(CurrentValue))
      )
    ).WithName(Splits,
      // For each of the splits but the last
      Sequence(1, Splits.Count() - 1).ForEach(
        Portions.Nth(CurrentValue).RegexExtract(Regex + "$", "is").Length().WithName(ReplacementLength,
          // replace the trailing match (calculate its length here)
          // with a word using Splice() to apply formatting
          Splits.Nth(CurrentValue).Splice(
            Splits.Nth(CurrentValue).Length() + 1 - ReplacementLength,
            ReplacementLength,
            "13v31"
          )
        )
      ).Concatenate(Splits.Last())
    )
  )
)
1 Like
8

Hi Team, I found a workaround to this that could almost be called elegant. I thought I would post it here in case the next person who comes across this post could benefit.

image

The idea is simple. I have added nice spacing but it is just a long chain of SPLIT/JOIN pairs.

[Title Template].Split('{{First Name}}').Join(thisRow.[First Name]).Split('{{Last Name}}').Join(thisRow.[Last Name]) ...
  1. Take your original text. (Heystack)
  2. SPLIT on the text you are looking to replace (Needles).
  3. And then immediately JOIN using the replacing text (Replace With).
  4. Repeat as necessary

Enjoy!

As a happy bonus I also found this method doesn’t drop formatting or @References that can happened with other methods

2 Likes