Expedite your data cleaning with these time saving Excel functions.
Microsoft Excel is typically one’s first introduction to data software. It’s a simple yet powerful enough tool that you can use for data entry, charts and graphs, and project management.
With the rising complexity and volume of data, there is a growing need for people who have proficiency in troubleshooting data cleaning.
I’ve learned that the process of data cleaning can be very subjective. Most commonly, there are a few core procedures you can use like: SORT, REMOVE, REPLACE, etc. For this reason, the order in which you make certain decisions depends on the data, the person cleaning the data, and the intended use of the end product.
This brings me to the problem I had recently. I had a list of email addresses that I needed to clean up. I needed to remove text within each cell containing an email address. Keep reading for the Excel functions I used to remove the characters.
Remove characters from the right of a string
To remove characters from the end of a cell, you can use the following formula:
=LEFT (string, LEN (string) – num_chars)
- LEFT – the function that refers to the number of characters you want to extract starting from the left side of a text string
- String – refers to blocks of text
- LEN – refers to the length of the string
- Num_chars – refers to the amount of characters you want to remove
In the example below, I needed to remove the phrase “(No Name Available)” from each cell. This phrase, with spaces and parentheses included, totaled 20 characters. There will be 20 characters will be subtracted from the total length of the strength. The difference will be applied to the LEFT function in order to extract that many characters from the start of the string.
To remove the last 20 characters, I used 20 for num_chars.
=LEFT (A2, LEN(A2) – 20)
Remove characters from the left of a string
To remove characters from the beginning of a string, you can use the following formula:
=RIGHT (string, LEN (string) – num_chars)
- RIGHT – the function that returns a number of characters from the rightmost side of a string
- String – refers to blocks of text
- LEN – refers to the length of the string
- Num_chars – refers to the amount of characters you want to remove
Next we are using a combination of functions, RIGHT and LEN. I provided a few examples with different number of characters to remove. The desired amount of characters you plan to remove will be extracted from the total length of the string. Finally the difference will be applied to the RIGHT function in order to extract that many characters from the end of the string.
In keeping with the first example, to remove the first 20 characters, I used 20 for num_chars.
=RIGHT(A2, LEN(A2) – 20)
Related: What Is Data And Why Is It Important?
That’s how I removed text from the right or left in Excel. I hope this helps!
Live abundantly,
Bethany
