Replace, multiple parts

Questions and Help
1

I am trying to get help with a replace based on copying and pasting from a table on a website;

{formparagraph: name=vsapmc; cols=50}
**What was input:
Brown, Kimberly ADAMS, MICAH 09/08/2021 14 days ago
Brown, Kimberly PRESLEY, ELVIS 09/05/2021 15 days ago
Brown, Kimberly MCFLY, MARTIN (Marty) 09/06/2021 15 days ago

{=replace(vsapmc, "Brown, Kimberly", " ")}
**What displays after the replace
ADAMS, MICAH 09/08/2021 14 days ago
PRESLEY, ELVIS 09/05/2021 15 days ago
MCFLY, MARTIN (Marty) 09/06/2021 15 days ago

How do I go about getting rid of the "14 days..." or whatever after the date?

Please advise and thank you.
Adam

2

Welcome to the forum @anon11192069,

This should do it.

{note}
Text to exclude:
{formtext: name=exclude; default=Brown, Kimberly}

Content to extract from:
{formparagraph: name=vsapmc; cols=60; rows=5; default=
Brown, Kimberly ADAMS, MICAH 09/08/2021 14 days ago
Brown, Kimberly PRESLEY, ELVIS 09/05/2021 15 days ago
Brown, Kimberly MCFLY, MARTIN (Marty) 09/06/2021 15 days ago}
{endnote: trim=right}

{note: preview=no}
{list=extractregexall(vsapmc, exclude&" ([A-Z a-z, ()]+[\d/]+)")}

{endnote: trim=right}

Result:
{=join(list, "\n")}

P.S. This is what it would look like without the join function:
{=list}

Here's what's happening in the snippet:

  • I created a formtext command with the name exclude, where I can insert the name to exclude. This for cases where the name in the list changes for any reason.
  • {extractregexall} matches all instances of a specific pattern. You can read more about it here: Text Blaze | Formula Reference (just run a search on the page for extractregexall)
  • I'm telling the {extractregexall} command to start by matching whatever is in the exclude variable
  • Once it has matched the text to be excluded (without capturing it), I'm telling it to match any combination of uppercase an lowercase letters, commas, spaces and brackets, and to keep capturing as many as it finds (hence the plus sign) until it reaches a place where it finds a combination of digits and slashes (\d means digits). Since I didn't include any spaces in this part, the matching will stop at the end of the date.
  • To conclude, I assigned the whole thing to a variable called list and then used the join function to join all of the items with a line break, represented by the \n

If you're not familiar with how regex patterns work, this might be a bit overwhelming for you. But please ask any questions you have, no matter how small. I'll be happy to help, as will many other members of this small but cool community :slight_smile:

1 Like
4

This works great thank you.
I do have a follow-up question; do I have to have a default set? I might have different data (names, etc.) and a different length. Sometimes I'll have 12 rows, sometimes 6, etc. The format, however, will always be the same:
LASTNAME1, FIRSTNAME1 (tab-delimiter) LASTNAME2, FIRSTNAME2 (tab-delimiter) DATE (tab-delimiter) XX days ago

I want to be able to copy and paste from a table on a webpage and that datat will be changing.

Please advise and thank you.

Adam

5

@anon11192069 can you give me some examples with dummy data please?

6

Here are 2 different groups.. I might pull this list today and the second tomorrow, the first field is the employee name, and say the 2nd is a made up customer name:
Soprano, Tony Adams, Amy 08/09/2021 13 days ago
Soprano, Tony BLOCK, BOB 07/03/2021 16 days ago
Soprano, Tony CHILTON, CHARLES (Charlie) 09/02/2021 21 days ago

Presley, Elvis Adams, Amy 08/09/2021 13 days ago
Presley, Elvis BLOCK, BOB 07/03/2021 16 days ago
Presley, Elvis CHILTON, CHARLES (Charlie) 09/02/2021 21 days ago
Presley, Elvis Dunder, Doug 08/09/2021 13 days ago
Presley, Elvis Echo, Erin 07/03/2021 16 days ago
Presley, Elvis FRANSISCO, FRANCIS (Frank) 09/02/2021 21 days ago
Presley, Elvis Gygex, Gary 08/09/2021 13 days ago

7

@anon11192069, if you're 100% the values are separated by a tab stop, there could be a much simpler way of doing this.

But the data needs to be in the form of a table. Copy the data and use this snippet. Let me know how that works out.

{data={clipboard}}
{rows=split(data, "\n")}
{repeat: for row in rows; trim=right}
{=split(row, "\t")[2]} {=split(row, "\t")[3]}
{endrepeat}