Merging data in two columns

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. 

0
2/6/2008 5:54:05 PM
excel 39879 articles. 2 followers. Follow

13 Replies
597 Views

Similar Articles

[PageSpeed] 42

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

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>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. 

0
dguillett1 (2487)
2/6/2008 6:21:13 PM
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

"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>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. 


0
Tyro (331)
2/6/2008 6:29:02 PM
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.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
> 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
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>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.
> 

0
2/6/2008 6:38:01 PM
      Just put into a macro module. change mc="c" to mc="L" and fire the 
macro

      If you're new to macros, you may want to read David McRitchie's intro 
at:
      http://www.mvps.org/dmcritchie/excel/getstarted.htm


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
> 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.
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>> 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
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>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.
>>
> 

0
dguillett1 (2487)
2/6/2008 6:51:32 PM
Thank you very much for your prompt response. It worked, but only for the 
first two rows. Any suggestions?

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>      Just put into a macro module. change mc="c" to mc="L" and fire the 
> macro
>
>      If you're new to macros, you may want to read David McRitchie's intro 
> at:
>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>> 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.
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>> 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
>>>
>>> -- 
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>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.
>>>
>>
> 

0
2/6/2008 7:04:31 PM
Tested fine. Send me your workbook, if desired. Send to the address below, 
NOT the ng.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
> Thank you very much for your prompt response. It worked, but only for the 
> first two rows. Any suggestions?
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>      Just put into a macro module. change mc="c" to mc="L" and fire the 
>> macro
>>
>>      If you're new to macros, you may want to read David McRitchie's 
>> intro at:
>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>> 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.
>>>
>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>> 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
>>>>
>>>> -- 
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> dguillett1@austin.rr.com
>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>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.
>>>>
>>>
>>
> 

0
dguillett1 (2487)
2/6/2008 7:13:18 PM
I do not think it will make it. It is 47mb.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:Okt8cRPaIHA.5208@TK2MSFTNGP04.phx.gbl...
>
> Tested fine. Send me your workbook, if desired. Send to the address below, 
> NOT the ng.
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
> news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
>> Thank you very much for your prompt response. It worked, but only for the 
>> first two rows. Any suggestions?
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>>      Just put into a macro module. change mc="c" to mc="L" and fire the 
>>> macro
>>>
>>>      If you're new to macros, you may want to read David McRitchie's 
>>> intro at:
>>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>
>>>
>>> -- 
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>>> 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.
>>>>
>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>>> 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
>>>>>
>>>>> -- 
>>>>> Don Guillett
>>>>> Microsoft MVP Excel
>>>>> SalesAid Software
>>>>> dguillett1@austin.rr.com
>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>>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.
>>>>>
>>>>
>>>
>>
> 

0
2/6/2008 7:22:43 PM
Send part..... ( the part with the problem)

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:uvl$6WPaIHA.4208@TK2MSFTNGP04.phx.gbl...
>I do not think it will make it. It is 47mb.
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:Okt8cRPaIHA.5208@TK2MSFTNGP04.phx.gbl...
>>
>> Tested fine. Send me your workbook, if desired. Send to the address 
>> below, NOT the ng.
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>> news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
>>> Thank you very much for your prompt response. It worked, but only for 
>>> the first two rows. Any suggestions?
>>>
>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>>>      Just put into a macro module. change mc="c" to mc="L" and fire the 
>>>> macro
>>>>
>>>>      If you're new to macros, you may want to read David McRitchie's 
>>>> intro at:
>>>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>
>>>>
>>>> -- 
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> dguillett1@austin.rr.com
>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>>>> 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.
>>>>>
>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>> -- 
>>>>>> Don Guillett
>>>>>> Microsoft MVP Excel
>>>>>> SalesAid Software
>>>>>> dguillett1@austin.rr.com
>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>>>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.
>>>>>>
>>>>>
>>>>
>>>
>>
> 

0
dguillett1 (2487)
2/6/2008 7:48:48 PM
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.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:ucBVSlPaIHA.4196@TK2MSFTNGP04.phx.gbl...
> Send part..... ( the part with the problem)
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
> news:uvl$6WPaIHA.4208@TK2MSFTNGP04.phx.gbl...
>>I do not think it will make it. It is 47mb.
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>> news:Okt8cRPaIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>
>>> Tested fine. Send me your workbook, if desired. Send to the address 
>>> below, NOT the ng.
>>>
>>> -- 
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>> news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
>>>> Thank you very much for your prompt response. It worked, but only for 
>>>> the first two rows. Any suggestions?
>>>>
>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>>>>      Just put into a macro module. change mc="c" to mc="L" and fire 
>>>>> the macro
>>>>>
>>>>>      If you're new to macros, you may want to read David McRitchie's 
>>>>> intro at:
>>>>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>>
>>>>>
>>>>> -- 
>>>>> Don Guillett
>>>>> Microsoft MVP Excel
>>>>> SalesAid Software
>>>>> dguillett1@austin.rr.com
>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>>>>> 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.
>>>>>>
>>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>>>>> 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
>>>>>>>
>>>>>>> -- 
>>>>>>> Don Guillett
>>>>>>> Microsoft MVP Excel
>>>>>>> SalesAid Software
>>>>>>> dguillett1@austin.rr.com
>>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>>>>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.
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
> 

0
2/6/2008 8:51:56 PM
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))

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
news:e25fxIQaIHA.536@TK2MSFTNGP06.phx.gbl...
> 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.
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:ucBVSlPaIHA.4196@TK2MSFTNGP04.phx.gbl...
>> Send part..... ( the part with the problem)
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>> news:uvl$6WPaIHA.4208@TK2MSFTNGP04.phx.gbl...
>>>I do not think it will make it. It is 47mb.
>>>
>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>> news:Okt8cRPaIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>>
>>>> Tested fine. Send me your workbook, if desired. Send to the address 
>>>> below, NOT the ng.
>>>>
>>>> -- 
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> dguillett1@austin.rr.com
>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>> news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
>>>>> Thank you very much for your prompt response. It worked, but only for 
>>>>> the first two rows. Any suggestions?
>>>>>
>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>>>>>      Just put into a macro module. change mc="c" to mc="L" and fire 
>>>>>> the macro
>>>>>>
>>>>>>      If you're new to macros, you may want to read David McRitchie's 
>>>>>> intro at:
>>>>>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>>>
>>>>>>
>>>>>> -- 
>>>>>> Don Guillett
>>>>>> Microsoft MVP Excel
>>>>>> SalesAid Software
>>>>>> dguillett1@austin.rr.com
>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>>>>>> 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.
>>>>>>>
>>>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>>>>>> 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
>>>>>>>>
>>>>>>>> -- 
>>>>>>>> Don Guillett
>>>>>>>> Microsoft MVP Excel
>>>>>>>> SalesAid Software
>>>>>>>> dguillett1@austin.rr.com
>>>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>>>>>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.
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
> 

0
dguillett1 (2487)
2/6/2008 9:11:37 PM
That was the clue I needed. I got it to work on the whole spreadsheet now. 
Thank you very much for your assistance.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:OtTRkTQaIHA.4808@TK2MSFTNGP05.phx.gbl...
>
> 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))
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
> news:e25fxIQaIHA.536@TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>> news:ucBVSlPaIHA.4196@TK2MSFTNGP04.phx.gbl...
>>> Send part..... ( the part with the problem)
>>>
>>> -- 
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>> news:uvl$6WPaIHA.4208@TK2MSFTNGP04.phx.gbl...
>>>>I do not think it will make it. It is 47mb.
>>>>
>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>> news:Okt8cRPaIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>>>
>>>>> Tested fine. Send me your workbook, if desired. Send to the address 
>>>>> below, NOT the ng.
>>>>>
>>>>> -- 
>>>>> Don Guillett
>>>>> Microsoft MVP Excel
>>>>> SalesAid Software
>>>>> dguillett1@austin.rr.com
>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>> news:%23SoVwMPaIHA.5160@TK2MSFTNGP05.phx.gbl...
>>>>>> Thank you very much for your prompt response. It worked, but only for 
>>>>>> the first two rows. Any suggestions?
>>>>>>
>>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>>> news:eOQGSFPaIHA.4440@TK2MSFTNGP06.phx.gbl...
>>>>>>>      Just put into a macro module. change mc="c" to mc="L" and fire 
>>>>>>> the macro
>>>>>>>
>>>>>>>      If you're new to macros, you may want to read David McRitchie's 
>>>>>>> intro at:
>>>>>>>      http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>>>>
>>>>>>>
>>>>>>> -- 
>>>>>>> Don Guillett
>>>>>>> Microsoft MVP Excel
>>>>>>> SalesAid Software
>>>>>>> dguillett1@austin.rr.com
>>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>>> news:9D69747C-E93F-4099-9404-5814F59ABDBA@microsoft.com...
>>>>>>>> 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.
>>>>>>>>
>>>>>>>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>>>>>>> news:%23FgOW0OaIHA.4476@TK2MSFTNGP06.phx.gbl...
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>> -- 
>>>>>>>>> Don Guillett
>>>>>>>>> Microsoft MVP Excel
>>>>>>>>> SalesAid Software
>>>>>>>>> dguillett1@austin.rr.com
>>>>>>>>> "JEC" <thejohncarlson@REMOVEMEverizon.net> wrote in message 
>>>>>>>>> news:AE44D097-58A6-463A-AE25-439D4E9EF14B@microsoft.com...
>>>>>>>>>>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.
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
> 

0
2/6/2008 9:22:24 PM
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.
0
John
6/3/2008 12:11:18 AM
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
dguillett1@austin.rr.com
"John Capizzi" wrote in message news:200862201117ezfromsc@yahoo.com...
>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. 

0
dguillett1 (2487)
6/3/2008 1:21:41 PM
Reply:

Similar Artilces:

merging
Is it possible to create a document in publisher (mine is a certificate) and then merge the desired information from a select querry in access? Hi Tina (sttpreston@clyde.k12.oh.us), in the Microsoft� newsgroups you posted: || Is it possible to create a document in publisher (mine is || a certificate) and then merge the desired information from || a select querry in access? Yes, you can. Which version of Publisher and Windows are you using? -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" wit...

Merge same transaction
I've had transactions that I've placed in my register with detailed info in the memo area only to have the same exact transaction download with no info and unrecognized as the same transaction. Traditionally I've copied and pasted the info into the downloaded transaction and then deleted my transaction. This is a real PAIN... Is there a way to tell Money that these two separate transactions are the SAME transaction and then have it merge them? It works exactly as intended when Money recognizes the transaction that's already in the register and then merges them beautiful...

creating an address data base #2
I would like to create a database of clients, and when i type ne invoices i would like to select them so the address etc will fill int the address block automaticly -- adspreadboroug ----------------------------------------------------------------------- adspreadborough's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565 View this thread: http://www.excelforum.com/showthread.php?threadid=27192 ...

Importing data from Maximizer
Hi: What experience has anyone had with importing Maximizer data into MSCRM using either the Data Migration tool, Scribe, or a combination of both? Scribe don't handle this seamlessly, you apparently need to export to a .csv file first. Is this still quicker using this process with Scribe, than with the DMF? Many Thanks Cathy Allington Hi, We are also looking at migrating Maximizer into CRM. If you would not mind, could you please let me know if you find anything more on this. I would truly appreciate it. Shauna skoppang@shaw.ca "Cathy Allington" wrote: > Hi: > ...

Word mail merge
After printing a document there exists a activity with subject "Word Mail Merge" to the contact with status completed. Is there a possibility to change the subject name or to make a link to the original Word document? R, Joop. I think that the subjct name is hardcoded. The issue of only adding this paltry information to the activity and not showing what document was sent is a problem many have asked about. Microsofts workaround to this glaring gap in functionality is to then add a copy of the word document to the record using notes. Totally unworkable if you have just mail merg...

Accessing two tables from a single mfc application
Hi, I need to know how to access two tables using a single mfc(vc++) application. I also need to know how to extract data from a table using an mfc application using a primary key in a table.It is very important that I get answers for these two questions as this is part of my final year college project and I have very little time to complete this. Please Help! Thanks, Satish. "Satish Chandrasekar" <yourdisplayname@discussions.microsoft.com> wrote in message news:A0D7EC0A-18C3-4375-B5A5-AFDB1AB2F577@microsoft.com... > Hi, > I need to know how to access two tab...

How can I run two powerpoint sessions?
I want to compare two power point presentations on a system with two monitors. Each monitor would have a powerpoint session running so that I could see them side by side. I do not want to run both power point presentation in one session as this, even when maximised only fills one monitor and there is no enough screen space to see the two presentatiosn side by side. This is easily done in both Word and Excell simply by starting a new sessin when the firts one ins minimused. But I cant seem toget Power point to follow suite. With the first presentation minimized, Hold down the ...

Merging 2 Companies into 1
Are there any guidelines or outline for what tables you need to look at to gather the various information you need in order to merge 2 companies together as well as issues to consider when undertaking such a project? It is a project and a half. I got approval from a major client yesterday to proceed with just this project. You literally need to look at all of them! I understand that MS Professional Services will do this project for you. In my case the client cannot be down and has too many 3rd party products for us to pass it off. When does your project need to be completed? -- Ri...

