CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
nehshere
nehshere

Reputation: 5

Multiple substitue or replace text in excel using a table

I'd like to create a super substitute function in excel (if there is such a thing). For my needs, nesting is insufficient because my substitution list is long.

What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text.

Sample Data

If we look at the above example, the columns R and S consists of my table, with column R as the original text that I wanted replaced with the equivalent row entry in column S, if found in my list. My data in column V is the list that I want to work on. I want the formula in column W to look at the equivalent entry in column V and lookup in the table for any matches within the text. If there is a match, then I want that text from column R replaced with the text in column S. In some cases there can be 2 matches, as with the top example of 'LHR:JFK' which should ideally get replaced to 'LON:NYC'.

There are ways to do this with VBA but I would like to know if there is an Excel formula option for the same as I don't know where to begin with VBA.

Your help is greatly appreciated.

Thanks, Neha

Upvotes: 0

Views: 3812

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

This will do as you ask:

=SUBSTITUTE(SUBSTITUTE(V2,LEFT(V2,3),IFERROR(VLOOKUP(LEFT(V2,3),R:S,2,FALSE),LEFT(V2,3))),RIGHT(SUBSTITUTE(V2,LEFT(V2,3),IFERROR(VLOOKUP(LEFT(V2,3),R:S,2,FALSE),LEFT(V2,3))),3),IFERROR(VLOOKUP(RIGHT(SUBSTITUTE(V2,LEFT(V2,3),IFERROR(VLOOKUP(LEFT(V2,3),R:S,2,FALSE),LEFT(V2,3))),3),R:S,2,FALSE),RIGHT(SUBSTITUTE(V2,LEFT(V2,3),IFERROR(VLOOKUP(LEFT(V2,3),R:S,2,FALSE),LEFT(V2,3))),3)))

enter image description here

Upvotes: 1

Related Questions