Regexreplace text with a variable text-CodePudding

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.

CodePudding user response：

try:

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

.        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

CodePudding user response：

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.

CodePudding user response：

Delete everything from Col B (including the header in B1). Then place the following array formula in cell B1:

=ArrayFormula({"Result";IF(A2:A="",,REGEXREPLACE(A2:A,"user[^\\] (\.\w{3})",TRIM(D2:D)&"$1"))})

This one formula will produce the header (which you can later change within the formula itself if you like) and all results for all occupied rows.

The REGEX find and replace expressions will look only for patterns that start with "user" and do not have any backslashes after them in the string and terminate in a period followed by three word characters (i.e., a three-letter file extension); those will be replaced with the values in D2:D plus the file extension.

I included TRIM as a safeguard against stray spaces that may be added to the Col-D values.

If you find that you'll later be including file types with fewer and/or more letters in the extension, change the {3} to {2,4} (e.g., to include file extensions of two to four letters instead of only three).