Use of REPLACE as a shortcut (Ctrl+H), from the menus, and examples of a standard code macro and in a change event macro.
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
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.
B2: =SUBSTITUTE(SUBSTITUTE(A2,"*",""),"!"," ")
or the match the macros below replace with spaces and trim the result
B2: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"*"," "),"!"," ")
In VBA prior to Excel 2K use application.Substitute.
For assistance in installing the following standard code macro see Getting Started with Macros.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 SubKeep 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.
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
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)
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
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) (#translate)
newString = translate(oldString, toString, fromString)
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 Worksheet FormulaSUBSTITUTE(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.
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
If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2005, F. David McRitchie, All Rights Reserved