Adding text to a cell already containg text

[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

 

[J.E. McGimpsey]

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.

 

[Jeff Darling]

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

 

[Gord Dibben]

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 D3322 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