2

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.

Losai
  • 329
  • 2
  • 9

1 Answers1

3

To me the following worked:

enter image description here

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 to HelloYou. 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.
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Sir, could you share the working file please! – Mayukh Bhattacharya Mar 18 '22 at 20:22
  • 1
    @MayukhBhattacharya, [here](https://1drv.ms/x/s!AmlmnAMqhNdL5H4PxN3WmHaCx1mB?e=FmGL64) you go. Take it, I don't want to see these formulas nomore haha =) – JvdV Mar 18 '22 at 20:24
  • Sir, downloaded, than you so much, one thing your last formula on my query worked superb. Just amazing I dont have words to explain, how you manage to do this I am really surprised, Sir, please if possible do create some posts on 14 New Functions by Microsoft ! – Mayukh Bhattacharya Mar 18 '22 at 20:27
  • @Jvdv Also working for me. Its magical! Big Thanks to you. – Losai Mar 18 '22 at 20:30
  • 1
    I am reading about these functions! They are trully a very early christmas gift. If any question comes along I may drop answer that give alternatives using these formulae! Glad yours is working so nicely. – JvdV Mar 18 '22 at 20:31
  • Alright @JvdV Sir, Sir `VSTACK` & `HSTACK` seems fascinating ! – Mayukh Bhattacharya Mar 18 '22 at 20:35
  • 1
    Not just that `TEXTSPLIT()` is going to be a gamechanger for sure too. Exciting times. – JvdV Mar 18 '22 at 20:37
  • 2
    I wonder if textsplit will have possibilities just as versatile as "your" filterxml split version. This would also mean it's usable for users other than running Windows. – P.b Mar 18 '22 at 20:46
  • 1
    @P.b, as far as I can see, [yes](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7). You should be able to feed an array of either characters or strings to split on. What will be lacking is the xpath to filter the result but ms wants us to use `FILTER()` and all other sorts of stuff for that. A shame I don't have these functions ready to try just yet. – JvdV Mar 18 '22 at 20:49
  • 1
    You can split by row or column. I wonder if you are able to split both ways. Let say comma downwards and semicolon sideways, or the other way around – P.b Mar 18 '22 at 21:00
  • 1
    @JvdV - crazy I had not seen [this](https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066). Since I finally got my hands on LAMBDA, I have been building a library to achieve most of these myself as they are frequently needed. So... that will soon be obsolete. – mark fitzpatrick Mar 19 '22 at 07:30