Excel Column Break Up

D

da

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
M

Mike Rogers

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
 
R

Ron Rosenfeld

Thanks, but how and where do I write these formulas?
Click to expand...

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
 
S

Shane Devenshire

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.
 
D

da

Thank you

Ron Rosenfeld said:
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
Click to expand...
 
D

da

Thank you, it worked just as you explained it.

Shane Devenshire said:
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
Click to expand...
 
Top