Excel MAcro to insert text from one column to other

  • Thread starter Thread starter Dhawal
  • Start date Start date
D

Dhawal

Hello,

I have a worksheet in which my data is arranged as per temp. I have
Temp in one column and the corresponding value in the other. I need to
inser


For eg.
I have
temp 25C in two adjacent columns


i have to make it Temp: 25C


and remove the 25C from that column.


Can I get some help with the Excel Macro for the same.


Dhawal...
 
Using a third, helper column such as column C, you could put this formula to
CONCATENATE the two adjacent cells into one.........

=A1&": "&B1

Then you could highlight column C and Do Copy > Pastespecial > Values to get
rid of the formulas.....then you could delete your old columns A and B if
you wish.........

hth
Vaya con Dios,
Chuck, CABGx3
 
Thank you.
But I have a whole bunch of columns to take care of, about 100 of them
and they are separate.. Is it efficient to do it this way.
 
Dhawal

This macro has no error checking - Select the "Temp"'s and run the macro -
it will delete the entire column with the actual temperatures.

Sub CONCAT()
For Each C In Selection
C.Value = C & ": " & C.Offset(0, 1)
Next
ActiveCell.Offset(0, 1).Select
Selection.EntireColumn.Delete
End Sub

Press Alt + F11 to open the VBE, choose Insert Module and copy the macro.
Return to the workbook select the Temps Press Alt + F8 choose the macro and
run it.

You will have to repeat this for each of your columns of data.

Regards
Peter
 
Hello,

I really apprecite the help. The macro solves my problem. But what if I
have a different data in the same column below 'temp'?

Thank you,

Dhawal
 
Dhawal

Well, the macro will only work on the selection. Different data below will
not be affected; so do not select the entire column. However, if you have
selected data in column A then the entire column B will be deleted.

I'll have another look and maybe I can delete just the data in column B if
that would be better for you.

Regards
Peter
 
I've rewritten the macro so that the offset column is just cleared rather
than deleted, I'm sure that it is safer this way.

Sub CONCAT()
' Only select the column that you want to
' join the data - not both columns.
For Each c In Selection
c.Value = c & ": " & c.Offset(0, 1)
c.Offset(0, 1).Value = ""
Next

End Sub

Hope this answers your query.
Regards

Peter
 
Dhawal

Better still this will only work if the selected cells contain "Temp" and
will ignore other entries.

Sub CONCAT()
' Only select the column that you want to
' join the data - not both columns.
For Each c In Selection
If Trim(LCase(c)) = "temp" Then
c.Value = c & ": " & c.Offset(0, 1)
c.Offset(0, 1).Value = ""
End If
Next

End Sub

Sorry for the delay - I went to bed.

Peter
 
Thank you Peter,

That absolute solves my problem. I really appreciate your help.

Dhawal
 
Back
Top