I have a column of data that has been imported, A2:A1000. Along the way, it has been concatenated from two columns, using “AND”, “OR” or “WITH”. I only need the front half of this information. I also need it to be an exact case match, because sometimes the data in A:A could be “AmpersandWITHRedPyramid”, and a non-case match would return “Ampers”.
I’m thinking, put the terms I want to search/replace somewhere, like Column Z (Z1:Z3). But then I have no clue.
Try this
=ArrayFormula(REGEXREPLACE(A2:A1000,“((AND|OR|WITH)\w+)”,“”))
This might work :-
=ifs(iferror(find(“AND”,A2),0),index(split(A2,“AND”,FALSE),0,1),iferror(find(“OR”,A2),0),index(split(A2,“OR”,FALSE),0,1),iferror(find(“WITH”,A2),0),index(split(A2,“WITH”,FALSE),0,1))
Put that in B2 and copy it to B3-B1000
I’d really prefer to not use the AND/OR/WITH in the formula - as the list is actually about 30 words long.
You can construct the regex string with something like:
=CONCATENATE("((", TEXTJOIN("|", FALSE, Z1:Z30), ")\w+)")
This isn’t working - it’s spits out the same text.
this is working for me
=ArrayFormula(REGEXREPLACE(A2:A100,“(AND|OR|WITH\w+)”,“”))
Perfect! Now I’ll try to figure out the TEXTJOIN, and then I’ll add a SPLIT.
Sorry - it’s actually only working on the first delimiter.
I got it! The “w” was throwing things off.
ARRAYFORMULA(REEXREPLACE(A:A, TEXTJOIN(“|”, false, $Z$1:$Z$30, “+”), “$”). Then I’ll do a LEFT(B:B, FIND(“$”, B:B)-1).
Guess I don’t understand your question, thought you wanted to keep everything before the AND|OR|WITH .
Oh well, if you got it to work Good!