Forum Discussion
Convert Array formula
Hi there, I'm trying to insert a formula (COUNTIFS) from a table. I've done this countless times with no issue. The array looks different when I try to get the criteria from the table. It seems ...
Detlef_LewinThanks. But I can't do that.
Is there a way to remove them with a formula or workaround? The table is input from office forms and I need to create a formula or something to read the data so that it can generate monthly reports
You should investigate the source. It is strange that Forms sometimes would add a line feed.
You could use Find & Replace, SUBSTITUTE(), Power Query ...
let Source = Excel.CurrentWorkbook(){[Name="AppInput"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start time", "Completion time", "Name"}, "Question", "Answer"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","#(lf)","",Replacer.ReplaceText,{"Answer"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Start time", type datetime}, {"Completion time", type datetime}, {"ID", Int64.Type}, {"Name", type text}}) in #"Changed Type"*
Detlef_Lewin thanks. tried to place the CLEAN formula, the SUBSTITUTE formula in the COUNTIFS function but it keeps on giving me an error. I've even tried to CRL + ENTER the formula, with no luck.
