Add same text at end of each cell?

  • Thread starter Thread starter bubba1965
  • Start date Start date
B

bubba1965

Is there a way to add a comma and a space and a piece of text at the
end of each cell in a column.

The text that is added is the same for each cell

Is this possible with find and replace or format. I guess I want t
know if this is possible without having to manually enter this info i
each of the thousands of rows within this colum
 
One way:

insert a column next to your column of interest (say column K). Then
enter

K1: =J1 & ", my addtional text"

copy down as far as necessary.

Copy column K. Select column J and choose Edit/Paste Special, selecting
the Values radio button. Click OK.

Delete column K.
 
bubba

Formula and a helper column...........

Enter in an adjacent cell =A1 & ", text"

Drag/copy down as far as you need. Then copy the helper column and Paste
Special>Values. Delete column A if wish.

VBA Macro..........

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

Select the range of cells and run macro.

When the input box comes up enter your comma<space>text and OK

Gord Dibben Excel MVP
 
thanks so much.

Both methods worked extremely well.

I especially liked the ease of the Macro. I appreciate your assistance
 
OK, I guess I figured out the macro.

But I am unsure of the syntax of the other method.

Do I keep the quotation marks around my selected text and what is th
easiest way to copy that formula to all of the cells in my helpe
column.

I understand the logic, I guess I just haven't figured out the correc
syntax or I am missing a step, because it is just copying the formul
in each cell.

Thanks for your hel
 
I have one more macro question for this situation.

Is it possible to modify the macro to achieve the following:

I have 45 sheets in my workbook. Is it possible to use one macro t
change this entire workbook if every cell that I want to change i
located in the same column in each sheet and if I want the text that i
to be added to be the name of that specific worksheet.

So I would want a comma, then a space and then the name of th
worksheet - at the end of the text in each cell in column A

Thanks for your hel
 
bubb

Try this alteration.

Sub Add_Text_Right()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
Dim ws As Worksheet
On Error GoTo justformulas
Application.ScreenUpdating = False
Set wkbkToCount = ActiveWorkbook
For Each ws In wkbkToCount.Worksheets
ws.Activate
Set thisrng = ws.Range("A1", Range("A" & Rows.Count). _
End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = ", " & ws.Name
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next cell
Next ws
Application.ScreenUpdating = True
Exit Sub
justformulas:
MsgBox "only formulas in range"
End Sub

Gord Dibben Excel MVP
 
Back
Top