I've noticed that we can't predict the quality of the data we need if it has been received from multiple sources. Trailing spaces, non-printing characters, upper case only text, etc. I'm putting together a spreadsheet tool which cleans data so it can be collated and usable.
On sheet 1, data can be entered, and a nested formula 'cleans' the data on sheet 2, before VBA gets rid of the formula to paste the values.
The problem I have is because sheet 2 is set to general, it doesn't recognise dates and numbers. It sees everything as text but prompts me that there is a problem when it recognises that data isn't text.
Is there a way to ensure that Excel recognises a date as a date and a number as a number? That is, without having to go through the manual process of changing the format.
Any help would be much appreciated.
On sheet 1, data can be entered, and a nested formula 'cleans' the data on sheet 2, before VBA gets rid of the formula to paste the values.
The problem I have is because sheet 2 is set to general, it doesn't recognise dates and numbers. It sees everything as text but prompts me that there is a problem when it recognises that data isn't text.
Is there a way to ensure that Excel recognises a date as a date and a number as a number? That is, without having to go through the manual process of changing the format.
Any help would be much appreciated.