More than 64 levels of =Substitute

[qwzky]

Hi, community!
I am working at a sheet that should substitute letters and groups of letters with other letters. The problem is: this requires me more than 64 levels of nesting. How can I shorten this formula? Or is there any alternative to =Substitute function?

Fonetică 2.xlsx
	B	C	D	E
2
3		Word	tăierii
4
12
13		Phonetic Translation	[tă-ie-rii]
14
17
18		Sounds	Vowels: [ăeii] ● Consonants: [tr] ● Semivowels:[iii]
19
Sheet3

Cell Formulas
Range	Formula
D13	D13	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!F8,"ači","ač"),"eči","eč"),"rči","rč"),"iči","ič"),"oči","oč"),"uči","uč"),"ăči","ăč"),"âči","âč"),"aĝi","aĝ"),"eĝi","eĝ"),"iĝi","iĝ"),"oĝi","oĝ"),"uĝi","uĝ"),"ăĝi","ăĝ"),"lĝi","lĝ"),"aK'i","aK'"),"eK'i","eK'"),"iK'i","iK'"),"oK'i","oK'"),"uK'i","uK'"),"ăK'i","ăK'"),"âK'i","âK'"),"aG'i","aG'"),"eG'i","eG'"),"iG'i","iG'"),"oG'i","oG'"),"uG'i","uG'"),"ăG'i","ăG'"),"âG'i","âG'"),"K'iu","K'u"),"K'ia","K'a"),"ĝii","ĝi"),"lči","lč"),"nči","nč"),"nĝi","nĝ")
D18	D18	="Vowels: "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D13,"b",""),"k",""),"d",""),"f",""),"g",""),"h",""),"j",""),"l",""),"m",""),"n",""),"p",""),"r",""),"s",""),"ș",""),"t",""),"ț",""),"v",""),"z",""),"č",""),"Č",""),"ĝ",""),"Ĝ",""),"K'",""),"G'",""),"oi","o"),"ie","e"),"-","")&" ● Consonants: "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D13,"a",""),"e",""),"i",""),"o",""),"u",""),"ă",""),"î",""),"â",""),"-","")&" ● " & "Semivowels:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D13,"oi","i"),"oa","o"),"ie","i"),"ui","i"),"ai","i"),"eu","u"),"ei","i"),"iu","i"),"a",""),"b",""),"k",""),"d",""),"f",""),"g",""),"h",""),"j",""),"l",""),"m",""),"n",""),"p",""),"r",""),"s",""),"ș",""),"t",""),"ț",""),"v",""),"z",""),"č",""),"Č",""),"ĝ",""),"Ĝ",""),"K'",""),"G'",""),"re",""),"e",""),"ă",""),"î",""),"â",""),"-","")

 

[DanteAmor]

Here is a UDF that you can try:

Function SubstituteLevels(s As String, rng As Range)
  Dim c As Range
  For Each c In rng
    s = Replace(s, c.Value, c.Offset(, 1).Value)
  Next
  SubstituteLevels = s
End Function

You should use helper columns like this:

Dante Amor
	A			D	E		G	H	I	J	K	L	M	N
1									Vowels		Consonants		Semivowels
2							Original	New	Original	New	Original	New	Original	New
3							ači	ač	b		a		oi	i
4							eči	eč	k		e		oa	o
5							rči	rč	d		i		ie	i
6							iči	ič	f		o		ui	i
7							oči	oč	g		u		ai	i
8							uči	uč	h		ă		eu	u
9							ăči	ăč	j		î		ei	i
10							âči	âč	l		â		iu
11							aĝi	aĝ	m		-		ai
12							eĝi	eĝ	n				b
13				[tă-ie-rii]			iĝi	iĝ	p				k
14							oĝi	oĝ	r				d
15							uĝi	uĝ	s				f
16							ăĝi	ăĝ	ș				g
17							lĝi	lĝ	t				h
18				Vowels: [ăeii] ● Consonants: [tr] ● Semivowels:[iii]			aK'i	aK'	ț				j
19							eK'i	eK'	v				l
20							iK'i	iK'	z				m
21							oK'i	oK'	č				n
22							uK'i	uK'	Č				p
23							ăK'i	ăK'	ĝ				r
24							âK'i	âK'	Ĝ				s
25							aG'i	aG'	K				ș
26							eG'i	eG'	G				t
27							iG'i	iG'	oi	o			ț
28							oG'i	oG'	ie	e			v
29							uG'i	uG'	-				z
30							ăG'i	ăG'					č
31							âG'i	âG'					Č
32							K'iu	K'u					ĝ
33							K'ia	K'a					Ĝ
34							ĝii	ĝi					K
35							lči	lč					G
36							nči	nč					re
37							nĝi	nĝ					e
38													ă
39													î
40													â
41													-
Hoja16

