Use of REPLACE as a shortcut (Ctrl+H), from the menus, and examples of a standard code macro and in a change event macro.

Replace Worksheet Function and Replace in VBA

Location: http://www.mvps.org/dmcritchie/excel/replace.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Ctrl+H Replace shortcut key (#ctrlh)

See problem definition on the right side -->

Select the range of cells to be changed

Use Ctrl+H  or the Edit (menu), Replace, then in the dialog

Find What: ~*
Replace With: (leave blank to remove string)
use button: Replace or use button: Replace All

Then repeat

Find What: !
Replace With: enter a space by hitting the spacebar
use button: Replace or use button: Replace All

    The problem is the remove the asterisks, and change the exclamation points to spaces.
 
 AB
 1  
 2 *My!Whole!Name*   
 3 *Ellie!May!Jones*  
   
 AB
 1  
 2 My Whole Name   
 3 Ellie May Jones  

Use tilde in front of certain characters (question, asterisk, tilde)
— see #wildcards for substitutions for   ?, or *, or ~

Buttons on Replace dialog:
Replace ALL will replace all occurences in all cells
Replace     without the all will restrict to all characters in the selected cells, one cell at a time with each invocation starting with the active cell.

SUBSTITUTE Worksheet Function (#substitute)

B2:  =SUBSTITUTE(SUBSTITUTE(A2,"*",""),"!"," ")

or the match the macros below replace with spaces and trim the result

B2:  =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"*"," "),"!"," ")

Application.SUBSTITUTE in VBA

Replace was introduced with Excel 2K and can be used in place of application.Substitute.

