Below are the possible solutions to the Challenge 73 – Sum the Numbers in a String Formula using traditional Excel functions =SUM(–(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*LEN(A2)+1,LEN(A2)))) Using FILTERXML (for office 2013 and greater and 365) =SUM(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s")) If you are not on Office 365, then you will need to enter above formula as array i.e. by pressing CTRL+SHIFT+Enter….
Excel Quiz 54
Multi Column VLOOKUP Formula – Power Query – Tips & Tricks 8
In the previous post, we looked into VLOOKUP formula for exact match but where single column was involved – VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7 In this, I am presenting formula which will work for any number of columns, hence it will work for 1 column, 2 columns and so on….
VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7
Generally, a merge is performed and then various succeeding operations are performed to arrive at Exact Match for VLOOKUP/XLOOKUP. But all these steps can be replaced with a single formula in Power Query which is faster than merge operation. Read on….. Let's recap VLOOKUP formula for Exact Match. =VLOOKUP(A2,Sheet2!A:C,3,0) =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) This will look for exact…
Challenge 73 – Sum the Numbers in a String
Let's say cell A2 has following string. 12,2, 2023,309,4, 557,62 Hence, there are following distinct numbers 12 2 2023 309 4 557 62 The challenge is to find the sum of these numbers. Hence answer should be 2969 which is 12+2+2023+309+4+557+62. The solution to this problem will be published on 28-Feb-22.
Tips & Tricks 184 – Using FILTERXML to reverse a string of Words
Another FILTERXML hack. Suppose you have following string of words in cell A2 Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin You want to reverse this string of words and want following output. Berlin, Detroit, Miami, Washington, Delhi, Paris, London, Moscow FILTERXML will come to your rescue. You can use following formula for this =TEXTJOIN(",…
Solutions to Power Query Challenges – 1 to 7
Attached Excel files contain solutions to all Power Query challenges posted till date (1 to 7). Please note that there are multiple solutions possible and the solution contained in these files is one of the approaches possible. Many people will come up with their own innovative solutions. PQ_Challenge_1_Solution PQ_Challenge_2_Solution PQ Challenge_3_Solution PQ_Challenge_4_Solution PQ_Challenge_5_Solution PQ_Challenge_6_Solution PQ_Challenge_7_Solution…
Tips & Tricks 183 – Using FILTERXML to extract nth word from front and back
Suppose you have following string Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin And you want to retrieve nth word from front. You can use following FILTERXML formula for this =FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]") Where N need to be replaced with the word number which you want. Hence, if you wanted 2nd word, then replace that with…
Clean the data – Power Query Challenge 7
You would need to clean the data to give the result in picture. Try to do maximum steps through UI and minimize formula / M-code. The problem file related to this challenge can be downloaded from PQ_Challenge_7_Problem The solution to this Clean the data – Power Query Challenge 7 will be published after a week…
Power Query Challenge 6 – Generate Multiple Columns
This time you would need to generate many columns from a single column sorted through Power Query. The problem has many alphabets in single column and you will need to generate columns of alphabets which are sorted.