Multiple substitution?

[nightoul]

Hello. Is there a way to create a multiple substitution so that for a number of specific characters I get different ones? I need this for transposition of notes in music. So for example, for each letter "C" I get a "G", for a "D" an "A" and so on. The thing is that to cover up all the scale, I need a total of 7 substitutions.
Like this:

C - D
D - E
E - F#
F - G
G - A
A - B
B - C#

Thanks!

 

[Eric W]

Welcome to the forum.

There are a few ways:

	A	B	C	D	E	F
1	C	D		C A B	D B C#	D B C#
2	D	E
3	E	F#
4	F	G
5	G	A
6	A	B
7	B	C#

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula E1 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,A1,1),A2,2),A3,3),A4,4),A5,5),A6,6),A7,7),1,B1),2,B2),3,B3),4,B4),5,B5),6,B6),7,B7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F1 {=CONCAT(MID(CONCAT(B1:B7),FIND(MID(D1,(ROW(INDIRECT("1:"&LEN(D1)/2))-1)*2+1,2),CONCAT(A1:A7)),2))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

It's possible to string 7 SUBSTITUTE functions together to substitute each note at a time, but the problem is that on the first pass, you'll convert a C to a D, and on the second pass, you'll convert a D to an E, including the Cs you already converted. So to avoid that, you need to first substitute the source notes into an intermediate value (I used 1-7), then substitute 1-7 to the new notes. That's the formula in E1. Also note that in column A, each entry has 2 characters, the notes all have a space after them. This is to accommodate the sharp or flat symbols if necessary.

Option 2, if you have a newer version of Excel (2016 or Live), you can use the CONCAT function to shorten it a bit. That formula is in F1.

Finally, option 3, it would be very simple to write a UDF (User defined function), that would be much easier to understand and maintain.

Let me know if any of these options appeal, as well as how your input notes look like.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here's another CONCAT possibility if you have an Excel version with that function. It doesn't require the lookup table in the sheet.

Excel Workbook
	D	E
1	CAB	DBC#
2	FBEG	GC#F#A
Substitute Notes

 

[nightoul]

Thank you for your great replies. I have Excel 2013 so unfortunately the array formulas don't work (but maybe it's for some other reason, I have also different language settings but even if I translated the function names, nothing happened) but I think that Eric's suggestion about intermediate values is great. Maybe it will be easier to do this:

1. Assign numbers 1 to 7 to the notes of the scale (C to B in C major).
2. List the notes of the target scale (G, A, B, C, D, E, F, G in G major). I'm omitting the sharps/flats for now.
3. Ascertain the interval between the scales: perfect fifth.
4. Work out number correspondences. Like this:

C - 1
D - 2
E - 3
F - 4
G - 5
A - 6
B - 7

C must become G, so 1 > 5. D must become A and so on. We get: 1 > 5, 2 > 6, 3 > 7, 4 > 1, 5 > 2, 6 > 3, 7 > 4.
If the interval btw the source scale and target scale is different, these correspondences will shift. So you just need to work out 5 more (you have 7 notes which means 6 possibilities of transposition, again leaving out sharps/flats for which I think an independent second step could be invented, that would be easier. What do you think? I hope what I've outlined is not just restating your suggestions (I don't see into your formulas).

 

[Peter_SSs]

I didn't really understand post 4, but based on the earlier information, does this work for you?

Book1
	D	E
1	CAB	DBC#
2	FBEG	GC#F#A
Substitute Notes

Cell Formulas
Range	Formula
E1	=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,"A","b"),"B","c#"),"C","d"),"D","e"),"E","f#"),"F","g"),"G","a"))

 

[nightoul]

I didn't really understand post 4, but based on the earlier information, does this work for you?

	D	E
1	CAB	DBC#
2	FBEG	GC#F#A

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Substitute Notes​

Worksheet Formulas Cell Formula E1 =UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,"A","b"),"B","c#"),"C","d"),"D","e"),"E","f#"),"F","g"),"G","a")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It seems the formula didn't paste, would you please post it again? Thanks!

 

[nightoul]

Oh sorry, it must have been something with my browser. Thanks, this finally works!

 

[Peter_SSs]

Oh sorry, it must have been something with my browser. Thanks, this finally works!

Glad you got it sorted.