add a word to text in a cell

[Guest]

I have a text string in a cell and I want to add a new text string in front
of the existing text string in the same cell. For example, the word "place"
is already in the cell and I want to add the word "this" in front of the word
"place" to form a new string in the cell that is "this place". How would I do
this in visual basic?

 

[Trevor Shuttleworth]

Alan

in Excel, you would do a global replace of "place" by "this place". Record
a macro while you do this manually and that should give you the code that
you need.

Regards

Trevor

 

[Guest]

Trevor, doing this didn't really help me. While the word I want to add is
always going to be the same, the word that I am adding it to will be
different in each new cell. How do I copy the text string from the existing
cell to a variable that can then be combined with the new word?

 

[Gord Dibben]

Alan

VBA macro........

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

In the "Enter your Text" inputbox enter "this "(no quotes and note the
<space>)


Gord Dibben Excel MVP