MS Excel / VBA help thread

You are reading a single comment by @chez_jay and its replies. Click here to read the full conversation.

•

ChainBreaker in reply to @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

•

chez_jay in reply to @ChainBreaker

If bad data is in A1, what does

=mid(a1,1,1) return?

Do you mean =mid(a1;1;1) rather?

This is a regional settings thing. Set in the OS, at least on Windows.

UK, US default to comma separated lists (or formulae in this case). Most European countries default to semicolon.

About

Avatar for chez_jay @chez_jay started