+ Reply to Thread
Results 1 to 11 of 11

Search and replace text

  1. #1
    Registered User
    Join Date
    05-21-2014
    Posts
    32

    Search and replace text

    Hi,

    I want to append numbers for a particular text.
    For example, the word 'DIRECTV' available in many places in a sheet. I want 'DIRECTV' to be replaced with 'DIRECTV 1' for first find and 'DIRECTV 2' for second find and also on. I tried some macro with the help of google but couldn't finish it as I have less VBA knowledge. Please help me on this.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Search and replace text

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Cheers!
    Deep Dave

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Search and replace text

    May be this?

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    05-21-2014
    Posts
    32

    Re: Search and replace text

    Thanks Sixthsense.
    It works great however a small issue, Range("A1") should contain first value right? i.e. 'DIRECTV 1' instead it gets the last find value 'DIRECTV 52'. Any idea?

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Search and replace text

    Change this line

    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    And see whether it solves your issue

  6. #6
    Registered User
    Join Date
    05-21-2014
    Posts
    32

    Re: Search and replace text

    No, issue remains same.

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Search and replace text

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  8. #8
    Registered User
    Join Date
    05-21-2014
    Posts
    32

    Re: Search and replace text

    Sixthsense,
    It contains highly confidential data.
    I just inserted a row (header) and run your macro. Now, it gives the output as I required. Thanks.
    I am going to google for a macro to insert a row and to delete inserted row. I don't want to insert row manually each time.
    Thanks again

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Search and replace text

    It seems that this problem only occur when the first cell in the range contains the string you wish to change so you could also try to add the following lines to code you got from Sixthsense after the "Application.ScreenUppdating" line.

    Please Login or Register  to view this content.
    Alf
    Last edited by Alf; 08-10-2016 at 04:07 AM.

  10. #10
    Registered User
    Join Date
    05-21-2014
    Posts
    32

    Re: Search and replace text

    Thanks Alf

  11. #11
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Search and replace text

    You are welcom but looking again at Sixthsenses code I think modification should just be

    Please Login or Register  to view this content.
    Alf

    Ps morale of story, never suggest modefication of other members code unless you are really sure you know you are doing

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how to make search and replace as text and not a number
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2016, 02:24 PM
  2. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  3. Help Automating or trying to come up with a way to search and replace a text string.
    By workerbee12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2014, 03:14 PM
  4. [SOLVED] VBA to Search a Text String, Find a Value listed in another column and replace the text
    By Jschrod303 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2012, 08:56 PM
  5. [SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd
    By kldailey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2012, 05:23 PM
  6. Search and replace a line of text in a CSV file
    By tomlancaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2010, 09:02 AM
  7. Open Another File to Search and Replace Text
    By Ricky Pang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2006, 01:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1