Another headscratcher! Problem with cell format not allowing sum in status bar

[Rhothgar]

Happy New Year to all,

Hope you all had a relaxing and peaceful time with family and friends.

In Office 365, I have a blank workbook into which I have imported a UTF-8 Tab Delimited Text File. The file contains a column of what appears to be currency ie £-9.00.

When I check the format, it says General.

The Customise Status Bar shows I have Sum checked but they won't add up even if I change the format to number or currency.

Furthermore, when I double click the cell, it converts it from £-9.00 to -9.00 ONCE I move off that cell and select another one.

Can someone explain to me if there is a way to change all using Power Query Editor or some other clever trick please? I can't click on every cell in that column to convert it. I'd get Repetitive Strain Injury!

Thanks in advance

 

[sandy666]

try to replace red with nothing and change Type to currency or whatever you want £-9.00

btw. try to use proper forum for PQ

 

[Rhothgar]

try to replace red with nothing and change Type to currency or whatever you want £-9.00

btw. try to use proper forum for PQ

No need for PQ! I just need to start to learn to think laterally like what you've just done! There are so many ways to skin a cat and now, at least, I know one of them. I would never have thought of that.

Thank you!

 

[sandy666]

You are welcome

if there is a way to change all using Power Query Editor

this is a text: £-9.00 (in PQ) so you need to chenge it to the number. that's all