How many times have you downloaded a data set off the server, and loaded it up in Excel only to find that it’s not in the right format or you can’t edit it? No worries, there are ways to quickly sanitize the data!
Discover these 5 Excel formulas and save yourself a bunch of time next time you work with text data.
Text Capitalization
You have downloaded a list of countries, unfortunately, the data is in the wrong format. It’s all in lower cases! Don’t worry, you don’t need to import the text into a word editor. Here are three formulas that can help you apply the appropriate capitalization to text. This works on sentences too.
- =PROPER(cell reference) – Capitalizes the first character of each word
- =LOWER(cell reference) – Applies all lowercase letters
- =UPPER(cell reference) – Applies all uppercase letters
Remove Blank Spaces
When you copy data from other sources like a website or PDF files, you are likely to import blank spaces in each cell. You can use =TRIM() to help clean up each cell.
Related article – Why Is TRIM Not Removing Spaces In Excel?
Extract Parts Of A Phone Number
You’ve been sent a list of phone numbers but you need to work out the area code of each. You can easily extract this information using the =LEFT() function. You can also try these functions:
- =LEFT(cell reference of text, number of characters) – from the leftmost character
- =MID(cell reference of text, starting position, number of characters) – from the starting position you select
- =RIGHT(cell reference of text, number of characters) – from the rightmost character
Interesting read – What Is Index Match? A Simple Step By Step Guide
Determine The Data Type Of Cell Contents
Sometimes when you receive data, it may look like a string of text or look like spaces, but for some odd reason, your formula just won’t work! There are two functions that can help with this:
- =CODE() – This returns the numeric code for the first character in a string. In Windows, it uses the ANSI character set and Macintosh character set for Macs. For example, CODE(“a”) returns the code 97. This is helpful when you want to check if the sentence is actually being treated in a format recognizable by Excel.
- =TYPE() – This returns the data type of a cell. For example, you might have a string of numbers but Excel is actually picking it up as text. Below is the list of various TYPE return values.
| If value is | TYPE returns |
|---|---|
| Number | 1 |
| Text | 2 |
| Logical value | 4 |
| Error value | 16 |
| Array | 64 |
| Compound data | 128 |
Count The Number Of Words In A Cell
Every once in a while, there will be a curve ball request like counting the number of words in a sentence. Sure you could this in Word (I actually don’t know how!), but never fear. Excel is here to help!
- The formula is =LEN(TRIM(cell reference))- LEN(SUBSTITUTE(TRIM(cell reference),” “,””))+1
The formula counts the number of spaces in a cell, with the +1 covering the first word.
Not your everyday request, but a good one to keep in your back pocket in case it ever comes up!
Interesting read – Copy Paste Like A Pro In Excel
Handling Errors
Last but not least, handling errors in large datasets. You have started applying some of the formulas above to datasets that have hundreds of records, only to find that row 83 is blank. There are a couple of ways to help you handle these errors.
- =IFERROR(cell reference, return this value if error) – The all-encapsulating error checker. If your cell is any form of error, it will return the value you have specified. Personally, I prefer to use specific checks to determine issues (e.g. is it a blank cell) – but I do use this on occasion.
- =ISERROR(cell reference) – This checks if the cell reference is an error and will return TRUE if it is an error. You can pair this with an IF() function (i.e very similar to IFERROR) – you needed to do this prior to IFERROR being introduced to Excel 2007! You can also apply ISBLANK, ISNA, and ISNONTEXT in a similar manner.
Thanks for reading and I hope you found this helpful!
Other Excel tutorials you may also like: