Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Multiple substitute or replace text using a table

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

    Comment


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

      Code:
      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, 06:41.
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: Multi-substitute using a table

        Thanks WillR. I've attached a spreadsheet with sample data.
        Attached Files

        Comment


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

          Comment


          • #6
            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...

            Code:
            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

            Comment


            • #7
              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:
              Code:
              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)
              ' or 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?

              Comment


              • #8
                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
                Code:
                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

                Comment


                • #9
                  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
                  Code:
                  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:
                  Code:
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Multiple substitute or replace text using a table

                      Ok, so I know this is a long shot, as this thread is almost 8 years old, however, the problem discussed here is EXACTLY what I'm currently running into. I'm using Office 2011 if that matters. I guess my question –forgive me for being na´ve– is basically, how do you actually implement the final code? I would assume creating a macro and entering VBA editor? Thanks

                      Comment


                      • #12
                        Re: Multiple substitute or replace text using a table

                        Hi drumtechjp

                        Welcome to the forum.
                        Your right it is a very old post .. If you start a new thread and reference this thread you will get a soultion to your problem..
                        If the solution helped please donate to RSPCA

                        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

                        Comment

                        Trending

                        Collapse

                        There are no results that meet this criteria.

                        Working...
                        X