|
Excel Column Break Up
Hi
Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 |
Excel Column Break Up
On Fri, 20 Feb 2009 16:35:01 -0800, da wrote:
Hi Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 If the only "$" is the one preceding the amount, then Date: =LEFT(A1,8) Merchant: =MID(A1,10,FIND("$",A1)-11) Amount =MID(A1,FIND("$",A1),99) --ron |
Excel Column Break Up
Thanks, but how and where do I write these formulas?
"Ron Rosenfeld" wrote: On Fri, 20 Feb 2009 16:35:01 -0800, da wrote: Hi Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 If the only "$" is the one preceding the amount, then Date: =LEFT(A1,8) Merchant: =MID(A1,10,FIND("$",A1)-11) Amount =MID(A1,FIND("$",A1),99) --ron |
Excel Column Break Up
da
"where do I write these formulas?" In the columns you want the information spread to. If your in information is now in column A starting with row 2, you would put each formula in row 2 of column B, C, & D. Mike Rogers "da" wrote: Thanks, but how and where do I write these formulas? "Ron Rosenfeld" wrote: On Fri, 20 Feb 2009 16:35:01 -0800, da wrote: Hi Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 If the only "$" is the one preceding the amount, then Date: =LEFT(A1,8) Merchant: =MID(A1,10,FIND("$",A1)-11) Amount =MID(A1,FIND("$",A1),99) --ron |
Excel Column Break Up
On Fri, 20 Feb 2009 16:59:01 -0800, da wrote:
Thanks, but how and where do I write these formulas? You put them in the cell where you want to see the relevant data. If your original is in A1, and you want to see the date in B1, then you would put the formula I provided for the date in B1. And so forth. Please note that if your original data is NOT in A1, you will need to change the reference to A1 in the formulas I provided to reflect the correct cell. --ron |
Excel Column Break Up
Hi,
Here is an entirely different approach: 1. Select all the data in the column and choose Data, Text to Columns, 2. Select Fixed width and click Finish. The first column should be your dates. 3. Select the second column and choose Data, Text to Columns, 4. Select Delimited and click Next. 5. Select Other and enter $ as the delimiter and click Finish. -- If this helps, please click the Yes button Cheers, Shane Devenshire "da" wrote: Hi Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 |
Excel Column Break Up
Thank you
"Ron Rosenfeld" wrote: On Fri, 20 Feb 2009 16:59:01 -0800, da wrote: Thanks, but how and where do I write these formulas? You put them in the cell where you want to see the relevant data. If your original is in A1, and you want to see the date in B1, then you would put the formula I provided for the date in B1. And so forth. Please note that if your original data is NOT in A1, you will need to change the reference to A1 in the formulas I provided to reflect the correct cell. --ron |
Excel Column Break Up
Thank you, it worked just as you explained it.
"Shane Devenshire" wrote: Hi, Here is an entirely different approach: 1. Select all the data in the column and choose Data, Text to Columns, 2. Select Fixed width and click Finish. The first column should be your dates. 3. Select the second column and choose Data, Text to Columns, 4. Select Delimited and click Next. 5. Select Other and enter $ as the delimiter and click Finish. -- If this helps, please click the Yes button Cheers, Shane Devenshire "da" wrote: Hi Is there any way I can break up the following in three different columns? Date, Merchant and Amount? At presnt, all info is in one column. Thanks 01/01/08 ALBERTSONS #6555 UPLAND, CA $3.23 01/01/08 VONS FUEL 10026813 UPLAND, CA $35.50 01/01/08 WAL-MART #1992 UPLAND, CA $17.80 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $12.00 01/02/08 GRAZIANOS ITALIAN RESTAUR UPLAND, CA $7.50 01/02/08 SHELL OIL 57442712808 UPLAND, CA $31.50 01/03/08 MIMIS CAFE 00000125 UPLAND, CA $14.73 01/27/08 UPLAND FARMERS MARKET UPLAND, CA $19.09 01/27/08 VONS Store00026815 UPLAND, CA $24.70 01/28/08 ALBERTSONS #6555 UPLAND, CA $20.50 01/28/08 VONS FUEL 10026813 UPLAND, CA $39.50 01/29/08 NEW BOMBAY STORES UPLAND, CA $27.35 |
| All times are GMT +1. The time now is 08:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com