SUBSTITUTE problem

[Vasilis_Antonio]

Hi, i need to replace the notes of a long text with the next note. Example “Nto Re Re Mi” change to “Re Mi Mi Fa”.
i try
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(([transporto]);
“Nto”;“Re”);
“Re”;“Mi”);
“Mi”;“Fa”);
“Fa”;“Sol”);
“Sol”;“La”);
“La”;“Si”);
“Si”;“Nto”)
but it everything to Nto that is the last. Please help!!!

[alphacp]

In nested substitutions you generally have to work backwards. So ABCD to BCDE you would start with D first and change that to E, then C to D etc. If this is more circular such as ABCD to BCDA then you would initially need to change D to something different like X and then at the end change X back to A

Example:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([transporto],“Nto”,“XXX”),“Si”,“Nto”),“La”,“Si”),“Sol”,“La”),“Fa”,“Sol”),“Mi”,“Fa”),“Re”,“Mi”),“XXX”,“Re”)

[Austin]

An additional option, is this is music(?), you could make the “note” a number and just add 1,2,3 etc if you need to make everything the next note. This way you can avoid all the headache of having to deal with a character based list and have a numerical list. You then just make a virtual column or make the number a reference to a note table that would translate 0=Nto 1=Re 2=Mi etc.
When you go over 7 you reset back to 0 with mod 7. I hope the concept makes sense there.