Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Multiple substitute or replace text using a table

  1. #1
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    Multiple substitute or replace text using a table

    I'd like to use VBA to create a super substitute function. For my needs, nesting is insufficient because my substitution list is at 20 and growing. To make matters worse, the function needs to be used in several places.

    What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text. This way, whenever new items come up, all I have to do is add them to the list. The syntax of the function would be along the lines of SUPERSUB(TextString, table), where TextString contains the text that could be modified.

    I have no idea how to start. Thanks for any help.

    Steve

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    Re: Multi-substitute using a table

    It's been a week and no one has given it a shot. Oh well. How about a little guidance then? I suspect that it isn't all that complicated, but my VBA isn't very strong.

    It seems that I'd have to set up a loop to work my way down the table, and for each entry in the table, execute a VBA version of SUBSTITUTE(). I'd also need some way to stop at the end of the table.

    Any code samples that step through a table or that has a VBA statement or Function similar to SUBSTITUTE() would be very helpful. Thanks in advance.

    Steve

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Multi-substitute using a table

    Hi steve...

    Could you attach a small sample of your data, illustrating the data before & after... that would help - I am thinking that the VBA Replace function would probably be the way I would handle this...

    To illustrate it's use in the meantime, here is some example code I wrote a while back that for a range of cells gets user input for what to replace with what....

    VB:
    Sub replace_text() 
        Dim strMyChar As String 
        Dim strMyReplace As String 
        Dim rngMyRange As Range 
        On Error Resume Next 
        Do 
            Set rngMyRange = Application.InputBox _ 
            (prompt:="Select a range of cells to action", Type:=8) 
            On Error Goto 0 
             'Is a range selected? Exit sub if nowt selected
            If rngMyRange Is Nothing Then 
                End 
            Else 
                Exit Do 
            End If 
        Loop 
        strMyChar = Application.InputBox _ 
        (prompt:="What do you want to replace?", Type:=2) 
        strMyReplace = Application.InputBox _ 
        (prompt:="What do you want to replace it with?", Type:=2) 
        With rngMyRange 'with the range just selected
            .Replace What:=strMyChar, Replacement:=strMyReplace, _ 
            SearchOrder:=xlByColumns, MatchCase:=True 
        End With 
    End Sub 
    
    
    Last edited by Will Riley; February 24th, 2005 at 06:41.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  4. #4
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    Re: Multi-substitute using a table

    Thanks WillR. I've attached a spreadsheet with sample data.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Multi-substitute using a table

    Ok, gimme a bit.... I'll go refill the beer & get back to you

    Are you ok with opening the files & looping thru the worksheets... ? or do you need help with that too ?
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Multi-substitute using a table

    OK

    That was quite fun ....This code worked fine on 3 test files i selected.
    I have commented the code to explain - I assumed that the range A4:A10 was text that needed replacing by B4:B10 - just expand the ranges in the master file if you need to adding extra words/phrases...

    VB:
    Option Explicit 
     
    Sub String_Replacer() 
        Dim ws As Worksheet, wb As Workbook 
        Dim fList As Variant, I As Integer 
        Dim rng1 As Range 
        Dim cel As Range 
        Dim strMyChar As String, strMyReplace As String 
         
        With ActiveWorkbook.Worksheets("sheet1") 
            Set rng1 = .[A4:A10] 
        End With 
         'displays the dialog fo rchoosing files to action
        fList = Application.GetOpenFilename(MultiSelect:=True) 
         'check and see if cancel selected, which returns a boolean variable
        If TypeName(fList) = "Boolean" Then 
            MsgBox "No files selected. Activity halted." 
            Exit Sub 
        End If 
         'Loops through every file that is selected and open
        For I = 1 To UBound(fList) 
             'open the workbook, but do not update links
            Set wb = Workbooks.Open(fList(I), False) 
             'loop thru sheets used range
            For Each ws In wb.Worksheets 
                 'loop down list of text needing replacing
                For Each cel In rng1.Cells 
                    strMyChar = cel.Value 
                    strMyReplace = cel.Offset(0, 1).Value 
                     'replace text
                    With ws.UsedRange 
                        .Replace What:=strMyChar, Replacement:=strMyReplace, _ 
                        SearchOrder:=xlByColumns, MatchCase:=True 
                    End With 
                     'next word/text to relace
                Next cel 
            Next ws 
             'close & save
            wb.Close True 
             'next workbook to do
        Next I 
    End Sub 
    
    
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  7. #7
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    Re: Multiple substitute or replace text using a table

    Thanks WillR! You do good work on beer. I'm going to have to rethink my state of inebriation while working.

    I gave you a simplistic version of the data and I should have provided more context. The file conversion project that I mentioned is fairly complicated. I'll be working on the files one at a time. The files are already open because I first need to review and often correct each source file because all too often the users have modified the original template. I then set off a macro that sucks a copy of the worksheet into the conversion workbook, then a cleaner tab parses text into useful tables, validates some data and reorganizes different types of data. It then goes through a pivot table which is used by the final tab to reorganize one dimensional data into a two dimensional grid and extract data out of the cleaner tab and other look-up tables. Finally it copies the converted worksheet to the original workbook, saves it under a new file name based on the contents of a cell and then deletes the old file. Whew, I may have gone from too little information to too much!

    To properly use your code, it would only need to work on a specified tab in the conversion workbook. That should be a simple modification, but as I've said, my VBA is weak.

    I have another limitation that didn't occur to me to mention earlier. There are cells in the worksheet that I don't want to convert and there are other cells that I want to convert only if they exceed a specified length. That is why I was thinking of using a function instead of a procedure.

    I used your code to create a function similar to what I was thinking about. I say similar, because it has two options that rely on VBA functions that do not exist. If there were a VBA equivalent to Excel's FIND or SUBSTITUTE functions, I think something like the following code could work:
    VB:
    Function SuperSub(strOldText As String, Rng1 As Range) 
         'strOldText is the string to be modified and
         'Rng1 are the strings in strOldText that get replaced.
        Dim cel As Range 
        Dim strMyChar As String, strMyReplace As String 
        Dim Place As Integer 
        For Each cel In Rng1.Cells 
            strMyChar = cel.Value 
            strMyReplace = cel.Offset(0, 1).Value 
             ' Next line does not work.
             'It would require a VBA version of Excel's SUBSTITUTE to work
            strOldText = Substitute(strOldText, strMyChar, strMyReplace) 
             ' [b][COLOR=Blue]or[/COLOR][/b] next 2 lines would work if there was a VBA version of Excel's FIND
            Place = Find(strMyChar, OldText) 
            OldText = Left(OldText, Place - 1) & strMyReplace _ 
            & Right(OldText, len(OldText)-(Place + Len(strMyChar))) 
        Next cel 
        SuperSub = OldText 
    End Function 
    
    
    Is beer powerful enough to help find an answer to this problem?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Multiple substitute or replace text using a table

    Are you sure you require a function on its own? I was under the impression that you wanted a procedure that acted on many cells in a workbook... however, for your information:

    There is a VBA Find function. It returns a Range object.

    You can also use Substitute via
    VB:
    application.WorksheetFunction.Substitute(...) 
    
    
    which takes the same arguments as the Excel Worksheet Function, namely,

    (text,old_text,new_text,instance_num)

    You may also want to check out VBA Instr function
    It returns a Variant (Long) specifying the position of the first occurrence of one string within another.

    Syntax

    InStr([start, ]string1, string2[, compare])

    Personally, I would check out all three in VBA help files first and take a look at the examples of how & when they can be used.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  9. #9
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    Re: Multiple substitute or replace text using a table

    Are you sure you require a function on its own?
    Unfortunately that requirement was lost in the early history of this thread. I talked about a function in my first post.

    You can also use Substitute via
    VB:
    application.WorksheetFunction.Substitute(...) 
    
    
    Now that's what I'm talking about! I didn't know that you could use worksheet functions in VBA. I may have VBA shortcomings, but I'm excellent with worksheet formulas. WillR, you just increased my VBA power tenfold.

    Adapting WillR's original code along with the new love of my life, application.WorksheetFunction, I came up with a function that works perfectly. It's better than nested substitutes because it provides an unlimited number of substitutes without the need to change the formula. You just add to the substitution list.

    Here's the syntax:
    SuperSub(Original_Text, Old_Text_Range)

    It requires a two column table, the first with Old Text and the second with New Text. Note, only the range of the Old Text is used in the function.

    Example:
    -----A ------B
    1 Old Text New Text
    2 steveorg WillR
    3 writes-- develops
    4 code---- programs


    =SuperSub("steveorg writes great code.",$A$2:$A$4) produces the same results as
    =Substitute(Substitute(Substitute("steveorg writes great code.","steveorg","WillR"),"writes","develops"),"code","programs")
    Results: WillR develops great programs.

    Here's the function code:
    VB:
    Function SuperSub(OriginalText As String, rngOldText As Range) 
        Dim cel As Range 
        Dim strOldText As String, strNewText As String 
         'loop through list of old_text used in substitute
        For Each cel In rngOldText.Cells 
            strOldText = cel.Value 
            strNewText = cel.Offset(0, 1).Value 
            OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText) 
        Next cel 
        SuperSub = OriginalText 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Multiple substitute or replace text using a table

    Excellent Steve, It's real nice to see you have managed to get the solution yourself

    FYI, Not all worksheet functions are available in VBA, but if you search in the VB Help, you will find that the ones that are are listed somewhere...
    Kind Regards, Will Riley

    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replace Part Text With Text From Cells
    By chrisrichter in forum Excel General
    Replies: 6
    Last Post: October 2nd, 2008, 22:23
  2. Import Multiple Text Files For Pivot Table
    By Bart2008 in forum Excel General
    Replies: 3
    Last Post: January 16th, 2008, 12:46
  3. Replace Text In Mutiple Text Boxes
    By Jeff P in forum Excel General
    Replies: 14
    Last Post: September 13th, 2007, 15:28
  4. Replace or Substitute inverted commas with nothing
    By Michael Avidan in forum Excel General
    Replies: 2
    Last Post: June 17th, 2006, 23:26

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
  •  
porno