Substitute function - 64 levels reached

[Redien]

Greetings,
please I have a question regarding the Substitute function. I have reached 64 levels and I would like to ask if it is possible to shorten the formula. I have an idea to make it maybe as a VLOOKUP and a SUBSTITUTE. I was thinking about creating a table with two columns. First is what it should look for and second with what it should be replaced but I am not sure if it is possible or how the formula should look like.
Words that have to be substituted are mostly full names of schools that need to be shortened to have max. 30 letters, unfortunately, I can not just cut the names as it needs to still make sense.
To have a better understanding, for example - Elementary School - Ele. Sch. / Science elementary school - Sc. ele. sch. / University of science and technology - Uni. of sc. and tech., etc.

Here is the current formula (Czech language):


=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(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(Paste!F3,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,""),"Základní škola a mateřská škola","ZŠ a MŠ"),"příspěvková organizace","p.o."),"přírodovědných","přírod."),"Základní a mateřská škola","ZŠ a MŠ"),"Školní jídelna","ŠJ"),"Základní Škola","ZŠ"),"Dětský domov","DD"),"Mateřská Škola","MŠ"),"Dům Sociálních Služeb","DDS"),"služeb","sl."),"Jazyková","Jaz."),"akademie","akad."),"obchodní","obch."),"jazykové","Jaz."),"Střední Průmyslová Škola","SPŠ"),"elektrotechnická","ele."),"Střední škola","SŠ"),"Mateřská škola","MŠ"),"Školní","Škol."),"gymnázium","gym."),"Základní škola","ZŠ"),"Dům sociálních služeb","DSS"),"ZÁKLADNÍ ŠKOLA A MATEŘSKÁ ŠKOLA","ZŠ a MŠ"),"obchodu","obch."),"zdravotnická","zdrav."),"gastronomie","gast."),"matematiky","mat."),"Střední průmyslová škola","SPŠ"),"sociálních","soc."),"sociální","soc."),"Školní jídelna","ŠJ"),"školní jídelna","ŠJ"),"Mateřská školka","MŠ"),"/",""),"základní škola","ZŠ"),"Hotelová škola a Obchodní akademie","HŠ a OA"),"Základní","ZŠ"),"Vyšší odborná škola","VOŠ"),"Hotelová škola","HŠ"),"Obchodní akademie","OA"),"Sociální","Soc."),"mateřská škola","MŠ"),"náměstí","nám."),"Střední odborná škola","SOŠ"),"Střední odborné učiliště","SOU"),"ZÁKLADNÍ ŠKOLA","ZŠ"),"MATEŘSKÁ ŠKOLA","MŠ"),"ŠKOLNÍ JÍDELNA","ŠJ"),"dětský domov","DD"),"střední škola","SŠ"),"dětský domov","DD"),"přísp. organizace","p. o."),"Střední lesnická škola","SLŠ"),"-západ",""),"-východ","")

Thank you very much for any advice or help!
Best regards

 

[RoryA]

Are you replacing the entire cell content, or just part?

 

[Redien]

Both cases. It can be part but also a whole cell when the name matches the rule.
For example: ,"Střední Průmyslová Škola","SPŠ"

 

[RoryA]

I'd probably use a lookup table for the whole cell matches, then a table of substitutions for the partial match replacements. Are there ever more than one partial replacements to be made in the same cell?

 

[Redien]

I am not sure but probably yes. It does sometimes combine two or more rules for one cell. If I understand you question correctly.
Example: Obchodní Gymnázium - "gymnázium","gym."),"obchodní","obch.")
Unfortunately, this is the second issue I am facing - Upper and lower cases. Almost every Substitute noted in the formula should be two times as function is case sensitive

 

[RoryA]

You can just convert the case of the cell using UPPER (or LOWER), do the substitutions and then convert back using say PROPER.

From the sounds of it though, I'd probably use code for this. Easier to loop through the substitutions table.

 

[Redien]

Ok, I will try to do so.
Is there a possibility to make a function that would insert something into an empty cell like ,,Paste,, or ,,Insert,,?
I got the idea to make functions Upper/Lower than Proper on a separate sheet as you said and then insert it back. So there could be more Substitute functions in more cells.
I am not experienced in excel so something like code or macro is foreign village for me