How can I use python or the reduce function to replace certain phrases in a string so I don't have to use a bunch of nested SUBSTITUTE functions?
I am working on a personal project where I have a cell with a string of phrases separated by commas.
Example:
Apples: 2, oranges: 1, Bananas, lemons
Apples: 5, Bananas: 2
Grapes
Strawberries
I want to take these cells and replace all instances as follows:
| Old | New |
|---|---|
| Apples | Pears |
| Bananas | Plantains |
| Lemons | Limes |
| Grapes | Elderberries |
| Strawberries | Blueberries |
In the actual data set I'm working with, the list of phrases is much longer, but hopefully this gets my idea across. Using nested substitute functions could work, but it would be a pain to type out and end up unreadable and difficult to work with. Is there a better way to do this?
I tried using the REDUCE formula but the output I get is this:
| Phrase | Old | New | Reduce Output |
|---|---|---|---|
| Apples: 2, oranges: 1, bananas, lemons | Apples | Pears | Blueberries |
| Bananas | Plantains | Blueberries | |
| Lemons | Limes | Blueberries | |
| Grapes | Elderberries | apples: 3, oranges: 2, bananas, lemons | |
| Strawberries | Blueberries | apples: 3, oranges: 2, bananas, lemons |
When I was expecting the output to be this:
Pears: 3, oranges: 2, plantains, limes
The reduce formula looks like this:
=REDUCE(LOWER($B$2:$B$6),LOWER($C$2:$C$6),LAMBDA(old,new,SUBSTITUTE(LOWER(A2),old,PROPER(new))))
What am I doing wrong here? This is the first time I've used the reduce function. I am a bit familiar with python, if that would make this easier.
[link] [comments]
Want to read more?
Check out the full article on the original site