How do I use 2 sets of data in a chart
both sets of data are in the same workbook, but on two different worksheets. Hi, Have read of Jon's page http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html Cheers Andy Skeety wrote: > both sets of data are in the same workbook, but on two different worksheets. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

adding data from multiple spreadsheets into one chart
I need to put information from 4 different spreadsheets into one graph. Can someone please help me? Produce a graph from your first spreadsheet. Copy the relevant data range from the second spreadsheet, then select the graph and edit/ paste special/ new series or new data points. Similarly for the third and fourth sheets. If you have difficulties getting exactly what you want that way, you can use edit Source Data in the graph to add an extra series or to change the X or Y data range for an existing sreries. -- David Biddulph "Marjory" <Marjory@discussions.microsoft.com&g...

mail merge/merging 2 address lists...
Is there a way to merge two of your address lists in Publisher 2002? I have two address lists in my data base folder and would like all the addresses merged into one as there are some addresses different in each and some the same. Thank you. Robin Hi Robin (vicary@kconline.com.NOSPAM), in the Microsoft� newsgroups you posted: || Is there a way to merge two of your address lists in Publisher 2002? || I have two address lists in my data base folder and would like all || the addresses merged into one as there are some addresses different || in each and some the same. No, you cannot. You nee...

merge and compare
We have two versions of the same work sheet from differnt dates. Some of the cells have been changed in the latest version and we need to compare which cells have had changes and selectively merge the two. Is there any hope??? jimired wrote: > We have two versions of the same work sheet from differnt dates. > > Some of the cells have been changed in the latest version and we need > to compare which cells have had changes and selectively merge the two. > > Is there any hope??? Hi have a look at http://www.cpearson.com/Zips/Compare.ZIP An add-in which compares two worksh...

