Substitute()

Substitute() Function

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,”-“,”“)

Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: