how would i separate these entries from my credit card statement into coherent columns?

[bigdan]

For some reason my credit card doesnt give excel statements. How would I separate gibberish like this into columns? I'd like 4 columns please.
I used LEFT, MID, RIGHT, FIND commands for the first 3 columns, but then I got stuck.

Jan 07 Jan 07 GOOGLE *OkCupid g.co/helppay#NS 2.52
Apr 01 Apr 02 BANK PMT/PAIEMENT BCIC -200.00
Mar 23 Mar 25 TIM HORTONS QUEBEC ON 3.03
Apr 10 Apr 10 INTEREST CHARGES 15.18

 

[Eric W]

Perhaps:

Book1
	A	B	C	D	E
1	Jan 07 Jan 07 GOOGLE *OkCupid g.co/helppay#NS 2.52	Jan 07	Jan 07	GOOGLE *OkCupid g.co/helppay#NS	2.52
2	Apr 01 Apr 02 BANK PMT/PAIEMENT BCIC -200.00	Apr 01	Apr 02	BANK PMT/PAIEMENT BCIC	-200
3	Mar 23 Mar 25 TIM HORTONS QUEBEC ON 3.03	Mar 23	Mar 25	TIM HORTONS QUEBEC ON	3.03
4	Apr 10 Apr 10 INTEREST CHARGES 15.18	Apr 10	Apr 10	INTEREST CHARGES	15.18
Sheet6

Cell Formulas
Range	Formula
B1	=LEFT(A1,6)
C1	=MID(A1,8,6)
D1	=MID(A1,15,LEN(A1)-15-LEN(TEXT(E1,"#.00")))
E1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))+0

 

[bigdan]

thank you so much!

can you explain this part of the code in D1? this is where I got stuck when I was designing the formulas myself

LEN(TEXT(E1,"#.00"))

 

[Eric W]

In order to make the E column numeric, I added 0 to the string. This converts a string number to a real number, allowing you to sum up that column if you wish. But if you do that, it could convert 200.00 to just 200 (no cents). The D1 formula starts at position 15, and then figures out how many characters to extract. It subtracts the first 15 characters, then has to subtract the number of characters from column E. But if the E formula converts 200.00 to 200, then I can't just do a LEN(E1), because it will be off by 3 characters. So I use the TEXT function to make sure the length I calculate is the original length, not the shorter length.

If you take the +0 off the E1 formula, you can remove the TEXT from the D1 formula, but then you can't add up column E.

Make sense?