How do you insert a phrase at the start of all cells in a column?

  • Thread starter Thread starter Elskan
  • Start date Start date
E

Elskan

I am trying to insert the same phrase followed by a colon and a space in
front of the text in all cells in a column. Is there a simple way to do this
with find and replace?
 
change mc to your column

Sub insertphraseallcellsincolumn()
Dim i As Long
Dim mc As Long
mc = 11 'col K
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Len(Application.Trim(Cells(i, mc))) > 0 Then
Cells(i, mc).Value = "new stuff " & Cells(i, mc)
End If
Next i
End Sub
 
In a helper column enter ="phrase: " & A1

Copy down assuming current text cells are Column A

Adjust to suit.


Gord Dibben MS Excel MVP
 
Assuming your existing words are in column “B†you could put a Concatenate
function in the “A†column and “fill down†to combine the two. The function
in your case will look something like this:

=CONCATENATE("The phrase I want to insert",":"," ",B1)

A good way is to use a wizard. Just click on the fx symbol and in the
search box type “concantenateâ€. Press “go†and “okâ€. Enter in the “Text 1â€
box your phrase. Next box goes your colon, next box goes your space and for
the last, click on the cell with the existing words that are in column “Bâ€.

Once you have the basic formula in cell A1 you can fill down or just copy /
paste it into cell A2, A3 etc.

If you like the results, you can then convert your result from its current
status as a formula into a more useable form of text by copying column A and
using Paste Special to paste as a value.

If you want to experiment with a different and simple way of adding two
columns with words together just pick a cell and type an equals sign, then
select a cell containing the first text, then type “&†and click on a cell
containing the second text. Press enter

Good luck, you may want to read other posts about the Concatenate Function.
Quin
 
Back
Top