Automatically Strip Unneeded Data and Reformat Date

[oldmuttonhead]

Hello, everyone! I hope you are having a great day today!

I am trying to manipulate data downloaded from a bank file into a useable format. What adds a level of difficulty is that I am trying to do this for someone who has almost zero Excel knowledge so I'm trying to automate this as much as possible. Also, I'm not sure I could make a more difficult date format to use if I was trying. Here is the data I have:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Posted Date
[/TD]
[TD]Description
[/TD]
[TD]Amount
[/TD]
[TD]Currency
[/TD]
[TD]Transaction Reference Number
[/TD]
[TD]Fl Transaction Reference
[/TD]
[TD]Payee
[/TD]
[TD]Transaction Code
[/TD]
[TD]Server ID
[/TD]
[TD]Sic Code
[/TD]
[TD]Type
[/TD]
[TD]Credit/Debit
[/TD]
[TD]Origination Date
[/TD]
[TD]Original Amount
[/TD]
[TD]Currency
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fri Mar 01 00:00:00 EST 2013
[/TD]
[TD]CHECK
[/TD]
[TD]47.08
[/TD]
[TD]USD
[/TD]
[TD]1080
[/TD]
[TD]201000000000
[/TD]
[TD][/TD]
[TD]83
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Check
[/TD]
[TD]Debit
[/TD]
[TD]Fri Mar 01 00:00:00 EST 2013
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fri Mar 01 00:00:00 EST 2013
[/TD]
[TD]CHECK
[/TD]
[TD]519.24
[/TD]
[TD]USD
[/TD]
[TD]1082
[/TD]
[TD]201000000000
[/TD]
[TD][/TD]
[TD]90
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Check
[/TD]
[TD]Debit
[/TD]
[TD]Fri Mar 01 00:00:00 EST 2013
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is what I need:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Amount
[/TD]
[TD]Serial
[/TD]
[TD]Description
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]3/1/2013
[/TD]
[TD]47.08
[/TD]
[TD]1080
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2013
[/TD]
[TD]519.24
[/TD]
[TD]1082
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

One more slight complication: Both input and output files will be CSV files.

I've tried various ideas like macros, VBA, etc. but I just can't seem to get the output I need without some weird hangup. Is it possible to do this as "automagically" as possible? I've been at this for 2 weeks now and I'm ready to pull my hair out. Any help even if it is just pointing me in the right direction would be GREATLY appreciated!

Thanks!

Rick

 

[RonB1111]

If a Posted Date is in A2, this will convert it to an excel date:
=--MID(A2,9,2)&"-"&MID(A2,5,3)&"-"&RIGHT(A2,4)

 

[oldmuttonhead]

Thanks, RonB1111!

I've tried to do something similar and use a Macro to "fix" it automatically with no luck. I've seen various ways to do that in a spreadsheet with a variable number of rows, but I can't seem to get any of them to work. Either they stop after 1 row or they don't calculate the formula. I thought I was an Excel guru until I ran into this problem. Is there a good way to do this where a novice can easily do it?

 

[RonB1111]

If your imported Posted Dates are in A2:a1000, you can enter the formula in any available column in row 2, and then copy/paste that cell down to row 1000 and it will convert every Posted date in A2:A1000.

If this doesn't address the problem you're facing, please clarify.