-
@ChainBreaker if it is white space then =trim(data) may remove some of it, if not you can use the substitute function as well.
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
ChainBreaker-
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
@ChainBreaker if it is white space then =trim(data) may remove some of it, if not you can use the substitute function as well.