Forum Discussion
Convert Array formula
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.
You can't place SUBSITUTE() in COUNTIFS() because COUNTIFS() expects a reference and not an array.
You have to change from COUNTIFS() to SUMPRODUCT() or use the other options.