Excel at hyphen removal

BY J. CARLTON COLLINS

Q: I have an Excel worksheet containing hundreds of Social Security numbers, and I want to remove the hyphens. Can you tell me how this can be done?

A: I can think of two ways to remove hyphens from Social Security numbers in Excel.

Find and Replace method. Select the data containing the Social Security numbers, then from the Home tab’s Editing group, click on Find & Select and select Replace. Enter a hyphen in the Find what text box and leave the Replace with text box blank. Click the Replace All button. ( Note: It is possible that your Social Security numbers don’t contain hyphens. Instead they may contain “em” or “en” dashes, but most computer keyboards (especially on laptops) lack those symbols. If this is the case, highlight a cell containing a Social Security number, press the F2 key to enter edit mode, then highlight one of the em or en dashes and press Ctrl+C to copy. Then, when using the Find and Replace method described above, alter the instructions as follows: Instead of entering a hyphen, select the Find what text box and press Ctrl+V to paste an em or en dash into that field.)

Formula methods. You can remove unwanted hyphens using the Substitute function, or a combination of the Left, Mid, and Right functions; both solutions are illustrated in column C in the screenshot below.

(If necessary, use the trick described in option 1 above to copy and paste em or en dashes into your Excel formulas as needed.)

SPONSORED REPORT

Questions to ask before committing to the cloud

Cloud computing has its pros and cons. In this report, we answer common questions CPAs may have as they consider transitioning partially or fully to the cloud.

QUIZ

News quiz: IRS reopens an online service, but criticism endures

The IRS brings back the Get Transcript Online service, but the agency faces criticism for its handling of the aftermath of the event that led to the shutdown of the service. See how much you know about other recent news with this quiz.

CHECKLIST

Auditing risks in culture

Cultural flaws can seriously damage an organization. Here’s how internal auditors can reduce risks by embedding culture audits into existing audit programs.