Clickbank Ad Rotator
Home / Digital & Internet News / Common Data Cleanup Formulas in Excel

Common Data Cleanup Formulas in Excel

For years, I’ve used the publication as a resource to not just describe how to do things, but to also keep a record for myself to look up later! Today, we had a client that handed us a customer data file that was a disaster. Virtually every field was misformatted and; as a result, we were unable to import the data. While there are some great add-ons for Excel to do the cleanup using Visual Basic, we run Office for Mac which won’t support macros. Instead, we look for straight formulas to assist. I thought I’d share some of those here just so others can use them.

Remove Non-Numeric Characters

Systems often require phone numbers to be inserted in a specific, 11-digit formula with the country code and no punctuation. However, folks often enter this data with dashes and periods instead. Here’s a great formula for removing all non-numeric characters in Excel. The formula reviews the data in cell A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Now you can copy the resulting column and use Edit > Paste Values to write over the data with the properly formatted result.

Evaluate Multiple Fields with an OR

We often purge incomplete records from an import. Users don’t realize that you don’t always have to write complex hierarchical formulas and that you can write an OR statement instead. In this example below, I want to check A2, B2, C2, D2, or E2 for missing data. If any data is missing, I’m going to return a 0, otherwise a 1. That will allow me to sort order the data and delete the records that are incomplete.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Trim and Concatenate Fields

If your data has a First and Last Name fields, but your import has a full name field, you can concatenate the fields together neatly using the built in Excel Function Concatenate, but be sure to use TRIM to remove any empty spaces before or after the text. We wrap the entire field with TRIM in the event that one of the fields doesn’t have data:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Check for Valid Email Address

A pretty simple formula that looks for both the @ and . in an email address:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Extract First and Last Names

Sometimes, the problem is the opposite. Your data has a full name field but you need to parse out the first and last names. These formulas look for the space between the first and last name and grab text where necessary. Here’s the first name:

=LEFT(A1, SEARCH(" ",A1,1))

And the last name:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

Limit the Number of Characters and Add …

Did you ever want to clean up your meta descriptions? If you wanted to pull content into Excel and then trim the content for use in a Meta Description field (150 to 160 characters), you can do that using this formula from My Spot. It cleanly breaks the description at a space and then adds the …:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Of course, these aren’t meant to be comprehensive… just some quick formulae to help you get a jump start! What other formulas do you find yourself using? Add them in the comments and I’ll give you credit as I update this article.


© 2016 DK New Media.

Click Here For Original Source Of The Article

Check Also

360 Stories: Create, Edit and Publish Your 360˚ Video Campaigns

360 Stories: Create, Edit and Publish Your 360˚ Video Campaigns

Skydiving 360 Video

Facebook partnered with Blend Media earlier this year to help bring more 360˚ video content to the platform, also launching a community hub for 36 video creators. The community offers access to video tutorials sourced from the worldwide creative community. Experienced professionals will offer tips including how to create 360˚ video when using a selfie stick, working in low-light conditions with different 360˚ cameras and image stabilisation. A list upcoming of upcoming creator workshops, together with re-caps from previous events. Hundreds of creators attended workshops hosted by Blend Media and Facebook in cities around the world during 2017. Professional creatives


© 2017 DK New Media. All Rights Reserved. Visit and Subscribe to MarTech today!

Leave a Reply

Your email address will not be published. Required fields are marked *

css.php