pasting same data over many cells containing a formula

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi, I am trying to insert data into cells that already have data in them.
Is there a way to get excel to do this simply? I know I can add a row of
formulas and get it done but I am told there is an easier way and I don't
see it.

Thanks!

Todd

ex. become
51986 w256 51986
51987 w256 51987
51988 w256 51988
 
Iif you don't want to use a formula like ="w256 " & A1 copied down the column.

You could try a macro.

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Else
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next
End If
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
Thanks,

Those are good ways, however, it is supposed to be much easier, like a pre
built excel function that does it. It isn't anything I know of, but I have
a friend who can't remember how, but saw it in a excel course.


Todd
 
Excel has never had such a function so it is not easier as you have been told.

Without helper cells and formulas or a macro there is no way to append or
prepend text to existing text or values.

Your friend may have seen a macro in action.

Or the the course instructor may be a moonlighting prestidigitator


Gord
 
You mean like an addin, you can package a macro as an addin.
There are many different ways of running a macro.
 
Back
Top