seperating into separate columns

B

bubba1965

Is there an easy way to separate information contained in one column
into two separate columns.

For example, I have a column of about 1000 dates that are listed as
Sat, Aug. 21, Sun, Aug. 22, Mon, Aug. 23 etc. Is there a formula that
would separate Sat from Aug. 21 etc. So basically, everything before
the comma is put in one column and everything after the comma in
another column.

Also when I get Sat, Sun, Mon, etc. into its own column. Is there an
easy way to add a period to each day of the week. So Sat becomes Sat.
and Sun becomes Sun., etc.

Thanks for any help you can provide.
 
D

DDM

Bubba, normally the "Text to Columns" feature will do the trick, but since
you want the data in two columns rather than three, you'll have to use
formulas.

If your data starts in A1, put this formula in B1:
=LEFT(A1,FIND(",",A1)-1)&"."
Put this formula in C1: =RIGHT(A1,LEN(A1)-5)
Or use this one (in C1) if the days aren't all three letters (for ex., if
"Tue" is "Tues": =RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1))))

Copy the formulas down, then select all the cells you copied to and Edit >
Copy > Edit > Paste Special > Values > OK.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
B

bubba1965

Thank you - the text to columns worked quite nicely.

As far as the concatenation formula - I am a little confused. Should
create a helper column - to put the formula in.
I tried that and got the desired result - but then when I moved tha
column to where the original column was and deleted the original colum
- I lose the cell reference in the formula.

What is the best way to accomplish what I am trying to do.? (adding
period to the end of each word in the column
 
D

DDM

Bubba, you're on the right track, but once you have your result in the
helper column, you have to select all the cells and Edit > Copy, then
immediately Edit > Paste Special > Values to turn the formulas into values.
Then you can move them over.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Top