Data manipulation to remove undesired character strings

FlexicareTommy

New Member
Joined
Apr 4, 2013
Messages
11
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

<tbody>
</tbody>
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?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Date</td><td style=";">State</td><td style=";">Amount</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">12/04 THE PHOTO LAB COSTA MESA CA 1021.62</td><td style=";">12/04</td><td style=";">CA</td><td style="text-align: right;;">1021.62</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">12/05 CKE*CALIENTE SOUTHWE 271 COSTA MESA CA 9.70</td><td style=";">12/05</td><td style=";">CA</td><td style="text-align: right;;">9.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">12/06 SP * TASTY SHOP HTTPSTASTYSHO NY 28.00</td><td style=";">12/06</td><td style=";">NY</td><td style="text-align: right;;">28.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-decoration: underline;color: #0000FF;;">12/07 WAL-MART #5687 IRVINE CA 18.92</td><td style=";">12/07</td><td style=";">CA</td><td style="text-align: right;;">18.92</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">12/07 REI.COM 800-426-4840 WA 287.70</td><td style=";">12/07</td><td style=";">WA</td><td style="text-align: right;;">287.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">12/07 APL* ITUNES.COM/BILL 866-712-7753 CA .99</td><td style=";">12/07</td><td style=";">CA</td><td style="text-align: right;;">0.99</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A2,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A2," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A2</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A2," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A2," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=LEFT(<font color="Blue">A3,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A3,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A3," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A3</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A3," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A3," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=LEFT(<font color="Blue">A4,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A4,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A4," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A4</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A4," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A4," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=LEFT(<font color="Blue">A5,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A5,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A5," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A5</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A5," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A5," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=LEFT(<font color="Blue">A6,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A6,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A6," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A6</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A6," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A6," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">=LEFT(<font color="Blue">A7,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C7</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A7,1,FIND(<font color="Green">"~",SUBSTITUTE(<font color="Purple">A7," ","~",(<font color="Teal">LEN(<font color="#FF00FF">A7</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A7," ",""</font>)</font>)-1</font>)</font>)</font>),""</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A7," ",REPT(<font color="Green">" ",255</font>)</font>),255</font>)+0</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Nice job @Scott Huish

And perhaps to round it off:

Cell E2 (HelperColumn)
Code:
=TRIM(RIGHT(A2,LEN(A2)-LEN(B2)))
Cell F2 Balance of text
Code:
=TRIM(LEFT(E2,((FIND(" "&C2&" ",E2))-1)))
Cheers

pvr928
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Data</td><td style="text-align: right;;"></td><td style=";">Date</td><td style=";">Vendor</td><td style=";">State</td><td style=";">Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">12/04 THE PHOTO LAB COSTA MESA CA 1021.62</td><td style="text-align: right;;"></td><td style=";">12/04</td><td style=";">THE PHOTO LAB COSTA MESA</td><td style=";">CA</td><td style="text-align: right;;">1021.62</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">12/05 CKE*CALIENTE SOUTHWE 271 COSTA MESA CA 9.70</td><td style="text-align: right;;"></td><td style=";">12/05</td><td style=";">CKE*CALIENTE SOUTHWE 271 COSTA MESA</td><td style=";">CA</td><td style="text-align: right;;">9.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">12/06 SP * TASTY SHOP HTTPSTASTYSHO NY 28.00</td><td style="text-align: right;;"></td><td style=";">12/06</td><td style=";">SP * TASTY SHOP HTTPSTASTYSHO</td><td style=";">NY</td><td style="text-align: right;;">28</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">12/07 WAL-MART #5687 IRVINE CA 18.92</td><td style="text-align: right;;"></td><td style=";">12/07</td><td style=";">WAL-MART #5687 IRVINE</td><td style=";">CA</td><td style="text-align: right;;">18.92</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">12/07 REI.COM 800-426-4840 WA 287.70</td><td style="text-align: right;;"></td><td style=";">12/07</td><td style=";">REI.COM 800-426-4840</td><td style=";">WA</td><td style="text-align: right;;">287.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">12/07 APL* ITUNES.COM/BILL 866-712-7753 CA .99</td><td style="text-align: right;;"></td><td style=";">12/07</td><td style=";">APL* ITUNES.COM/BILL 866-712-7753</td><td style=";">CA</td><td style="text-align: right;;">0.99</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet14</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=MID(<font color="Blue">A2,7,FIND(<font color="Red">" "&E2&" ",A2</font>)-7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=TRIM(<font color="Blue">LEFT(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">A2," ",REPT(<font color="Teal">" ",100</font>)</font>),200</font>),150</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">A2," ",REPT(<font color="Green">" ",100</font>)</font>),50</font>)+0</td></tr></tbody></table></td></tr></table><br />

Formulae copied down.
 
Last edited:

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,573
Messages
5,469,472
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top