Recognising a date and number

[MrOllyR]

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.

 

[Joe4]

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.

Have you tried using "Text to Columns"?
You can only do it one column at a time (though you can have VBA loop through each column), but it is a good tool for converting numbers and dates that have been entered as text to the numbers and dates. Note that in the third argument, if you leave it set to "General", it might guess correctly, and differentiate dates from numbers when converting (though Excel actually stores dates as numbers with special formats, so dates REALLY are numbers in Excel).

 

[MrOllyR]

Have you tried using "Text to Columns"?
You can only do it one column at a time (though you can have VBA loop through each column), but it is a good tool for converting numbers and dates that have been entered as text to the numbers and dates. Note that in the third argument, if you leave it set to "General", it might guess correctly, and differentiate dates from numbers when converting (though Excel actually stores dates as numbers with special formats, so dates REALLY are numbers in Excel).

Thank you for your reply. I've tried this and it's more trouble than it's worth in terms of applicability. Instead, I've started again from scratch and created a series of macros to clean and trim data without altering the number format. Thank you anyway!