Regex Sharepoint Names

Question

Hi all

I am bring in some data from Sharepoint to a workflow. The Attendee column is adding various characters to the name data. The names come through looking like this:

81;#ZZ_John Smith;78;#Mark Smith;105;#Joe Bloggs Carpenter;13;#Ashley Le Brocq;26;#Alan Shearer;

I would like to present the data as simple names separated by a comma, i.e.

John Smith, Mark Smith, Joe Bloggs Carpenter, Ashley Le Blocq, Alan Shearer

I have tried to use the regex parse function, but regex is still in the realms of the dark arts for me at this stage.

Does anyone have a solution they can share with me?

Thanks

Jonathan-Sherman · Accepted Answer

Hi @Sidey1978,

You could do it with a regex tokenise and a summarise tool

The tokenise expression could be #(?:.*?_)?(.*?);

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

danilang · Accepted Answer

Hi @Sidey1978

You can also do it in a single formula tool like this

TrimLeft(REGEX_Replace(Replace([Field1],";",""), "\d+#(?:.*?_)?", ","),",")

Working from the inside out

Replace([Field1],";","") replaces all the Semi colons with empty strings

REGEX_Replace(XXX, "\d+#(?:.*?_)?", ",") replaces the remaining non-word characters with a comma.  The expression is

\d+#  matches any number of digits followed by #

(?:.*?_)?  The blue inner part .*?_ matches any sequence of character ending in a "_".  The wrapping part in green says that the inner part is optional.  This handles the "ZZ_" which is only there at the start

TrimLeft(xxx,",") The regex leaves an extra comma at the start, so this removes it.

Dan