Excel MAcro to insert text from one column to other

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

CLR

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
 
D

Dhawal

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

PeterAtherton

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
 
D

Dhawal

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
 
P

PeterAtherton

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
 
P

PeterAtherton

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
 
P

PeterAtherton

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
 
D

Dhawal

Thank you Peter,

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

Dhawal
 
Top