Combine columns

MReichardt

New Member
Joined
Mar 30, 2016
Messages
6
I have two columns of text data. I want to combine them (For example, one column with first name, second column with last name; I want one column with first and last name.) I have searched for the last couple days around the web and can't find any way that will work. I don't want formulae. I just want to combine data. Is there a way to do it without taking 15 steps through Notepad?

Thanks.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
I have two columns of text data. I want to combine them (For example, one column with first name, second column with last name; I want one column with first and last name.) I have searched for the last couple days around the web and can't find any way that will work. I don't want formulae. I just want to combine data. Is there a way to do it without taking 15 steps through Notepad?
Where is the combined text to go... a new column or replacing one of the existing columns? If the first one, what do you have against using a formula and, if the latter, what happens to the other column?
 
Last edited:

MReichardt

New Member
Joined
Mar 30, 2016
Messages
6
Where is the combined text to go... a new column or replacing one of the existing columns? If the first one, what do you have against using a formula and, if the latter, what happens to the other column?

One of the existing columns. I'm trying to sort a bunch of contact data into something that will work with Highrise. So, I took a bunch of VCFs and converted them to .csv. Now I'm trying to organize the data and a bunch of it (organization, post within that organization) can be combined into one column and that's what I'm trying to accomplish, rather than doing it cell by cell. It's also why I don't want to use a formula because then I'm afraid the content that get uploaded to Highrise will just be the formula, rather than the data itself. I also want to eliminate the superfluous columns before I try to upload, which means any formula would be invalidated.

Thanks!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
You could do what you want with a macro (change what I highlighted in red to the values that work with your data... use the variable name to tell you which they are for)...
Code:
[table="width: 500"]
[tr]
	[td]Sub CombineColumns()
  Dim StartRow As Long, LastRow As Long
  Dim FirstNameCol As String, LastNameCol As String, AddrFirst As String, AddrLast As String
  FirstNameCol = "[B][COLOR="#FF0000"]A[/COLOR][/B]"
  LastNameCol = "[B][COLOR="#FF0000"]B[/COLOR][/B]"
  StartRow = [B][COLOR="#FF0000"]1[/COLOR][/B]
  LastRow = Cells(Rows.Count, LastNameCol).End(xlUp).Row
  AddrFirst = Range(Cells(StartRow, FirstNameCol), Cells(LastRow, FirstNameCol)).Address
  AddrLast = Range(Cells(StartRow, LastNameCol), Cells(LastRow, LastNameCol)).Address
  Range(AddrFirst) = Evaluate("IF(" & AddrLast & "="""",""""," & AddrFirst & "&"" ""&" & AddrLast & ")")
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CombineColumns) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

MReichardt

New Member
Joined
Mar 30, 2016
Messages
6

ADVERTISEMENT

You could do what you want with a macro (change what I highlighted in red to the values that work with your data... use the variable name to tell you which they are for)...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CombineColumns()
  Dim StartRow As Long, LastRow As Long
  Dim FirstNameCol As String, LastNameCol As String, AddrFirst As String, AddrLast As String
  FirstNameCol = "[B][COLOR=#FF0000]A[/COLOR][/B]"
  LastNameCol = "[B][COLOR=#FF0000]B[/COLOR][/B]"
  StartRow = [B][COLOR=#FF0000]1[/COLOR][/B]
  LastRow = Cells(Rows.Count, LastNameCol).End(xlUp).Row
  AddrFirst = Range(Cells(StartRow, FirstNameCol), Cells(LastRow, FirstNameCol)).Address
  AddrLast = Range(Cells(StartRow, LastNameCol), Cells(LastRow, LastNameCol)).Address
  Range(AddrFirst) = Evaluate("IF(" & AddrLast & "="""",""""," & AddrFirst & "&"" ""&" & AddrLast & ")")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks. It keeps giving me a runtime error for AddrFirst and I assume would do the same for AddrLast. Says "range not defined." It's been a long time since I've done anything with macros, so I don't remember a lot of the basics.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
You could do what you want with a macro (change what I highlighted in red to the values that work with your data... use the variable name to tell you which they are for)...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CombineColumns()
  Dim StartRow As Long, LastRow As Long
  Dim FirstNameCol As String, LastNameCol As String, AddrFirst As String, AddrLast As String
  FirstNameCol = "[B][COLOR=#FF0000]A[/COLOR][/B]"
  LastNameCol = "[B][COLOR=#FF0000]B[/COLOR][/B]"
  StartRow = [B][COLOR=#FF0000]1[/COLOR][/B]
  LastRow = Cells(Rows.Count, LastNameCol).End(xlUp).Row
  AddrFirst = Range(Cells(StartRow, FirstNameCol), Cells(LastRow, FirstNameCol)).Address
  AddrLast = Range(Cells(StartRow, LastNameCol), Cells(LastRow, LastNameCol)).Address
  Range(AddrFirst) = Evaluate("IF(" & AddrLast & "="""",""""," & AddrFirst & "&"" ""&" & AddrLast & ")")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks. It keeps giving me a runtime error for AddrFirst and I assume would do the same for AddrLast. Says "range not defined." It's been a long time since I've done anything with macros, so I don't remember a lot of the basics.
Does the text I highlighted in red match with where your data is actually located?
 

MReichardt

New Member
Joined
Mar 30, 2016
Messages
6
Yes. At the moment I'm just testing it on columns D and E to combine area codes and phone numbers. D has area codes. E has phone numbers. I limited the LastRow value to 378, which is the last row I'm using.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top