Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Find / Replace in Excel 2007

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 07-10-2009
Member
 
Join Date: Aug 2009
Posts: 56
Find / Replace in Excel 2007
  

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:

Code:
Cell 1 Cell 2 Cell 3
Shall be as follows:

Code:
XCell 1X XCell 2X XCell 3X
I have tried to Find / replace, but I have not made it there.

Reply With Quote
  #2  
Old 07-10-2009
Member
 
Join Date: May 2008
Posts: 979
Re: Find / Replace in Excel 2007

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.
Reply With Quote
  #3  
Old 07-10-2009
Member
 
Join Date: Aug 2009
Posts: 56
Re: Find / Replace in Excel 2007

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.
Reply With Quote
  #4  
Old 07-10-2009
Member
 
Join Date: May 2008
Posts: 979
Re: Find / Replace in Excel 2007

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

Code:
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.
Reply With Quote
  #5  
Old 21-03-2012
Member
 
Join Date: Jul 2010
Posts: 4
Re: Find / Replace in Excel 2007

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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Find / Replace in Excel 2007"
Thread Thread Starter Forum Replies Last Post
integrating find tool in a cell referring another workbook in MS-excel 2007 shyra_grace Windows Software 1 10-04-2012 11:12 PM
How to find replace text in Excel Chini mao Windows Software 3 07-01-2012 10:44 AM
Office 2007 find/replace formatting characters NickCanuck Windows Software 4 07-08-2010 06:30 PM
Cannot find macro working in excel 2007 Visala Windows Software 3 06-08-2009 11:47 PM
How to find the last row of Excel 2007 windows_user Windows Software 3 01-06-2009 11:05 PM


All times are GMT +5.5. The time now is 06:41 PM.