---

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

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.     A B  1      2  *My!Whole!Name*     3  *Ellie!May!Jones*         A B  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 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

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.

Function	Description
Join	Used to join arrays elements.
Split	Split a string into a variant array.
InStrRev	Similar to InStr but searches from end of string.
Replace	To find a particular string and replace it.
Reverse	To reverse a string.

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

Related   (#related)

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

• Strings and Manipulations
• Event Macros, Worksheet Events and Workbook Events
• TrimALL Macro, will convert CHR(160) which in html is &nbsp; (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.

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

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