Results 1 to 5 of 5

Thread: Find / Replace in Excel 2007

  1. #1
    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.

  2. #2
    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.

  3. #3
    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.

  4. #4
    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.

  5. #5
    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

Similar Threads

  1. Replies: 1
    Last Post: 10-04-2012, 11:12 PM
  2. How to find replace text in Excel
    By Chini mao in forum Windows Software
    Replies: 3
    Last Post: 07-01-2012, 10:44 AM
  3. Office 2007 find/replace formatting characters
    By NickCanuck in forum Windows Software
    Replies: 4
    Last Post: 07-08-2010, 06:30 PM
  4. Cannot find macro working in excel 2007
    By Visala in forum Windows Software
    Replies: 3
    Last Post: 06-08-2009, 11:47 PM
  5. How to find the last row of Excel 2007
    By windows_user in forum Windows Software
    Replies: 3
    Last Post: 01-06-2009, 11:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •