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!!!
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โ)
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.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |