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.


PROPER, LOWER, UPPER

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.

 


TRIM

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.

 


CONCATENATE

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.

 


RIGHT, LEFT, MID

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 Kaique Rocha from Pexels

 

Bitnami