650510_en-US

liliconcom

Regex to remove text from string

Hi, 

I am trying to remove specific text from a string using regex. The cell contents contain one or more items separated by commas with "not detected" at the end. I'd like to remove "not detected"  to leave just the items listed that can be split by the comma. What I am using:

 

Regex( Substitute( :"DETAILS"n, "not detected", "" ), "\S*" )

 

However, this appears to be truncating the list wherever there is a space, so "item A, item B not detected" becomes "item" where I would like to have "item A, item B."

 

I suppose I can work around it by removing spaces first, but would like to keep in more concise if possible. Is there a modification that would return the list and only remove the "not detected"?

 

Thank you, 

Cass

Automation and ScriptingContent OrganizationRe: Regex to remove text from string

Is return3 what you are looking for?

Names Default To Here(1);

str = "item A, item B not detected";

return1 = Regex(Substitute(str, "not detected", ""), "\S*");
return2 = Substitute(str, "not detected", "");
return3 = Words(Substitute(str, " not detected", ""), ",");

show(return1, return2, return3);

You can just replace the one additional space with substitute and to split into a list use Words() with "," as separator

Re: Regex to remove text from string

Yep it looks like return 2 gets me what I need. I should have mentioned that I inteded to split the items separated by commas into columns using the text to columns function. 

Thank you, jthi!