Combine Two Array Formulas
I have two array formulas:
1. Clean string from unwanted characters
2. Convert diacritics to alphabetical
Please Login or Register to view this content.
Both formulas works perfect.Please Login or Register to view this content.
How can I combine this two in one single formula?
I try to do this with IF or IFS, but something goes wrong.
I post trimmed versions of formulas.
In attached file is full versions of formulas.
Is this what you wanted? I replaced every instance of "D2" in one formula with the other formula, in cells A3 and A4.
Trying to look at what the formula does just made my head hurt, I'm afraid.
Thank you for good answer!Originally Posted by nick.williams
Yes, the expect result is that right, but I'm very scared about so biiiiiiiiig length
It can be shorter? Its possible?
Last edited by Losai; 03-18-2022 at 10:53 AM.
Hello Losai.
As I echo nick.williams on the head hurt I cannot offer a solution.
2 points though:
1.) I doubt you have just one string in your data field. Solution to this one string may not apply to others. More samples are almost always helpful.
2.) Rather than using arrays of literal characters had you considered nesting functions like UNICODE or UNICHAR or CODE or CHAR? These would shorten those formulas considerably.
Edit:
A 3rd point. I notice your literal arrays are comprised of a mix of rows and columns of characters. I would anticipate problems from that. If you continue with literal arrays can you combine all of those into one contiguous string?
Last edited by FlameRetired; 03-18-2022 at 11:38 AM.
Dave
I'm not sure about shorter, but you could make it more readable if you had a reference table with what is being replaced by what.
As for your actual formula, I think something in this format for the "cleaning" one would sort out your apparent spacing problem. I've assumed you won't have strings with "^^" in them, this is normally safe but might not be here. Presumably you can come up with some string that won't occur in your data though.
=SUBSTITUTE(CONCAT(IF(ISNUMBER(SEARCH("~"&MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),{"string_a","string_b"})),MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),"^^")),"^^","")
NB I've just put "string_a" and "string_b" instead of what you had in the file.
Last edited by nick.williams; 03-18-2022 at 12:01 PM. Reason: Typo
This does it all in one go for me, with your replacement reference table in F2:G335. I don't know if it works in Excel 2019.
=SUBSTITUTE(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(VLOOKUP("~"&MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),$F$2:$G$335,2,0)),VLOOKUP("~"&MID(D2,ROW(X$1:INDEX(X:X,LEN(D2))),1),$F$2:$G$335,2,0)),"^^")),"^^","")
NB there are a small number of duplicates in your replacement lists as well.
Of course, its a nice option to use helper column,
But I have a goal to use only formula, without VBA, and without helpers.
BTW. This version of your formula not working in excel 2019
Well you can always hard code the lookup table if you want to. I've uploaded a version which does it all in one cell with correct spacing, but the formula is still very long.
If I have some spare time at the weekend maybe I'll try and do a nicer version for 2019.
Wow!
Already, Thank You for correct spacing version.
I'm so happy with this!
I'll wait until you have time to build a nicer version.
Thank you so much!
Last edited by Losai; 03-18-2022 at 01:19 PM.
Please try
Please Login or Register to view this content.
Thank You mister Bo_Ry
This is very, very, very beautiful version. Just for improvement:
How to make it works also with numbers inside that string?
Hot make it works with some exception. Lets say point "." and slash "/"
To obtain this result:
This is simple string with Diacrtics and Symbols /05.34
Please! Answer! Please!
Add more needed string or symbol
Please Login or Register to view this content.
Right.
Its perfect! Its like a poetry.
Thank You mister Bo_Ry.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks