Data manipulation to remove undesired character strings

[FlexicareTommy]

Hello Mr. Excel experts! I'm in need of help to manipulate a data report that populates the entire information into one cell ("Text to columns" does not work unless there's some amazing trick I don't know how to use). This is a credit card financial statement data. Below is a small sub-section sample.

12/04 THE PHOTO LAB COSTA MESA CA 1021.62
12/05 CKE*CALIENTE SOUTHWE 271 COSTA MESA CA 9.70
12/06 SP * TASTY SHOP HTTPSTASTYSHO NY 28.00
12/07 WAL-MART #5687 IRVINE CA 18.92
12/07 REI.COM 800-426-4840 WA 287.70
12/07 APL* ITUNES.COM/BILL 866-712-7753 CA .99[TABLE="width: 64"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I can easily break the transaction date by using a LEFT() formula because the string is always 5 characters long. However the vendor, city, state, and value are always dynamically changing in character length for thousands of rows of data.

The state is always 2 characters in length followed by a space and then the value of the transaction - anywhere from 3-8 characters depending on the actual dollar value. I cant come up with a consistent way to get all information into their respective columns because the vendor data is always so drastically different even including numbers and special characters (two ex: REI.com with phone number and ITUNES which doesn't have a proper city name)

The format I'm trying to break this into is minimum four columns - Date, Vendor, State, Value.
City would be nice, but since it's not always available, this might be impossible. Can anyone solve this puzzle?

 

[Scott Huish]

Date, State, and amount are easily accomplished. The problem with the city, without any real delimiter, is because cities can be more than 2 words, there is no real way to determine where whatever comes before the city ends and the city name starts.
Formula in B2: =LEFT(A2,5)
Formula in C2: =LEFT(REPLACE(A2,1,FIND("~",SUBSTITUTE(A2," ","~",(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))),""),2)
Formula in D2: =RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255)+0
Format column D as number with 2 decimal places.
Copy down.

Excel 2010
	A	B	C	D
1		Date	State	Amount
2	12/04 THE PHOTO LAB COSTA MESA CA 1021.62	12/04	CA	1021.62
3	12/05 CKE*CALIENTE SOUTHWE 271 COSTA MESA CA 9.70	12/05	CA	9.70
4	12/06 SP * TASTY SHOP HTTPSTASTYSHO NY 28.00	12/06	NY	28.00
5	12/07 WAL-MART #5687 IRVINE CA 18.92	12/07	CA	18.92
6	12/07 REI.COM 800-426-4840 WA 287.70	12/07	WA	287.70
7	12/07 APL* ITUNES.COM/BILL 866-712-7753 CA .99	12/07	CA	0.99
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,5)
B3	=LEFT(A3,5)
B4	=LEFT(A4,5)
B5	=LEFT(A5,5)
B6	=LEFT(A6,5)
B7	=LEFT(A7,5)
C2	=LEFT(REPLACE(A2,1,FIND("~",SUBSTITUTE(A2," ","~",(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))),""),2)
C3	=LEFT(REPLACE(A3,1,FIND("~",SUBSTITUTE(A3," ","~",(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-1))),""),2)
C4	=LEFT(REPLACE(A4,1,FIND("~",SUBSTITUTE(A4," ","~",(LEN(A4)-LEN(SUBSTITUTE(A4," ",""))-1))),""),2)
C5	=LEFT(REPLACE(A5,1,FIND("~",SUBSTITUTE(A5," ","~",(LEN(A5)-LEN(SUBSTITUTE(A5," ",""))-1))),""),2)
C6	=LEFT(REPLACE(A6,1,FIND("~",SUBSTITUTE(A6," ","~",(LEN(A6)-LEN(SUBSTITUTE(A6," ",""))-1))),""),2)
C7	=LEFT(REPLACE(A7,1,FIND("~",SUBSTITUTE(A7," ","~",(LEN(A7)-LEN(SUBSTITUTE(A7," ",""))-1))),""),2)
D2	=RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255)+0
D3	=RIGHT(SUBSTITUTE(A3," ",REPT(" ",255)),255)+0
D4	=RIGHT(SUBSTITUTE(A4," ",REPT(" ",255)),255)+0
D5	=RIGHT(SUBSTITUTE(A5," ",REPT(" ",255)),255)+0
D6	=RIGHT(SUBSTITUTE(A6," ",REPT(" ",255)),255)+0
D7	=RIGHT(SUBSTITUTE(A7," ",REPT(" ",255)),255)+0

 

[jtakw]

.

 

[pvr928]

Nice job @Scott Huish

And perhaps to round it off:

Cell E2 (HelperColumn)

=TRIM(RIGHT(A2,LEN(A2)-LEN(B2)))

Cell F2 Balance of text

=TRIM(LEFT(E2,((FIND(" "&C2&" ",E2))-1)))

Cheers

pvr928

 

[jtakw]

Hi,

Got interrupted while posting above.
Since you say some rows will have City and some won't, the Vendor column may or may not contain the City:

Book1
	A	B	C	D	E	F
1	Data		Date	Vendor	State	Value
2	12/04 THE PHOTO LAB COSTA MESA CA 1021.62		12/04	THE PHOTO LAB COSTA MESA	CA	1021.62
3	12/05 CKE*CALIENTE SOUTHWE 271 COSTA MESA CA 9.70		12/05	CKE*CALIENTE SOUTHWE 271 COSTA MESA	CA	9.7
4	12/06 SP * TASTY SHOP HTTPSTASTYSHO NY 28.00		12/06	SP * TASTY SHOP HTTPSTASTYSHO	NY	28
5	12/07 WAL-MART #5687 IRVINE CA 18.92		12/07	WAL-MART #5687 IRVINE	CA	18.92
6	12/07 REI.COM 800-426-4840 WA 287.70		12/07	REI.COM 800-426-4840	WA	287.7
7	12/07 APL* ITUNES.COM/BILL 866-712-7753 CA .99		12/07	APL* ITUNES.COM/BILL 866-712-7753	CA	0.99
Sheet14

Cell Formulas
Range	Formula
C2	=LEFT(A2,5)
D2	=MID(A2,7,FIND(" "&E2&" ",A2)-7)
E2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),150))
F2	=RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),50)+0

Formulae copied down.

 

[pvr928]

Nice one @jtakw - I love the approach you take in E2 and F2 - just figured out how it works. So simple and so much easier than what I tend to use.

Cheers

pvr928