Find / Replace in Excel 2007

[KALANI84]

Sponsored Links

Is it possible to add a given text in all cells that contain text before without losing what is already there? A feature that is not Find / replace but rather Find / add. Thus, a feature that allows:

Cell 1 Cell 2 Cell 3

Shall be as follows:

XCell 1X XCell 2X XCell 3X

I have tried to Find / replace, but I have not made it there.

[Gunilla]

Unfortunately, you must either use a macro, or the cell next to (or under) it. The latter, let's say you want to add something to the A-column:

Cell B1:
="x"&A1&"x"

copy down the A column. Or, further: note B, Copy, dropdown button on the Paste button, Paste values. Delete a column.

Post back if you want a macro solution.

[KALANI84]

Great! This solution worked perfectly. Leave it on another sheet with reference to the current sheet, so I have two varieties available. Thank you!

If you have time and desire, so do I like macro solution also. If nothing else than to be able to experiment and learn about macros.

[Gunilla]

Open the VB editor (Alt F11). Menu Insert - Module. Paste the following:

Sub AddText() 
Dim RNG As Range, Cel As Range 
Dim S1 As String, S2 As String 
On Error Resume Next 
Set RNG = Application.InputBox ("Select the cells to have additional text:", _ 
"1 of 3", _ 
Selection.Address (True, True, Application.ReferenceStyle), _ 
Type: = 8) 
On Error GoTo 0 
If RNG Is Nothing Then Exit Sub 
Set RNG = Intersect (RNG, ActiveSheet.UsedRange) 
S1 = InputBox ("Text before the cell value:", "2 of 3") 
If StrPtr (S1) = 0 Then Exit Sub 'Cancel 
S2 = InputBox ("Text after the cell value:", "3 of 3") 
If StrPtr (S2) = 0 Then Exit Sub 
For Each Cel In RNG 
If Cel.Value <> "" Then 'not empty? 
If Cel.HasFormula = False Then 'not formula? 
Cel.Value = S1 & S2 & Cel.Value 
End If 
End If 
Next 
End Sub

In real life I would have made a simple user form instead of the three dialogues, but it is a little more high.

[TechGurus]

Good Tools are commercially also found on net where you can Find Replace in multiple Excel files (xls, xlsx) in Batch process like

excel-find-replace-batch