Google Sheets - replace the exact text/number with a different one ignoring other matching parts
I need a formula that automatically replaces a certain text or number in Google Sheets (places the result in a different column), but only if this the exact match.
I'm trying to add a formula that will automatically change a certain number or text (and place it to the column D) when someone adds it to the column A.
I've tried using =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3;"99";"8");"199";"15");"9999";"700"), but it returns 9999 as 88 (two times 99) instead of 700 and this is true for any text values as well. I've also tried using =REGEXMATCH / REGEXREPLACE, but it doesn't work with OR operator for some reason and using AND just adds matching values on top of each other (like getting 8700 instead of just 700 when trying to replace 9999 in this example). This is a nonlinear dependence, so simply multiplying/dividing doesn't work in this case.
I'm trying to solve this issue with an easy to follow and edit formula, so I'd rather avoid using scripts if possible (I'm afraid it can't be difficult for me to edit it later and explain how it works for other non tech savvy users). Any help is highly appreciated!
| old data | text data | new data (what I get) | desired outcome |
|---|---|---|---|
| 99 | 99 | 8 | 8 |
| 199 | 199 | 18 | 15 |
| 9999 | 9999 | 88 | 700 |
Create a lookup_table at some spot(as shown in screenshot) which lists all the substitutions you wish to accommodate & then use the xlookup as below:
=arrayformula(xlookup(A2:A,F:F,G:G,))
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation
- Google Sheets: Number Format option of horizontal axis labels in a histogram
- Textjoin with header values
- Specifying a time span / duration constant in Google Sheets formula over 24h
- If spreadsheet ID is not equal to and number of rows less than?
- Using a gs function with an input from a selection in html on appscript
- Generate unique random numbers in Google Sheets