Adding text to a cell already containg text

  • Thread starter Thread starter csab
  • Start date Start date
C

csab

Cells d3:d322 all contain the following text:

!!NEW!! 04 OPC Blue Line #/500


Cells e3:e32 all contain different names.

What I want to do is to insert cell e3 into d3, e4 into d4, e5 into d5,
etc. but I want the insertion to be placed after 500 (there must be
after 500 and the inserted cell) so it would look like this if e3 =
John Smith

!!NEW!! 04 OPC Blue Line #/500 JOHN SMITH


Thanks in advance for your help.

Cheers!

Craig
 
One way:

in column F (or some other convenient column) enter:

F3: =D3 & " " & UPPER(E3)

Copy this down to F322

then copy F3:F322 and Paste Special into D3, selecting the Values
radio button. Then delete F3:F322.
 
It sounds like you just want to concatenate.

This should work.

=(D3 & " " & E3)

Put this in cell F3. If you already have stuff in F3 then
just insert another column (I'm assuming you haven't used
all the columns and that there is room.)
 
Manually you would need a helper column, say F.

In F3 enter =D3 & " " & E3

Drag/copy down to F322

Copy column F and Paste Special>Values(in place). You can now delete columns
D and E if you wish.

Using VBA

Select D3:D322 and run the following macro.

Sub Add_Text_Right()
Dim cell As Range
Dim moretext As Range
Dim thisrng As Range
On Error GoTo justformulas
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
Set moretext = ActiveCell.Offset(0, 1)
For Each cell In thisrng
cell.Value = cell.Value & " " & moretext.Value
Next
Exit Sub
justformulas:
MsgBox "only formulas or numbers in range"
End Sub

Gord Dibben Excel MVP XL2002
 
Back
Top