SUBSTITUTION Problem!

**niceguy21** · 09-26-2012, 10:46 AM

Hello Excel friends,

I have this apparently simple problem to help solve.

Looking at the attached spreadsheet it should be clear that the output column should always output what is in the desired output column.

I.E that the parameters in the first 7 columns should be substituted correctly in the format listed in the input column, and this is to be outputted in the output column, such that one can copy down instantly

Is there a quick formula based solution to this?

Kind Regards,

**Kevin UK** · 09-26-2012, 11:16 AM

Hi niceguy21

In I3:

Formula:

Please Login or Register  to view this content.

**niceguy21** · 09-26-2012, 12:01 PM

Originally Posted by Kevin UK

Hi niceguy21

In I3:

Formula:

Please Login or Register  to view this content.

Many thanks for that useful tip Kevin,

I believe I stated this problem incorrectly actually.. maybe you can further help...

find attached the revised sheet: we are looking for a formula that can identify the parameters (in square brackets) in the input column and then output the correct parameters (see desired output).

once solution to this is to create multiple columns each one making a certain number of substitutions.

your thoughts...

**Kevin UK** · 09-26-2012, 12:09 PM

Hi niceguy21

You have got me beat on that one

Someone will come up with a solution. I will have another look later.

**niceguy21** · 09-26-2012, 12:18 PM

Originally Posted by Kevin UK

Hi niceguy21

You have got me beat on that one

Someone will come up with a solution. I will have another look later.

haha, yes, us too.. the problem is that the substitution formula only substitutes ''text'' on an individual input basis such that you can't just reference one of the cells.

**tigeravatar** · 09-26-2012, 12:48 PM

You could just have a lot of nested substitute functions:

Please Login or Register  to view this content.

Note that substitute is case sensitive though, so I had to change the inputs to http://Coke.com/abc/[DEVICE]/[COUNTRY] so that it would find [DEVICE] and [COUNTRY] because [device] and [country] aren't found due to the case sensitivity.

You could get around that by adding using the UPPER() function H3 within that formula, but then everything other than the replaced items would be capitalized in the result.

**niceguy21** · 09-26-2012, 01:28 PM

Originally Posted by tigeravatar

You could just have a lot of nested substitute functions:

Please Login or Register  to view this content.

Note that substitute is case sensitive though, so I had to change the inputs to http://Coke.com/abc/[DEVICE]/[COUNTRY] so that it would find [DEVICE] and [COUNTRY] because [device] and [country] aren't found due to the case sensitivity.

You could get around that by adding using the UPPER() function H3 within that formula, but then everything other than the replaced items would be capitalized in the result.

Many, Many thanks for your time on this... funnily enough we just worked out the exact same formula! although we have added in a Vlookup.

I guess the learning here is that substitution formula can use cell references!!!!