-
Google reckons that's a locale code...
https://stackoverflow.com/questions/894805/excel-number-format-what-is-409If bad data is in A1, what does
=mid(a1,1,1) return?Does it look like it returns the first character?
If it looks like it returns nothing then that's probably the hidden character
So try
=mid(a1,2,1) etc.then if you've got good data from position 2 onwards then you could always do
=mid(a1,2,len(a1)-2)which would have the effect of stripping the first and last characters, and leaving the good bit in the middle.
basically - find where the shit data is and exclude it
duncs-
Google reckons that's a locale code...
https://stackoverflow.com/questions/894805/excel-number-format-what-is-409
If bad data is in A1, what does
=mid(a1,1,1) return?Do you mean =mid(a1;1;1) rather?
Does it look like it returns the first character?
The editable lot comes back as a 4
the borked ones come back as the first number 0, 1, 2 or 3
If it looks like it returns nothing then that's probably the hidden character
So try
=mid(a1,2,1) etc.then if you've got good data from position 2 onwards then you could always do
=mid(a1,2,len(a1)-2)which would have the effect of stripping the first and last characters, and leaving the good bit in the middle.
basically - find where the shit data is and exclude it
Maybe the bad data is after?!
Also, not sure why the working one comes back with a 4
ChainBreaker
didnt work
further info - it seems to like [$-409]dd-mmmm-yy;@ when i select custom - doubt it means anything but hey... thought id add more info