Merging data in two columns

  • Thread starter Thread starter JEC
  • Start date Start date
J

JEC

I have been given a spreadsheet that contains contact information.
Unfortunately it was vreated in such a way that the area code is in one
column and the phone number is in the other. I would like to combine the two
into a single column. I tried doing this via a formula but and it worked but
the problem that I run into is that I would then like to copy and paste this
information into another document. When I copy and paste I get the formula
instead of the actual data. Anyone know a better way to go about this? I
would greatly appreciate any suggestions.
 
Sub combinephonenum()
mc = "d"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))

'for testing
'c.Offset(, 2) = c & " " & c.Offset(, 1)
c.Value = c & " " & c.Offset(, 1)
c.offset(,1).clearcontents
Next
End Sub
 
Select all of your phone numbers, copy (Ctrl+C), then where you want the
phone numbers select the topmost cell and do a paste special/values.

Tyro
 
Thank you for your suggestion. Now what do I do with it?

Forgive me, I do not know very much about Excel. If it helps make things
easier, the columns in my spreadsheet are L and M.
 
Thank you very much for your prompt response. It worked, but only for the
first two rows. Any suggestions?
 
Tested fine. Send me your workbook, if desired. Send to the address below,
NOT the ng.
 
I do not think it will make it. It is 47mb.

Don Guillett said:
Tested fine. Send me your workbook, if desired. Send to the address below,
NOT the ng.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
This is odd.

I copied the first 20 rows of info into a new spreadsheet. When I run the
macro on that, it works correctly on all but the first row.

If I run it on the first spreadsheet it only does the first two rows and
then stops.
 
It didn't work on the 1st row because it assumed you had a header row. See
the 2
For Each c In Range(Cells(2, mc), Cells(lr, mc))
 
That was the clue I needed. I got it to work on the whole spreadsheet now.
Thank you very much for your assistance.
 
I received a spreadsheet that was imported from a mailing program It had name in first column, street address in next column in separate rows instead of the next column same row. I expanded column but the stayed in separate rows. How do I move the city state to same row in next column. This spreadsheet is over 200 rows.
 
Try this where the name is in col A

Sub alignaddress()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -2
Cells(i, mc).Offset(1, 1).Copy Cells(i, mc).Offset(, 2)
Rows(i + 1).Delete
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...
 
Back
Top