I'd appreciate any comments on the following. My Regional Setting is UK but from what I can see the situation is the same with a European setting (decimal point being a , and thousands separator a .)
The attached workbook shows two records (a small subset of many more rows) with column labels in rows A1:A3
I've taken a copy in A8:A10
Note how when A8:A10 is selected and the TextToColumns functionality is used specifying the comma as a delimiter, with the output set to C8 there is an inconsistency between F9 & F10
Both show the same characters, i.e. 21.38 and 148.065 but F9 is Text and F10 is a number and F10 displays as 148,065 and the underlying number is 148065
The csv apparently comes from a USA source if that's relevant.
The problem I'm trying to address is that the F10 value should be a decimal 148.065 not a whole number 148,065.
I have a macro to remedy this but I'd like to understand why this happens in the first place.
Something seems to identify and distinguish those records in the csv that have two numeric characters after the dot, from those that have three numeric characters after the dot so that all the two numeric records result as text and all the three numeric characters as numbers.
Any ideas as to why this is?
Ideally I'd like the TextToColumns to produce a consistent result, preferably both numbers, but at least both the same.
Bookmarks