I have Sheet A with a list of "tokens" in Column A and the values of each token in Column B. There are over 100 different tokens.
| TOKEN | VALUE |
| ------- | ----------------- |
| [NAME] | Michael |
| [PHONE] | (800) 555-1234 |
| [EMAIL] | [email protected] |
Then in Sheet B I have content that will use these different tokens.
| TITLE | DESCRIPTION |
| ------------------------ | --------------------------------- |
| Hello, my name is [NAME] | This is a description for [NAME] |
| Call me at [PHONE] | This is a description for [PHONE] |
| Email me at[EMAIL] | This is a description for [EMAIL] |
The tokens used in the content in Sheet B need to be replaced with the corresponding token values from Sheet A. My question is, how can I use a formula to look for and substitute these tokens in Sheet B with the token values in Sheet A without using 100 SUBSTITUTE formulas?
Here is an example with desired outcomes: https://docs.google.com/spreadsheets/d/1jbZOe_MILqRLw-rUJwu9RKCM-c8GFX0jyWT3ByIVXy0/edit?usp=sharing
CodePudding user response：
I have added a new sheet ("CONVERTED [Erik Help]"). The formula in A1 of that sheet produces the header and all results.
There are special considerations in a situation like this. For instance, a zip code and a date each consist of five numeric characters in memory (i.e., the zip code is obvious but dates are saved as the number of elapsed days since an origin date of December 30, 1899). So the formula needs to be able to distinguish the two, or dates would come through in the underlying five-digit form.
The solution involves "exploding" the original string into pieces. But some of those pieces are punctuation, which are tricky and must also be accounted for both in the "explode" and in the rejoin.
The formula was written to accommodate the Col-A data presently shown in TOKENS!A:A. If changes or additions are made to that data, the formula should still work as written, but it may produce unexpected results requiring modifications to the formula. (There is no way to account for future data that may not be of-a-kind with the data shown.)
Finally, I have to put out there that this solution goes beyond what can typically be offered via a free, volunteer-run forum. Forums are designed to share a little knowledge, a "nudge in the right direction" or small formula/script help with someone who has already worked out the underlying formula/script mostly correctly themselves. Obviously free forums cannot provide customized, time-intensive, expert-level solutions at no cost as a norm. So this formula is an exception to the rule (at least my rule). As such, while smaller formulas shared on this forum are generally explained as to how they work, I'm sharing this formula as-is and without explanation, since explaining would take longer than having written it.
The formula:
=ArrayFormula({"FILLED";FILTER(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(VLOOKUP(TRIM(SPLIT(TRIM(REGEXREPLACE(REGEXREPLACE(CONTENT!A2:A,"\]\[","] ["),"([\s\.,\?\!\/\-\&"&CHAR(34)&"])","~$1")),"~",1)),{TOKENS!A:A,IF(REGEXMATCH(UPPER(TOKENS!A:A),"DATE"),TEXT(TOKENS!B:B,"mmm d, yyyy"),TOKENS!B:B)},2,FALSE),SPLIT(TRIM(REGEXREPLACE(REGEXREPLACE(CONTENT!A2:A,"\]\[","] ["),"([\s\.,\?\!\/\-\&"&CHAR(34)&"])","~$1")),"~",1))),,COLUMNS(SPLIT(TRIM(REGEXREPLACE(REGEXREPLACE(CONTENT!A2:A,"\]\[","] ["),"([\s\.,\?\!\/\-\&"&CHAR(34)&"])","~$1")),"~",1))))),"\s([\.,\?\!\/\-\&"&CHAR(34)&"])","$1"),CONTENT!A2:A<>"")})
CodePudding user response：
Try with script
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var dictionary = new Map()
ss.getSheetByName('TOKENS').getDataRange().getDisplayValues().slice(1).forEach(x => dictionary.set(x[0], x[1]))
var range = ss.getSheetByName('CONTENT').getRange(2, 1, ss.getSheetByName('CONTENT').getLastRow() - 1, 1)
var data = range.getValues()
data.forEach(function (row) {
[...row[0].matchAll(/\[[\s\S\w] ?\]/g)].flat().forEach(t => row[0] = (dictionary.has(t)) ? row[0].replace(t, dictionary.get(t)) : row[0])
})
range.offset(0, 1).setValues(data)
};
References: