Newbie question - splitting data from one column to two

[jesse_mclean]

Hi all,

New member here. I plan on making use of the useful links sticky. In the meantime, I'm stuck on a function for a work project. I'm attempting to separate the date from the monetary sum in the sample column pasted below so they're in two separate columns. I'm relatively new to =left functions and everything I've tried hasn't worked. Any help would be greatly appreciated.

Period Ending Amount
October 14, 2010 $24,008.99
November 14, 2010 $45,402.32
December 17, 2010 $42,462.62
January 14, 2011 $19,899.15
February 10, 2011 $43,165.19
March 14, 2011 $84,775.57
April 14, 2011 $64,935.58
May 13, 2011 $28,365.43
June 13, 2011 $64,385.37
June 28, 2011* $3,295.00
July 14, 2011 $58,990.88
August 15, 2011 $27,378.81
September 22, 2011 $100,448.00
October 28, 2011 $50,607.60
November 14, 2011 $64,102.13
December 15, 2011 $61,870.28
January 20, 2012 $20,941.66
February 23, 2012 $67,766.05
March 13, 2012 $40,695.43
April 13, 2012 $67,117.15
May 7, 2012 $114,404.97
June 7, 2012 $79,256.80


Cheers!

[Andrew-R]

You can do it with no formula at all, if you want.

Select that block of data, then use the "Text to columns" function (in the Data menu in 2003, I think), tell it that your data is delimited, select the "Other" delimiter and put a dollar sign as the delimiter and click OK.

Job done, now you just have to format the new column of data as currency and you're sorted.

[jesse_mclean]

That would have never dawned on me. Worked brilliantly. Thanks!

[tahaahsan]

It's pretty simple. You can use the "Text to column method" like Andew-R suggested where you can have the '$' as the data separator. You can add the $ sign back after you have the data split.

Or you can always use the Left and Right function

For example:

If you have pasted your data in lets say cell A1.


Here is the formula for date: (Paste it in B1)
=LEFT(A1,FIND("$",A1,1)-1)

And Here is the formula for revenue amount: (Paste it in C1)
=RIGHT(A1,LEN(A1)-SEARCH("$",A1,1)+1)

Hope this helps.