Sometimes, when you're reading data in a spreadsheet like Microsoft's Excel, it's simpler to put a formula directly into the spreadsheet than to go and process the information again via R, Python or any other solution.
The data "clean-up" I propose in this article will enable you to remove characters that don't interest you, such as "stop words" (hyphens, dots, spaces...); but it will also bring better harmonization.
Let's discover together how to substitute characters in Microsoft Excel ... and even delete them automatically.
Tempted? Let's go !
Deleting data: deleting the first characters of a cell
The formula you're waiting for to delete the last X characters of a cell in Excel is :
=LEFT(A;NBCAR(A2)-X
Where LEFT gives the starting point; where 1-character example, it's quite simple =LEFT(A2;NBCAR(A2)-1
For a 19-character example, it's on the same principle (the formula being fairly simple to understand): =LEFT(A2;NBCAR(A2)-19)
Note: this can be useful when sorting data, but also when formatting URLs for SEO or additional information.
Deleting data: deleting the last characters of a cell
If you've understood the principle for the end of the cell, it's exactly the same for the beginning of the Excel cell; except that you use RIGHT instead of LEFT
=RIGHT(A2;NBCAR(A2)-X
Note: you can replace
RIGHTwithLEFTor vice versa as you see!
Deleting data: search/replace
Sometimes it's easier to duplicate a column and perform a "simple" search + replace. To delete, you can search for the character(s) you want to remove from the cells and then replace them with nothing, or put a space in the replace field.
Remove excess spaces in your cell
Your Excel cell can sometimes be badly encoded, depending on the source you've retrieved ... and so sometimes there can be certain spaces that prevent you from working optimally! The formula you could use is: =SUPPRESPACE()
Example with text from =SUPPRESPACE("Best formula for deleting spaces") which becomes "Best formula for deleting spaces" ... still better than doing it cell by cell, isn't it?
For one cell it's quicker to do it manually, but when you have more than 5 ... it becomes too much for me!
And best of all, since it's a formula, you can copy it and adapt it to a cell:
For A2="Best formula for deleting spaces"; the formula =SUPPRESPACE(A2) will give the same result "Best formula for deleting spaces".
Deleting characters by substituting them
This formula can be used to delete characters such as double characters or spaces, as seen in the previous paragraph. But it can also be used to replace some characters with others.
Example with extra spaces (like deleting characters): =SUBSTITUE("The best... ... thing is ..SEO";". . ";" ") which will transform the sentence into The best thing is SEO.
Examples with a character (I'm going from lowercase to uppercase on SEO ): =SUBSTITUE("The best thing is seo"; "seo"; "SEO") which will transform the sentence into The best thing is SEO.
Example with a word to replace: =SUBSTITUE("The best thing is SEO"; "the SEO"; "the Data") which will transform the sentence into The best thing is Data
Please note that formulas are case sensitive.
This article is part of the data analysis theme, but can be used at different levels; SEO as we've seen, but sometimes text formatting or more complex calculation tables.