1

I have a spreadsheet contains a list of 24k items found in a grocery store. Most items have abbreviated words such as: w/ for with; choc for chocolate; sauv for sauvignon. Yet the list contains both sauv and sauvignon thus is I replaced all sauv with sauvignon I would get some instances of sauvignonignon.

How would I create a nestled substitute formula that would find and replace these terms? Or is there a better formula for find and replace?

3
  • Add a space to the end of each item through a formular, do your substitute while searching for "sauv " (notice the space), remove spaces
    – Eric F
    Jan 9, 2015 at 20:02
  • 1
    If the spaces don't work (such as the word being at the end of the field) you could do one replace of "sauv" with "sauvignon", then do a 2nd repcale of "sauvignonignon" with "sauvignon".
    – techturtle
    Jan 9, 2015 at 20:19
  • Thanks, the space trick works. I also really enjoyed using the 2nd replace function.
    – Hyetigran
    Jan 10, 2015 at 1:02

1 Answer 1

0

You can get a list of variations to deal with by using Find | Find All and... making out a list.

Gotta do that for any of the offered ideas. But what I like to do from there is pick the minimal one, if it doesn't seem it would mix with unrelated shorter things (they should've appeared on the Find All listing if they existed so not the biggest concern at this point). Once picked, Find and Replace all the longer versions down to the short one you picked. Once all are shortened down do it back for all of them at one time with one last F&R.

So chocolate becomes choco, then both become chocolate, no chocolatelate's need apply.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .