Substitute()

Substitute Function

When we (collectively, I mean companies, in general) build user Internet forms, why don’t we always create validation rules for fields?  I don’t know- probably never will- but we (Finance, Sales, HR, Operations) are often left to work with it.

So, what do you do when some phone numbers come in as (888) 555-1234 and others as 888-555-1234?  Or when you get some Social Security Numbers in the form 123-45-6789, while other eliminate the dashes?  You probably just leave and do your best to work around it.  But, if you want to work with it (etc. mail merge) or confirm that you have accurate (or at least valid in length) numbers, here’s a good way.  The substitute formula.

It’s very simple: simply tell Excel which cell (b3) needs to be cleaned up, which character you are looking to replace (“-“), and with what character (“”) you’d like to replace it.

=SUBSTITUTE(B3,”-“,”“)