R
Ray S.
I posted a question and got a reply that did not work. I marked the answer
not helpful to me, and replied to the person who answered. Since I have not
received an answer, I am asking again. Here is the gist of the prior post:
My Question:
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?
Joel responded:
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.
My reply to Joel:
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.
I'm still in need of advice. My file is quite large and I'd hate to have to
go through cutting and pasting phone numbers into a new column and deleting
rows throughout the entire file.
not helpful to me, and replied to the person who answered. Since I have not
received an answer, I am asking again. Here is the gist of the prior post:
My Question:
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?
Joel responded:
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.
My reply to Joel:
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.
I'm still in need of advice. My file is quite large and I'd hate to have to
go through cutting and pasting phone numbers into a new column and deleting
rows throughout the entire file.