Power Query challenge 4 is an interesting take on extraction and data manipulation. Extract the digits from the given string and sum the digits for that extract string in each row.
Of course, the query should be dynamic i.e. even if number of entries increase or decrease in a line OR/AND number of rows increase or decrease, the Query should give right result on Refresh.
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_4_Problem
The solution to Power Query Challenge 4 will be published after a week i.e. on Thursday, 14-Oct-21.
Solution link – Solutions to Power Query Challenges – 1 to 7
Power Query M function reference – PowerQuery M | Microsoft Docs
I know this is a Power Query challenge, but there is a relatively simple array formula that will do what you asked for. Remember, this is an array formula, so if you are using XL2019 or earlier, you have to commit this formula using CTRL+SHIFT+ENTER….
=SUM(IFERROR(0+MID(A1,ROW(1:99),1),0))
Note: In order to avoid using the INDIRECT function, I simply assumed the text in cell A1 will not be longer than 99 characters. If it could be longer, just change the 99 to a number equal to or larger than the longest number of characters that could ever be in the cell.
Great solution!!!
=SUM(IFERROR(–MID(F2,SEQUENCE(LEN(F2)),1),0)) also works.
Not my idea. I forget where I saw this.
Hola, envio mi soluccion.
=CONCAT(SI.ERROR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));""))
=SUMA(SI.ERROR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));""))