VBA Find and copy text with parentheses in a range of columns to new cell and clear old data

[BHW010116]

I am trying to figure out a way to search a range of columns for any thing that starts with an open parentheses and ends with a closing parentheses. Copy that data into one cell and then remove that txt from the original cells. I would prefer to do this using VBA instead of a formula but I am not sure how to get this to work. Any ideas? I posted what my data currently looks like and what I am trying to get my data to look like.

[TABLE="width: 1119, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]W & V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD](MUST SEND 07H0105-01[/TD]
[TD]WITH UNIT 2PCS PER)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W & V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD](MUST SEND 07H0105-01[/TD]
[TD]WITH UNIT 2PCS PER)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]650W 120V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD](THIS ETCH IS CORRECT) Watts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000W 120V[/TD]
[TD]"DO NOT RUN WITHOUT AIRFLOW"[/TD]
[TD](SEND TO HDA FOR HARDWARE)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3000W 230V[/TD]
[TD]"DO NOT RUN WITHOUT AIRFLOW"[/TD]
[TD](SEND BACK TO HDA FOR[/TD]
[TD]COMPLETION)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NECKDOWN AREA/ETCH AS CLOSE TO[/TD]
[TD]BOTTOM OF UNIT AS POSSIBLE[/TD]
[TD]/(SEND UNIT TO SLCT[/TD]
[TD]TO BE FINISHED)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]"DO NOT RUN WITHOUT AIRFLOW"[/TD]
[TD](send Unit[/TD]
[TD]To SLCT to[/TD]
[TD]Be Finished)[/TD]
[/TR]
</tbody>[/TABLE]
















[TABLE="width: 1438, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]W & V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](MUST SEND 07H0105-01 WITH UNIT 2PCS PER)[/TD]
[/TR]
[TR]
[TD]W & V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](MUST SEND 07H0105-01 WITH UNIT 2PCS PER)[/TD]
[/TR]
[TR]
[TD]650W 120V[/TD]
[TD]DO NOT RUN WITHOUT AIRFLOW[/TD]
[TD]Watts[/TD]
[TD][/TD]
[TD][/TD]
[TD](THIS ETCH IS CORRECT)[/TD]
[/TR]
[TR]
[TD]1000W 120V[/TD]
[TD]"DO NOT RUN WITHOUT AIRFLOW"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](SEND TO HDA FOR HARDWARE)[/TD]
[/TR]
[TR]
[TD]3000W 230V[/TD]
[TD]"DO NOT RUN WITHOUT AIRFLOW"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](SEND BACK TO HDA FOR COMPLETION)[/TD]
[/TR]
[TR]
[TD]NECKDOWN AREA/ETCH AS CLOSE TO[/TD]
[TD]BOTTOM OF UNIT AS POSSIBLE[/TD]
[TD]/[/TD]
[TD][/TD]
[TD][/TD]
[TD](SEND UNIT TO SLCT TO BE FINISHED)[/TD]
[/TR]
[TR]
[TD] company [/TD]
[TD] Do not run without airflow [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](SEND UNIT TO SLCT TO BE FINISHED)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub CombineAndMoveTextInParentheses()
  Dim R As Long, LastCol As Long, Txt() As String
  LastCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Txt = Split(Replace(Application.Trim(Join(Application.Index(Range(Cells(R, "C"), Cells(R, LastCol)).Value, 1, 0))), ")", "("), "(")
    Range(Cells(R, "C"), Cells(R, LastCol)).Clear
    Cells(R, "C").Value = Application.Trim(Txt(0) & " " & Txt(2))
    Cells(R, LastCol + 1) = "(" & Txt(1) & ")"
  Next
End Sub[/td]
[/tr]
[/table]