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
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.
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
Dont forget about AI!
I think this can work for you, you would just have to fine-tune it a bit
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
Interesting riddle. I found a way to do this without object modification
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():
4 Likes
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.
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