MS Excel / VBA help thread

@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

I'll do it for you, send us your bank details :D

coming right up!

Also does your bank give you other download options?

not really... its quite old... im trying to track my habits.

Are there consistent separators? does it always use "/" for example?

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?

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

Are there consistent separators? does it always use "/" for example?

yes

Could use something like this https://exceljet.net/formula/split-text-string-at-specific-character

Google reckons that's a locale code...

https://stackoverflow.com/questions/8948­05/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

2 Attachments

Whack that column of the data up on yousendit or whatever, let the hive mind have at it then.

today I learned...

there's a CLEAN function...
removes non printable characters

Try that. =CLEAN(A1)

I found out the group thing. You have to physically ungroup (its an option on the ribbon).
FUCK OFF EXCEL. I want my own groupings for dates thanks.

CLEAN looks good.

Try that. =CLEAN(A1)

Borked
Working date changes to like 44230
Borked give me the 03/03/2015

1 Attachment

yousendit

trying to find a free easy to use version

You can attach Excel files to posts. Just use the "Upload a file"

one of those is text.
you'll need to:
if(istext(a1)+0 =0, a1, bigformulatochopoutthedatefromtextgoeshere)

sorted it but what a fucking pain in the arse eh?

bigformulatochopoutthedatefromtextgoeshe­re

wut

You can attach Excel files to posts. Just use the "Upload a file"

1 Attachment

• Data testing dates.xlsx

My excel wont open that. Corrupt, unrecoverable data.

yeah, half of them are text.

Multiply by 1 to make them numbers, then format as a date

=A1*1

1 Attachment

Unfortunately in my experience CLEAN doesn't work that well. It can't remove zero width spaces for instance https://en.wikipedia.org/wiki/Zero-width_space (which caused me a great deal of swearing as they also don't show up when you press F2 to edit, due to being zero width).

@ChainBreaker removing zero width spaces is worth trying.
Go to this website https://coolsymbol.com/#non-printing-chars-table
Click to copy Zero-Width Space (ZWSP) Character
In Excel press Ctrl+H to get Find and Replace.
Paste the ZWSP into the Find, leave Replace blank and click Replace All.
If it says it made some replacements that may well be your problem.

Text to columns worked for me to get them into date format.

Way less exciting than I hoped.

Try isnumber(a1)+0

filter out the 1's
leaves 81 "problem cases".
clicked on cell. to look for white space or something.
clicked off cell
value in b turns to 1.

Which means the formatting hasn't been applied properly somewhere

=A1*1

Which column you using as A1? I am still getting a borked value #value!