Cell Formulas
Range	Formula
D13	D13	=SubstituteLevels(Sheet1!F8,G3:G37)
D18	D18	="Vowels: "&SubstituteLevels(D13,I3:I29)&" ● Consonants: "&SubstituteLevels(D13,K3:K11)&" ● Semivowels:"&SubstituteLevels(D13,M3:M41)

 

[qwzky]

This is Awesome Thank you a lot

 

[qwzky]

it worked for a second, but now I can't make it. Somehow, I changed something, but can't figure out what exactly. Can you give your insight? (I don't want to reference other sheets; I want to reference cells within the same sheet)

Fonetică 2.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1							Vowels		Semivowels		Consonants			Letter groups to sound groups
2
3							Original_V	New_V	Original_S	New_S	Original_C	New_C		Original		New
4							a	v	ai	vs	b	c		ači		ač
5							ă	v	au	vs	k	c		eči		eč
6							î	v	ea	sv	d	c		čea		ča
7		Word	cârmaci				â	v	ei	vs	f	c		c		k
8		Hyphen	câr-maci						eu	vs	g	c
9		Phonetic1	[câr-mači]						ia	sv	h	c
10		Phonetic2	#NAME?						ie	sv	j	c
11		Phonetic3	#NAME?						ii	vs	l	c
12		Types of sounds	#NAME?						io	sv	m	c
13									iu	sv	n	c
14											p	c
15											r	c
16											s	c
17											ș	c
18											t	c
19											ț	c
20											v	c
21											z	c
22											č	c
23												c
24												c
25												c
26
27
28
29
30
31
32
33
34
35
Sheet1

Cell Formulas
Range	Formula
J8	J8	=VLOOKUP(J7,$D$100:$F$706567,3,0)
J9	J9	=CONCATENATE("["&(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J8,"ce","če"),"ci","či"),"ge","ĝe"),"gi","ĝi"),"ghe","G'e"),"ghi","G'i"),"x","ks")&"]"))
J10	J10	=SubstituteLevels(J9,W4:X7)
J11	J11	=SubstituteLevels(J10,R4:R22)
J12	J12	="Vowels: "&SubstituteLevels(J11,R4:R22)&" ● Consonants: "&SubstituteLevels(D13,K3:K11)&" ● Semivowels:"&SubstituteLevels(D13,M3:M41)

 

[DanteAmor]

Don't use merged cells.
In the formula you must enter the cell with the original text, the second parameter must only be a column. It should be the left column.

Look at my example, I only put the left column:
SubstituteLevels(Sheet1!F8,G3:G37)
In this example, the left column is column G, range G3 to G37. The formula assumes that the new value is in the right column.

Follow the prompts according to my example. You will have no problems with the function.

 

[qwzky]

I have tried all the things you said: unmerged cell, only the left column. I also tried other things I said to me (ha-ha-ha): only the right column, both columns, followed the „Sheet1!F8” and changed to the word cell... I will unmark your solution, in order to not confuse the community, until we get this through

 

[DanteAmor]

But my solution works. But don't worry, I'll gladly explain again.
This is how you should put the data.

For the formula in cell J12.
The first argument of the formula is J7.
The second argument of the formula is U3:U7. (And that's it.)

In cells U3:U7 you put the original values and in cells V3:V7 you put the new values.
Note: In the formula it is not necessary to put the reference V3 to V7.
Take a look at my example, No merged cells:

varios 20feb2022.xlsm
	I	J	K										U	V
1													Letter groups to sound groups
2
3													Original	New
4													ači	ač
5													eči	eč
6													čea	ča
7	Word	cârmaci											c	k
8	Hyphen
9	Phonetic1
10	Phonetic2
11	Phonetic3
12	Types of sounds	Vowels: kârmaki
Hoja3

Cell Formulas
Range	Formula
J12	J12	="Vowels: "&SubstituteLevels(J7,U3:U7)

 

[qwzky]

Yep, all clear now Thank you, Dante! Thank you for your patience! Now it DOES work.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.