Date & Phone Manipulation


R

Ray S.

I have an Excel file with one column that has entries of both a date and
telephone number for each row of data. The date is in m/d/yyyy format and the
phone is in ###-###-#### format. The problem I have is that when the file was
created the date and all other relevant column information is in one row and
the corresponding telephone is in a second row with no other relevant column
information. So each record consists of two rows. I want to make a column
that will have the telephone number and get rid of the second row that has
only the telephone. Here's an example:

Date and Phone ... B...C...D...F... columns
6/2/2008 x y z m...
555-1212
6/2/2008 f g h j...
555-3333

I'd like to get

Date... Phone ... B...C...D...F... columns
6/2/2008 555-1212 x y z m...
6/2/2008 555-3333 f g h j...

Any ideas?
 
Ad

Advertisements

J

Joel

The easiest way is to use text to columns. I believe you have a return in
the cell to put the phone number on a 2nd line in the cell. If not, then the
width of the cell was adjusted t force the phone number on a 2nd line. Then
ingore the comments below about the square character.

1) Insert an empty column B.
2) Highlight Column A
3) Use menu Data - Text to Columns
a) Select fixed Width
b) Next
c) Use mouse to add a column by clicking to the left of the square
character between the date and phone number. a Verticle line will appear
which will sperate the data into two columns. The square character is the
return which put the phone number on a second line.

If there is no square character then simply click between the phone number
and date.
d) Press Finish
4) Now remove the return which is an invisiable character in front of the
phone number and any unecessary spaces. You can use Edit - Replace to remove
spaces in the cells. You can also remove the square character using replace.
 
Ad

Advertisements

R

Ray S.

OK, that was a nice try, but there is no return, and adjusting the width does
not do what you say in your second option. There are simply two rows, no
square character, and you cannot do anything about the separate rows with any
delimiter. They are just two separate rows. If you press Finish, it stays the
same, nothing changes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top