Dear all,
I am trying to clean some weather data scraped from the web - I have managed simple things like TRIM and CLEAN and can extract integers but some of the data have negative values (really important in temperature) and remove symbols (* and #) which relate to key that is irrelevant for my purposes.
I have tried wrapping formulae in VALUE to no avail.
I am using 365 and have attached the sheet below with negative values and one of the problems highlighted.
I want to use all the cleaned values as decimal numbers (apart from YEAR and MONTH columns).
many thanks in advance
Nick
Hi Nick,
You can use Power Query to clean this data. In the attached file (sheet 'Table1') I've loaded your data to Power Query and replaced the asterisk and hash with blanks and then converted the values to decimal numbers.
Note: you could probably use Power Query to get the data direct from the website and fix the formatting in one go.
If you'd like to learn more about Power Query and how you can use it to automate these data gathering and cleaning tasks, please check out my Power Query course.
Mynda
Mynda thankyou so much for this - apologies for the slow response - I have been off line for a few days.
I am in the middle of doing your excel expert course so will finish that then look at the Power query one.
Thanks so much again
Best
Nick
