Replacing multiple words within a string of text using a formula

1

I cannot figure this out.

Let’s assume you have two tables Table1 and Table2.

Table1 has one column TextColumn, which contains the text string.

Table2 has two columns WordColumn and ValueColumn.

You want to create a new column in Table1, let’s call it UpdatedTextColumn, where each text string from TextColumn will replace words that are found in WordColumn with the corresponding value from ValueColumn.

Originally I thought to use

ForEach(
  [Table2],
  thisRow.textColumn
    .RegexReplace(
      WordColumn, [ValueColumn]
    )
)

But the result this produces gives a bunch or different versions of the TextColumn’s row and it doesn’t build upon it’s changes.

I could make a button that solves this, but I would like not to. Is there anyway I can have the formula build on it’s changes?

2

Having trouble understanding what you need?

Can you pop in a MVP doc that shows what you’re after?

3

Hey, I made a small demo here!

4

Youll have to do something like this

Im splitting the sentence on a SPACE, then running a forEach loop over that. Bascially evaulating each word saying

Hey Coda, if the current word in the sentence which you are looking at happens to be in the second table, then go ahead and look into the second table, find which row it matches specifically, and replace it with the value corresponding to that word.
If its NOT in the table, well dont do anything

Heres the formula I wrote with some comments

image
image1962×1676 260 KB

1 Like
5

perhaps add

.Join(" “)+”."

to the end of your formula to string your words together into a sentence?

otherwise, you get the words listed with separating commas.

max

6

I added the join as a separate column.

Interestingly enough, adding join() to my formula, while still outputting the correct sentence, threw an error in the Coda formula editor

Try it out. Super weird.

7

Yep, I stumbled upon that too whatever the amount of ListCombine() I add to flatten the list :face_with_raised_eyebrow:

Something that seemed to work though (while still feeling strange) is to append a WithName() at the end and Join() the list in there :woman_shrugging:

thisRow.TextColumn.Split(" ")
  .ForEach(
    WithName(
      CurrentValue,
      currentWord,
      Table2.Filter(WordColumn = currentWord).First()
        .WithName(
          foundWordRow,
          If(
            foundWordRow.Count() > 0,
            foundWordRow.ValueColumn,
            currentWord
          )
        )
    )
  ).WithName(R,
    R.Join(" ")
     )

In that case, the formula editor doesn’t throw an error :woman_shrugging: …

8

I appreciate your reponse!

I tried the example, but unfortunately it didn’t quite work in my use case, I probably should’ve had another example.

I unfortunately cannot split by " ", as, if the sentence has punctation next to the keyword, the replacement falls apart.

If the input TextColumn said “I hate dogs. cats are cool though”, then it wouldn’t be able to find the word “dogs” because splitting it would produce “dogs.”

It would also need to keep the line breaks, which yours did, but it looks like in the Result column, it might be keeping it as a whole word since the ‘space’ is technically not a " ", so in that case it could also miss finding a keyword.

image

9

What if you put a regex inside the for loop to replace all non-alpha-numeric characters?

That would get around the punctuation issue.

10

I’m still trying to figure this out Ha! I’ve tried putting RegexReplace in multiple places but can’t seem to get the disired result…

11

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.