Stacking multiple formulas in one column


#1

I have a base with a load of cars that have descriptions of their features in shorthand.

Here’s an example:

Alfa-Romeo Giulietta (White - 49K miles) 1.4L MultiAir Veloce A/C Alloys B/T C/C 1/2 Leather Pan Roof P/S

I’d like to translate the shorthand using substitute formulas like the below:

SUBSTITUTE(Car, “Pan Roof”, “Panoramic Roof”)
SUBSTITUTE(Car, “A/C”, “Air Conditioning”)
SUBSTITUTE(Car, “P/S”, “Parking Sensors”)
SUBSTITUTE(Car, “B/T”, “Bluetooth”)
SUBSTITUTE(Car, “C/C”, “Cruise Control”)
SUBSTITUTE(Car, “1/2 Leather”, “Half Leather Interior”)
SUBSTITUTE(Car, “Alloys”, “Alloy Wheels”)

Is there anything I can put in as a separator so that I can do all these formulas in one column? Separating with an ‘&’ just concatenates each new permutation of the string together.

There are another 20 or so features that have shorthand reference codes, so really would rather avoid having to do a whole new column for each substitution.


Help with substitute for multiple formulas in one column
#2

Nesting? Not that it’s easily readable of course…

SUBSTITUTE(SUBSTITUTE(Car, "Pan Roof", "Panoramic Roof"), "A/C", "Air Conditioning")

But it’s not that bad either.


#3

Good idea.

I’ll give it a go, thanks @Tuur!


#4

Got it working, thanks dude.

(I now have a formula with 142 nested substitutes, but it works :joy:)


#5

Good job! I know exactly what that looks like. :grimacing: :scream:

:rofl: