# Google Sheets help (regexreplace) **URL:** \ **Category:** Factual Questions\ **Created:** [March 21, 2024, 8:16pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158 "2024-03-21T20:16:22Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 21, 2024, 8:16pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/1 "2024-03-21T20:16:22Z")
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. ---
**Author:** ![kenoMD](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@kenoMD](https://boards.straightdope.com/u/kenoMD)\ **Post date:** [March 21, 2024, 10:12pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/2 "2024-03-21T22:12:06Z")
Try this =ArrayFormula(REGEXREPLACE(A2:A1000,“((AND|OR|WITH)\w+)”,“”)) ---
**Author:** ![pjd](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pjd/32/3009_2.png) [@pjd](https://boards.straightdope.com/u/pjd)\ **Post date:** [March 21, 2024, 10:44pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/3 "2024-03-21T22:44:14Z")
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 ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 22, 2024, 2:25am UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/4 "2024-03-22T02:25:34Z")
I’d really prefer to not use the AND/OR/WITH in the formula - as the list is actually about 30 words long. ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [March 22, 2024, 2:46am UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/5 "2024-03-22T02:46:00Z")
You can construct the regex string with something like: `=CONCATENATE("((", TEXTJOIN("|", FALSE, Z1:Z30), ")\w+)")` ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 22, 2024, 11:48am UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/6 "2024-03-22T11:48:32Z")
> [@kenoMD](#): > > =ArrayFormula(REGEXREPLACE(A2:A1000,“((AND|OR|WITH)\w+)”,“”)) This isn’t working - it’s spits out the same text. ---
**Author:** ![kenoMD](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@kenoMD](https://boards.straightdope.com/u/kenoMD)\ **Post date:** [March 22, 2024, 12:17pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/7 "2024-03-22T12:17:06Z")
> [@kenoMD](#): > > =ArrayFormula(REGEXREPLACE(A2:A1000,“((AND|OR|WITH)\w+)”,“”)) this is working for me =ArrayFormula(REGEXREPLACE(A2:A100,“(AND|OR|WITH\w+)”,“”)) ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 22, 2024, 1:05pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/8 "2024-03-22T13:05:26Z")
Perfect! Now I’ll try to figure out the TEXTJOIN, and then I’ll add a SPLIT. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 22, 2024, 1:17pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/9 "2024-03-22T13:17:55Z")
Sorry - it’s actually only working on the first delimiter. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 22, 2024, 1:56pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/10 "2024-03-22T13:56:35Z")
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). ---
**Author:** ![kenoMD](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@kenoMD](https://boards.straightdope.com/u/kenoMD)\ **Post date:** [March 22, 2024, 5:23pm UTC](https://boards.straightdope.com/t/google-sheets-help-regexreplace/999158/11 "2024-03-22T17:23:34Z")
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!