Part IV: Managing and Analyzing Data
In other situations, you may need a formula-based solution. Consider the data shown in
Figure 25.26. The goal is to replace the second hyphen character with a colon for the part
numbers in Column A. Using Find and Replace wouldn’t work because there isn’t a way to
specify that only the second hyphen should be replaced.FIGURE 25.26
To replace only the second hyphen in these cells, Find and Replace is not an option.In this case, the solution is a fairly simple formula that replaces the second occurrence of a
hyphen with a colon:=SUBSTITUTE(A2,"-",":",2)To remove the second occurrence of a hyphen, just omit the third argument for the
SUBSTITUTE function:
=SUBSTITUTE(A2,"-",,2)This is another example where Flash Fill can also do the job.If you’ve worked with programming languages, you may be familiar with the concept of regular expressions. A regular
expression is a way to match strings of text using concise (and often confusing) codes. Excel doesn’t support regular
expressions, but if you search the Web, you’ll find ways to incorporate regular expressions in VBA, plus a few add-ins
that provide this feature in the workbook environment.Adding text to cells
If you need to add text to a cell, one solution is to use a new column of formulas. Here are
some examples:■ (^) The following formula adds ID: and a space to the beginning of a cell:
="ID: "&A2