New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Replace or Remove Part of a Text String

Free Excel Macros

Replace or Remove Part of a Text String

Postby Sisyphus » Mon Apr 16, 2012 2:29 am

Sometimes you need to replace all commas in a text with semicolons or all $-signs with €-signs, "last" with "previous" or anything with nothing. In such cases the following procedure comes in handy:
Code: Select all
Private Function ReplaceAll(ByVal Op As String, _
                            ByVal Ex As String, _
                            Optional ByVal Rp As String) _
                            As String
    '' 0070 V 1.0
   
    ' ==================================================
    '   Parameters:
    '       Op              = Original string to be operated on
    '       Ex              = Exclude by replacing or removing
    '       Rp              = String replacing Ex
    '                         [if not supplied, Ex is removed]
    ' ==================================================
   
    Dim Lft As String                               ' Left String (= Result)
    Dim n As Integer                                ' = Start of Ex
   
    ' ==================================================
   
    Do
        n = InStr(Op, Ex)
        If n Then
            Lft = Lft & Left(Op, n - 1)
            Op = Mid(Op, n + Len(Ex))
            ' don't add a space before a space or before the end:
            Lft = Lft & IIf(Rp = " " And (Left(Op, 1) = " " Or _
                                        Len(Op) = 0), "", Rp)
        End If
    Loop While n > 0
   
    ReplaceAll = Lft & Op
End Function
The logic is very simple.
  • You pass a string in which to do the replacing and receive in return a string in which replacements have been made.
  • As a second parameter you pass the string to be replaced. That could be a single character or a string of characters of any length.
  • The third parameter is the replacement string. This argument is optional. If you don't supply it the string to be replaced will be replaced with nothing, meaning it will be removed.
This is a typical line of code calling the above procedure. It would remove all commas. This is useful for building validation lists which require to use the comma as a list separator and can't, therefore, retain any commas within the list's items.
Code: Select all
MyString = ReplaceAll(MyString, ",")

All occurrences of the string to be replaced will be found, not only the first one. If the removal of a string leads to two blank spaces following each other the second or final blank will also be removed.

This procedure is particularly useful if called from a loop that examines each cell in a range of cells such as this one:
Code: Select all
    Dim R As Long

    For R = 1 To LastRow
        With Cells(R, "A")
            .Value = ReplaceAll(.Value, "my", "your")
        End With
    Next R

The function LastRow can be copied from this forum:
http://www.excelkey.com/forum/viewtopic.php?f=5&t=393

When deploying the above procedure remember to change its scope to 'Public' if you call it from another worksheet.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 11 guests