Regexreplace text with a variable text

user_19529675 0
Viewed 66

I want to replace "username" before file extentions with the custom names in column D regardless of variations in the rest of the text. The username is variable. I tried substitute function but it's replaces all the occurrences of username.

enter image description here

textjoin google-sheets arrays replace regex
2 Answers

As mentioned, the issue with the regexmatch from the find UI function of Google Sheets is that the whole document/tab will be searched to find matches.

Since the value to be replaced is not always the same, in fact, each row has its corresponding new value, so I believe the best approach to solve this is to use Apps Scripts.

The idea of the script would be to loop through rows, store the new username column of that row, search for the pattern to be replaced on the Data column and replace it with the stored new username.

You may use Javascript's String method replace() which also accepts regex as a search pattern parameter.

And here you may find an example of how to read/write data of a Sheets file using Apps Script.

user_178390410

try:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A, "(.+ - )")&D2:D&
                   REGEXEXTRACT(A2:A, "(\s?\.[a-z]+$)")))

enter image description here

.        single character
.+       multiple characters
(.+ - )  group of all characters followed by space, dash and space
\s       space
\s?      space if exists
\.       dot
[a-z]    any lowercase character
[a-z]+   group of lowercased characters
$        end of the string
user_56326290
Related