I have in (D2) very dirty string:
Ⱦ[ḫ]îs ɨs síṁƥḽẽ ~s?tring $with Ḑiă{cr}îtíc#s ẵɲd Ṧɏ<ṃƀǿⱡs. /123
To clean this I use two great formulas (Thanks @Jvdv)
First - Cleaning string from unwanted characters:
=TRIM(CONCAT(IF(MMULT(IFERROR(SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);{"ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ"\"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ /0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"});0);{1;1});MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);" ")))
Only First formula return next:
Ⱦ ḫ îs ɨs síṁƥḽẽ s tring with Ḑiă cr îtíc s ẵɲd Ṧɏ ṃƀǿⱡs /123
Second - Converting diacritics to alphabetical:
=CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");FIND(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1)))
Only Second formula return next:
T[h]is is simple ~s?tring $with Dia{cr}itic#s and Sy<mbols. /123
Both formulas (one after one) return next:
T i is is simple s tring with Dia cr itic s and sy mbols /123
I expect to see the same string, but with correct spacing:
This is simple string with Diacritics and Symbols /123
For this goal I combine both formulas in one single:
=SUBSTITUTE(CONCAT(IF(ISNUMBER(SEARCH("~"&MID(CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");FIND(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1)));ROW(X$1:INDEX(X:X;LEN(CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");FIND(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1))))));1);{" 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"}));MID(CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");FIND(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1)));ROW(X$1:INDEX(X:X;LEN(CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1));LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");SEARCH("~"&MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT";"UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ");FIND(MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1);CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ";"ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ"));1));MID(D2;ROW(X$1:INDEX(X:X;LEN(D2)));1))))));1);"^^"));"^^";"")
And its working. But something is not right in my combination, because is an excessive long length.
I ask experts for a nicer, optimized combination of this two array formulas, using Excel-2019, without VBA or Helper Columns. Thank You.
CodePudding user response：
To me the following worked:
Formula in A2:
=CONCAT(IFERROR(IF(EXACT(LOWER(MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1)),MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1)),LOWER(MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT","UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ"),SEARCH("~"&MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ","ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ")),1)),MID(CONCATENATE("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBCCCCCCCCCDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFGGGGGGGGGHHHHHHHHHIIIIIIIIIIIIIIIIIIIJKKKKKKKLLLLLLLLLLLLMMMNNNNNNNNNNNNOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOPPPPRRRRRRRRRRRSSSSSSSSSSTTTTTTTTTTT","UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUVVWWWWWWXXYYYYYYYYYYYYZZZZZZZ"),FIND(MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),CONCATENATE("ÁÀȦÄǍĀÃÅĄȺẤẦẮẰǠǺǞẪẴẢȀȂẨẲẠḀẬẶĂÂḂɃƁḄḆĆĊĈČÇȻḈƇƆḊĎḐĐƊḌḒḎÐƉÉÈĖÊËĚĔĒẼĘȨɆẾỀḖḔỄḜẺȄȆỂẸḘḚỆÉÈÊËḞƑǴĠĜǦĞḠĢǤƓḢĤḦȞḨĦḤḪⱧÍÌÏǏĬĪĨĮƗḮỈȈȊỊḬÍÌÏÎĴḰǨĶƘḲḴⱩĹĿĽⱢⱠĻȽŁḶḼḺḸḾṀṂŃǸṄŇÑŅƝṆṊṈÑŊÓÒȮÔÖǑŎŌÕǪŐỐỒƟØṒṐṌȪỖṎǾȬǬỎȌȎƠỔỌỚỜỠỘỞỢÓÒÔÖÕṔṖⱣƤŔṘŘŖɌⱤȐȒṚṞṜŚṠŜŠṤṦṢṨŞȘṪŤƬṬȚƮṰṮȾŢŦ","ÚÙÛÜǓŬŪŨŮŲŰɄǗǛṸṺỦȔȖƯỤṲỨỪṶṴỮỬỰÚÙÛÜṼṾẂẀẆŴẄẈẊẌÝỲẎŶŸȲỸɎỶƳỴÝŹŻẐŽƵẒẔ")),1)),IF(ISNUMBER(SEARCH("~"&MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),"/ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")),MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),"")))
But granted, this is a painfull and seriously hard debugging for anyone if something come up that needs changing. Please don't ask me about it =)
- Note: This worked with given sample data, but something simple like:
Hello?You, will turn toHelloYou. Quite frankly, there is also simply no way to tell the difference in pure logic wheather or not you would want a space or not in this case if the input is this cluttered.