Search code examples
excelexcel-formulaarray-formulasdata-cleaningexcel-2019

Combine Two Array Formulas

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.

Solution

  • 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.