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:

Posted Date

Description

Amount

Currency

Transaction Reference Number

Fl Transaction Reference

Payee

Transaction Code

Server ID

Sic Code

Type

Credit/Debit

Origination Date

Original Amount

Currency

Fri Mar 01 00:00:00 EST 2013

CHECK

47.08

USD

1080

201000000000

83

Check

Debit

Fri Mar 01 00:00:00 EST 2013

Fri Mar 01 00:00:00 EST 2013

CHECK

519.24

USD

1082

201000000000

90

Check

Debit

Fri Mar 01 00:00:00 EST 2013

<tbody>
</tbody>

Here is what I need:

Date	Amount	Serial	Description	Comments
3/1/2013	47.08	1080
3/1/2013	519.24	1082

<tbody>
</tbody>

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.