Substitute using a named list

**Hnorgaar** · 08-02-2018, 08:55 AM

Is there a way to use Substitute together with Match finding what to substitute from a named list, or another solution

This is what I have tried with no good result

SUBSTITUTE(A15;MATCH(A15;Exception;1);"@")

A15=044108 0584C. (Racing) When mounting on Diesel models the original hanger (part no 6Q0 253 144F) & the 2 original bolts (part no N0195269) must be used
All the parts in brackets are in a list named Exception

And the result im looking for is = 044108 0584C. @ When mounting on Diesel models the original hanger @ & the 2 original bolts @ must be used

Thx

Henrik

**CRIMEDOG** · 08-02-2018, 05:32 PM

#1 instead of ; use ,
#2 is the Named list one column?

I am thinking you need to replace the Match with Index and Match

**FDibbins** · 08-02-2018, 05:42 PM

@ Crimedog, I have a feeling, due to regional settings, Bangkok uses ; instead of ,

@ Hnorgaar perhaps it would help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**Hnorgaar** · 08-03-2018, 01:19 AM

Basically what Im looking for is NOT to use Substitute(Substitute(Substitute etc, as the strings to substitute for, can grow, now Im looking for 8 strings.

1. Im am using ; due to regional settings
2. I will do that as soon as possible

Henrik

**FDibbins** · 08-03-2018, 01:29 AM

OK thanks, we will await your sample file

**Hnorgaar** · 08-03-2018, 03:13 AM

Hi again

As I said, im trying to find a better and easier formula for column B, my tries is in column C. and is not working
All the possibilities will be in the list in sheet Exceptions and have a name, "Exception".
Hope this inf is enough

Thx for your time

Henrik