I’m looking for a way to replace specific numbers or text in Google Sheets, but only when they match exactly.
I want to create a formula that changes certain values from column A and puts the result in column D. The problem is that partial matches are also getting replaced when I don’t want them to.
I tried this formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3;"50";"12");"150";"25");"5050";"800") but it’s not working right. When I have 5050 in my cell, it becomes 1212 instead of 800 because it replaces the “50” parts twice.
I also tested REGEXMATCH and REGEXREPLACE functions but couldn’t get them to work with multiple conditions. The OR operator seems broken and AND just stacks the results together.
I need a simple formula that other people can understand and modify later. Scripts would be too complicated for my team to maintain. Can someone help me fix this?
Your SUBSTITUTE isn’t working because of the order. You’re letting shorter patterns match parts of longer ones. Put the longest strings first: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"5050","800"),"150","25"),"50","12"). See how 5050 comes first, then 150, then 50? This way it replaces the full 5050 with 800 before the “50” part can mess things up. I’ve done this in tons of projects where I needed exact matches without getting into regex. Always go longest to shortest - fixes the overlapping match issue every time.
Try a nested IF with EXACT function: =IF(EXACT(A3,"5050"),"800",IF(EXACT(A3,"150"),"25",IF(EXACT(A3,"50"),"12",A3))). This only replaces perfect matches and leaves everything else alone. It reads left to right, stops at the first match. I like this for numbers because it’s bulletproof - no partial matches, and non-technical people can follow it easily. Just add more IF statements for extra replacements.
word boundaries in regex work great for this! =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A3,"\b50\b","12"),"\b150\b","25"),"\b5050\b","800") the \b ensures it only matches complete numbers, not partial ones. tested it yesterday and it worked perfectly.