Excel Functions for Text Cleansing

Microsoft Excel should be considered as part of your analysis toolkit. Using formulas and functions makes ad hoc analysis and data cleansing quick and easy.


To change the case of a cell using one function, insert a new column to the right of your data and use the functions below. Don’t forget to copy and ‘Paste Values’ to make the values remain.


Sometimes when importing data, extra spaces can be added during the process before or after the data. To remove these, we use the TRIM function.


This function is useful for putting a First Name and Last Name field back together with one step. This pastes B2 and A2 together in one cell with one space in between.


These three functions are used to pull out certain parts of the cell without having to do this one by one.

LEFT finds the cell entered and returns the number of characters to the left, in the example this would be four characters.

Remember if you haven’t used the TRIM first, any trailing spaces will be included.

Big and small businesses use Excel because it’s easy to learn and allow analysts and stakeholders to speak the same language.

If more sophisticated add-ons are required (Power BI, Power Pivot, Power Maps) these are open source and easy to use.

Excel offers functionality and plenty of bang for your buck with a usable interface and plenty of add-ons for scalability.

Photo by 𝐕𝐞𝐧𝐮𝐬 𝐇𝐃 𝐌𝐚𝐤𝐞- 𝐮𝐩 & 𝐏𝐞𝐫𝐟𝐮𝐦𝐞 from Pexels