Combine columns

[MReichardt]

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.

 

[Rick Rothstein]

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?

 

[MReichardt]

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]

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)...

[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]

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)...

[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]

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)...

[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]

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.