REPLACE in VBA (#vba)

In VBA prior to Excel 2K use application.Substitute.

   REPLACE used in a standard macro on Preselected Text Cells (#macro)

For assistance in installing the following standard code macro see Getting Started with Macros.

Speed and efficiency considerations can be seen in Proper, and other Text changes including use of SpecialCells and selections, more tips on speed can be found in in Slow Response.

Sub Remove_ast_exclaim()
   '-- a specialized example of no practical use but that you
   '  might modify to something of better use for a specific application.
   ' David McRitchie 2005-10-09 based on join.htm#trimall
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     Target.Value = Replace(Target.Value, "*", " ")
     Target.Value = Replace(Target.Value, "!", " ") 
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
Keep in mind thatthe above example here is a very specialized use where asterisks and exclamation points are eliminated from text constants (non formulas) and then trimmed.  You might want to look a more useful general purpose macro like the TrimALL Macro, will convert CHR(160) which in html is   (non-breaking space) and the remove excess spaces, but be sure that is what you want.  Removing and converting characters may make record unsuitable to a database that is used to having certain characters as filler.

The next example is a Change Event macro and because it would apply only to one worksheet a high degree of customization would be explected.

   REPLACE used in a CHANGE event macro (#changeevent)

This macro is a bit different from the original Ctrl+H example at the top of the page.  The asterisk and the exclamation points will both be changed to spaces and the result will be trimmed.  Worksheet Change Event macros are for the sheet they are installed for, so some specifics have been included with this macro in that it will bypass a change to a cell that has a formula, or that resides on row 1.  It will only process cells in column 1 as they are entered.

You can force an entry by hitting F2 then Enter.

Use of Fill-Down with Ctrl+D or with the fill handle will not constitute a change event.

Worksheet Event Macros are installed with the worksheet by right-clicking on the sheettab, choose 'view code', and then paste in your macro, unlike standard macros which are installed in standard modules,

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.row = 1 Then Exit Sub
  If Target.HasFormula Then Exit Sub
  On Error GoTo ErrHandler
  Application.EnableEvents = False
  If Target.Column = 1 Then
     Target.Value = Trim(Replace(Replace(Target.Value, "*", " "), "!", " "))
  End If
ErrHandler:
  Application.EnableEvents = True
End Sub

The following four topics were copied from my strings page on 2005-10-09 when this page was created.

Case sensitivity (#sensitivity)

In general Worksheet Functions are not case sensitive, and in VBA programming everything that is strictly VBA is case sensitive.

WS Functions:  to make something case sensitive as in Data Validation, change the “Allow” dropdown to Custom, then, assuming the active cell is A1, enter =Exact(A1,UPPER(A1)) to ensure uppercase, or =EXACT(A1,LOWER(A1)), to ensure lowercase, and click the “error alert” tab and create an appropriate error message.  As mentioned in the table at the top, FIND is case sensitive, and SEARCH is not.  SUBSTITUTE(value,to,from) is entirely case sensitive.  UPPER, LOWER, and PROPER are not case sensitive but will convert the letter case.

VBA: usage is case sensitive.  Application. -- invokes an Excel function so it will be case sensitive if the Excel function is case sensitive.  To make VBA not case sensitive you can use UCASE or LCASE as in IF UCASE(myvar) = "ABC" THEN ...
 
There are a few VB functions that can be made case insensitive with an operand (InStr, Sort, Replace, Find, Select)
  InStr(1, Range("b2"), "text")      as binary comparison (default is 0: vbBinaryCompare)
  InStr(1, Range("b2"), "Text", 1)   as textual comparison (1: vbTextCompare)

Replace: Replace(expression, find, replace[, start[, count[, compare]]])
Target.Value = Replace(Target.Value, "<u>", "", , vbTextCompare)

 Original Result toString  fromString
 abcdef AbCdEf ACE ace
 abcdefg aBcdefg aB Ab
 abcdefgh AbcDefgh DRATs drats
 Abcdefghijk Ayxwvughijk zyxwvu abcdef
   B2: =personal.xls!translate(A2,C2,D2)
User Defined Function to simulate REXX and PL/I Translate:  You would have to write your own function to simulate a very basic translate function (#translate)
  newString = translate(oldString, toString, fromString)

Wildcard Characters   (#wildcards   ?, or *, or ~)

The Search Worksheet Function is not case sensitive and allows wildcards, precede wildcard characters of ?, or *, or ~ with a ~ (tilde) to match actual character.  The FIND Worksheet Function (case sensitive) does not allow wildcards.   Wildcards: ? single character, * any number of characters. 

When using SEARCH to obtain True (found) or False (not found) it will be necessary to test for ISNUMBER to eliminate the return of "#VALUE!" if not found.
    =ISNUMBER(SEARCH("* anymatch *"," " & $A1 & " "))

Find (Ctrl+F) and Replace (Ctrl+H) shortcuts are not case sensitive and allow wildcards and overrides of wildcards.

Examples of ”wildcard” use can be found in SEARCH and FIND among some Conditional Formatting examples.

Don't confuse with ampersand(&) formatting text in headers and footers (to type a plain ampersand double them as in AT&&T.

Also read about Regular Expressions and LIKE (VBA Operator).

REPLACE   (#replace)

REPLACE Worksheet Formula

    SUBSTITUTE(text,  old_text,  new_text,  instance_num)

    =SUBSTITUTE(A1,  ", ",   ",")

VBA     for a range, several examples   (in VBA prior to Excel 2K use application.Substitute)

   Activesheet.Columns("A").Replace(What, Replacement, LookAt, _ 
        SearchOrder, MatchCase, MatchByte)   '--replace method
 
   rng.Replace what:= ", ", replacement:= ",", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False 
  
   Intersect(Selection, Selection.SpecialCells(xlConstants, _ 
       xlTextValues)).replace(", ",",")
More information or examples on this page(strings.htm), in proper.htm and in proper.htm in combination with your Excel HELP and your VBA HELP.

Material introduced in VB6 with Excel 2000 not available on Macs   (#vb6)

The following string functions are not available in VB5 use before Excel 2000 and are not available on MACS.

FunctionDescription
JoinUsed to join arrays elements.
SplitSplit a string into a variant array.
InStrRev  Similar to InStr but searches from end of string.
ReplaceTo find a particular string and replace it.
ReverseTo reverse a string.

MSKB 188007 -- How To Simulate Visual Basic 6.0 String Functions in VB5

If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

Bookmarklets;  The following BookMarklets (Favelets) will distinguish between internal or external links.  First click on "highlight links",  and then on "int/ext links".  Your browser's Reset button (F5) will reload the original unmodified web page. 
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on October 10, 2005. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2005,  F. David McRitchie,  All Rights Reserved