[XLF] Vietamese TELEX Converter

' FILTERING VOWELS PHI NHAP
=
IF( LEN(B2)=1,
	B2,
IF( LEN(B2)=2,
	IF( OR(AND(RIGHT(B2,1)="a",LEFT(B2,1)<>"o"),RIGHT(B2)="i",RIGHT(B2)="m",RIGHT(B2)="n"),
		LEFT(B2,1),
	IF( AND(OR(RIGHT(B2,1)="o",RIGHT(B2,1)="u",RIGHT(B2,1)="y",),LEFT(B2,1)<>"u"),
		LEFT(B2,1),
		RIGHT(B2,1)
	)),
IF( LEN(B2)=3,
	IF( OR(RIGHT(B2,1)="g",RIGHT(B2,1)="h"),
		LEFT(B2,1),
		MID(B2,2,1)
	),
	IF(	RIGHT(B2,1)="n",
		MID(B2,3,1),
		MID(B2,2,1)
	)
)))

' FILTERING VOWELS NHAP THANH
' IF(OR(RIGHT(B2,1)="c",RIGHT(B2,1)="p",RIGHT(B2,1)="t")
=IF(RIGHT(B2,1)="h",MID(B2,LEN(B2)-2,1),MID(B2,LEN(B2)-1,1))

' JOINING TONE MARKS
=
IF( D2="z",
	C2,
IF( D2="f",
	SWITCH(C2,"a","à","ă","ằ","ơ","ờ","â","ầ","e","è","ê","ề","o","ò","ô","ồ","u","ù","ư","ừ","i","ì","y","ỳ"),
IF( D2="s",
	SWITCH(C2,"a","á","ă","ắ","ơ","ớ","â","ấ","e","é","ê","ế","o","ó","ô","ố","u","ú","ư","ứ","i","í","y","ý"),
IF( D2="r",
	SWITCH(C2,"a","ả","ă","ẳ","ơ","ở","â","ẩ","e","ẻ","ê","ể","o","ỏ","ô","ổ","u","ủ","ư","ử","i","ỉ","y","ỷ"),
IF( D2="x",
	SWITCH(C2,"a","ã","ă","ẵ","ơ","ỡ","â","ẫ","e","ẽ","ê","ễ","o","õ","ô","ỗ","u","ũ","ư","ữ","i","ĩ","y","ỹ"),
IF( D2="j",
	SWITCH(C2,"a","ạ","ă","ặ","ơ","ợ","â","ậ","e","ẹ","ê","ệ","o","ọ","ô","ộ","u","ụ","ư","ự","i","ị","y","ỵ")
)

' SPLIT CONSONANT
=
IF(AND(LEFT(B2,1)<>"a",LEFT(B2,1)<>"i",LEFT(B2,1)<>"u",LEFT(B2,1)<>"e",LEFT(B2,1)<>"o",LEFT(B2,1)<>"y"),
	IF(MID(B2,3,1)="h",
		LEFT(B2,3),
	IF(OR(LEFT(B2,1)=MID(B2,2,1),MID(B2,2,1)="h",MID(B2,2,1)="g",MID(B2,2,1)="r",LEFT(B2,1)="q",AND(LEFT(B2,1)="g",MID(B2,2,1)="i")),
		LEFT(B2,2),
	LEFT(B2,1)
)))

' FIXING CONSONANT
=CONCATENATE(
	IF(AND(B2="gi",LEFT(C2,1)="i"),
		"g",
	IF(AND(B2="g",OR(LEFT(C2,1)="i",LEFT(C2,1)="e")),
		"gh",
	IF(AND(B2="ng",OR(LEFT(C2,1)="i",LEFT(C2,1)="e")),
		"ngh",
	IF(AND(B2="c",OR(LEFT(C2,1)="i",LEFT(C2,1)="e")),
		"k",
	IF(AND(B2="c",LEN(C2)>1),
		IF(OR(
			AND(LEFT(C2,1)="u",OR(MID(C2,2,1)="y",MID(C2,2,1)="e")),
			AND(LEFT(C2,1)="o",OR(MID(C2,2,1)="a",MID(C2,2,1)="e")),
			AND(LEN(C2)=2,LEFT(C2,1)="u",MID(C2,2,1)="o"),
			AND(LEFT(C2,1)="u",OR(
				AND(MID(C2,2,1)="o",MID(C2,3,1)="w"),
				AND(MID(C2,2,1)="a",MID(C2,3,1)="a")
				)
			)),
			"q",
		B2
		),
	B2
	))))),
	IF(AND(B2="c",LEN(C2)>1,LEFT(C2,1)="o",OR(MID(C2,2,1)="a",MID(C2,2,1)="e")),
		CONCATENATE("u",RIGHT(C2,LEN(C2)-1)),
	C2
	),
	D2
)

' FILL TONE MARKS
=SWITCH(I2,1,"z",2,"f",3,"r",4,"x",5,"s",6,"j")

' FILL CONSONANTS
=SWITCH(H2,1,"",2,"b",3,"c",4,"ch",5,"d",6,"dd",7,"g",8,"gi",9,"h",10,"kh",11,"l",12,"m",13,"n",14,"ng",15,"nh",16,"ph",17,"r",18,"s",19,"t",20,"th",21,"tr",22,"v",23,"x")

' COMBINE
=IF(D2<>FALSE,
	IF(D2="dd",
		CONCATENATE("đ",E2),
	IF(AND(D2="gi",OR(LEFT(E2,1)="i",LEFT(E2,1)="ì",LEFT(E2,1)="ỉ",LEFT(E2,1)="ĩ",LEFT(E2,1)="í",LEFT(E2,1)="ị")),
		CONCATENATE("g",E2),
	IF(OR(AND(D2="qu",LEFT(E2,1)="u"),AND(D2="qu",LEFT(E2,1)="o")),
		CONCATENATE(D2,RIGHT(E2,LEN(E2)-1)),
	CONCATENATE(D2,E2)
	))),
E2
)