Adjusting column width doesn't Wrap the text
I have users that need to type in text into cells in Excel but the wrap text within the cells doesn't seem to work properly. On what way? Have you turned it on under format>cells>alignment and wrap text. To force it w/o applying text wrap change lines using alt + enter -- Regards, Peo Sjoblom "Duke" <duke@fpwk.com> wrote in message news:2948401c39197$96eb0620$a601280a@phx.gbl... > I have users that need to type in text into cells in Excel > but the wrap text within the cells doesn't seem to work > properly. Yikes! I am turning into DP! [In wh...

Any way to shift data axes?
Helo all, I am having problems getting some data into the format I need. I'm wondering if this is even possible actually. I'm sure it is, but I'd prefer to keep things simple. Data is coming in from a linked Excel table in a format like the following MRC VEH 1 2 3 4 5 101 AAA 12 34 56 78 90 101 BBB 12 34 56 78 90 101 CCC 12 34 56 78 90 102 AAA 12 34 56 78 90 102 BBB 12 34 56 78 90 102 CCC 12 34 56 78 90 103 etc... MRC and VEH denote the cost centre and vehicle types. For those combinati...

How can I get bar codes w/ Excel & mail merge?
I want to send out a bulk mailing with large postcards. I want the addresses printed on the postcards via mail merge. I am using Excel, but don't see where or how to add the bar code to the address, which would save me a lot of money. Can you help? >-----Original Message----- >I want to send out a bulk mailing with large postcards. I want the addresses >printed on the postcards via mail merge. I am using Excel, but don't see >where or how to add the bar code to the address, which would save me a lot of >money. Can you help? >. > hi, I not entirely...

formula for adding / subtracting two columns .
Can't find formula to add or subtract two or three entire columns in a spread sheet Thank you Let's say we have numbers in two columns, column A and column B from rows 1 thru 100. In C1, enter: =A1+B1 and copy down thru C100 Column C will be the sum of columns A & B. To get the grand sum of columns A & B in a single cell, enter in an un-used cell: =SUM(A1:B100) -- Gary''s Student "jm" wrote: > Can't find formula to add or subtract two or three entire columns in a spread > sheet > Thank you Hi Try something like: =SUM(A:A)-SUM(B:B)-S...

Merging worksheet into MS Word mail merge
I am merging a letter and an Excel worksheet. When I merge an Excel field that contains $ and commas separating the hundreds and thousands, the $ and the , do not show up in the merged letter. Any suggestions on how to solve this problem? -- Briggs Hi Instead of just referring to the cell e.g. A1 use =TEXT(A1,"[$$-409]#,##0.00") -- Regards Roger Govier "Briggs" <Briggs@discussions.microsoft.com> wrote in message news:2F326201-A50F-45E5-9767-4C189ACA5529@microsoft.com... > I am merging a letter and an Excel worksheet. When I merge an Exc...

Error in Mail Merge when selecting Edit Individual Labels
We are noticing an interesting error when trying to perform a mail merge within CRM and Microsoft Word. We have created a view in contacts and we perform all the typical mail merge functionality using the Labels option. At the end, instead of printing the labels without any edits, we click on “Edit Individual labels” and we get the following error… C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE has encountered a problem and needs to close. We are sorry for the inconvenience. We are on CRM v4 Update Rollup 8 using Outlook 2007 and the CRM Outlook (Online only) client. Has an...

Why does picking an cell highlight two cells?
In an Excel 2007 worksheet, some cells when picked with the mouse highlight the adjacent cell. The cells are not merged and data is only entered into the cell that was picked. Hi, 2 possibilities. 1. Tap F8. Did that cure it? 2. If it didn't take the zoom level up and down. There are reports of a bug in Excel 2007 that causes this and reportedly changing the zoom level clears the problem -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "...

importing data using a macro #2
I'm trying to import ten sets of data in Excel. I click on import external data and then choose a file and press next. I add a space to separate the data into two columns and then press finish. I was wondering if it would be possible to use a macro to import the data. Possibly import the first one and then have the macro import the next 9 in succession. The files I import are always in succession, but always have slightly different names. For example, the data I collect today will be saved as 718cr1, 718cr2, 718cr3, etc. The last number represents the trial number and the fir...

Transfer data from XML to Stream?
Hi all, I have a COM component that receives XML documents from MSMQ. Before I process the final documents and pass it to my application, I want to modify the format a bit, as in create a name/value pair structure from an element structure or rename certain fields. I'm using XmlTextReader to read over the original doc and I'm using XmlTextWriter to create a new XML file that I essentially need to load up again into an XMLDocument object. I'd rather write the modified XML to a stream local to my running instance of the COM component rather than create another physical file then ...

Two weird events..........thoughts?
ONE: One of my LAN user sent out an email today with 1 person on the TO and two people on the CC list. One of the recipients is also on the LAN running Win9x and the second recipient is also on the LAN running Win2000. The 9x user only got one email (like a normal email delivery). However the win2000 user got THREE of the same email messages. Both LAN users use Outlook 2000. I am not certain how the email showed upon the three recipient's email client program. Why and how might this of happend. I have a FreeBSD box for my mailserver running Sendmail/Qpopper. TWO: When...

Whats the formula to look at data in precentages in fixed periods
how do I calculate a continous percentage return of two data points at fixed intervals? example : data set={12,50,03,59,22,....} , fixed interval=2periods. [(12-03)/12], [(50-59)/50], [(03-22)/03],....I'd like to do this in Excel 03 "donald" wrote: > how do I calculate a continous percentage return > of two data points at fixed intervals? > example : data set={12,50,03,59,22,....} , > fixed interval=2periods. > [(12-03)/12], [(50-59)/50], [(03-22)/03], Assuming data is in A2 down: 12,50,3,59,22,... In B2: =(A2-A4)/A2 B2 formatted as percentage, then copi...

how to split data in a range to many ranges
I have data in a range, like 1,2,3,4,5. I want to split each of them to five different ranges. How to do it. thanks. -- Paul Data|Text to columns Delimited by commas Looks like it should work. Paul wrote: > > I have data in a range, like 1,2,3,4,5. > I want to split each of them to five different ranges. > How to do it. thanks. > > -- > Paul -- Dave Peterson Thank you, Dave. I made it. "Dave Peterson" <ec35720@netscapeXSPAM.com> ???????:421882CD.22F31915@netscapeXSPAM.com... > Data|Text to columns > Delimited by commas > > Looks...

look up a selection of data
Hi I would like to transfer or look up data when keying in a reference number and bring up all the data regarding the ref number Eg Cell A1 = 101, B1 = John and C1 = january Cell A2 = 102, B2 = Mark and C2 = january Cell A3 = 103, B3 = Sam and C3 = February Cell A4 = 101, B4 = John and C4 = March Cell A5 = 101, B5 = John and C5 = June When I key in Ref 101 in a chosen cell (eg.A10) then the display should have all the details Cell A11 = John, B11 = January Cell A12 = John, B12 = March Cell A13 = John, B13 = June I assume A11, A12 and A13 should also have formulas to generate data but I c...