Text to columns - changing data strings

[tthompson1987]

I'm trying to break a credit card statement from PDF to excel so the data can be useful. However the character strings are constantly changing based on vendor, location and the value of the transaction. Below is a sub-set sample of data. Date is easy to extract - LEFT(A1,5). But how do I consistently get the value of transaction when some are $2.95 (4 characters) and others are $5831.50 (7 characters). The vendor/city/state can be left crammed together, but it would be preferred to separate them as well. If I just use a simple "text to columns" function, I don't get the transaction value aligned in the same column. Thanks for your help!!

 

[tthompson1987]

Sample of data:

[TABLE="width: 500"]
<tbody>[TR]
[TD="width: 67"]12/07 & APL* ITUNES.COM/BILL 866-712-7753 CA .99
[/TD]
[/TR]
[TR]
[TD]12/09 & SPROUTS FARMERS MAR COSTA MESA CA 57.54[/TD]
[/TR]
[TR]
[TD]12/11 & USC STUDENT FIN SRVCS (3) 213-7404017 CA 5,831.50[/TD]
[/TR]
[TR]
[TD]12/10 & ROSS STORES #309 COSTA MESA CA 19.37[/TD]
[/TR]
[TR]
[TD]12/11 & HAND AND STONE MASSAGE AN 949-645-4823 CA 59.95[/TD]
[/TR]
[TR]
[TD]12/22 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 15.99[/TD]
[/TR]
[TR]
[TD]12/22 TANDOORI FRESH COSTA MESA CA 13.46[/TD]
[/TR]
[TR]
[TD]12/21 CHRONIC TACOS - NEWPOR NEWPORT BEACH CA 9.97[/TD]
[/TR]
[TR]
[TD]12/23 UBER *TRIP OVL5Y 800-592-8996 CA 9.83
[/TD]
[/TR]
[TR]
[TD]12/22 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 47.57[/TD]
[/TR]
[TR]
[TD]12/23 DEL TACO #13 BARSTOW CA 5.80[/TD]
[/TR]
[TR]
[TD]12/23 SKINNY FATS - SUMMERLIN LAS VEGAS NV 29.07[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This will extract the transaction amounts if it's Always at the end of the string:

Excel 2010
	A	B
1	12/07 & APL* ITUNES.COM/BILL 866-712-7753 CA .99	0.99
2	12/09 & SPROUTS FARMERS MAR COSTA MESA CA 57.54	57.54
3	12/11 & USC STUDENT FIN SRVCS (3) 213-7404017 CA 5,831.50	5831.5
4	12/10 & ROSS STORES #309 COSTA MESA CA 19.37	19.37
5	12/11 & HAND AND STONE MASSAGE AN 949-645-4823 CA 59.95	59.95
6	12/22 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 15.99	15.99
7	12/22 TANDOORI FRESH COSTA MESA CA 13.46	13.46
8	12/21 CHRONIC TACOS - NEWPOR NEWPORT BEACH CA 9.97	9.97
9	12/23 UBER *TRIP OVL5Y 800-592-8996 CA 9.83	9.83
10	12/22 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 47.57	47.57
11	12/23 DEL TACO #13 BARSTOW CA 5.80	5.8
12	12/23 SKINNY FATS - SUMMERLIN LAS VEGAS NV 29.07	29.07
Sheet14

Cell Formulas
Range	Formula
B1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),15)+0

Formula copied down.

 

[tthompson1987]

Thanks Jtakw! That worked perfectly!

 

[jtakw]

You're welcome, welcome to the forum.