i want 2 add a word to the start of every colum in excel

O

oliver mangham

i have imported some a spread sheet, and i want to edit it and export it. the
problem is i want to add "alarms\" before the txt in a colmun
there is about 300 rows in the column so it would take to long to type
 
J

JulieD

Hi Oliver

one method is to use a helper column, insert a blank column to the right of
your data (i'm assuming your data is in column A and the new column is
therefore B)
in B1 type
="alarms\"&A1
press enter
move the cursor over the bottom right corner of B1 until you see a +
and then double click, this will fill the formula down the column for you
now select column B choose copy
click on column A and choose
edit / paste special - values, OK
now you can delete column B

Hope this helps
Cheers
JulieD
 
G

Gord Dibben

Another method involves VBA.

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

Gord Dibben Excel MVP
 
Top