How to keep Uniform Data in Ms.Excel Using Text to column function

Keeping uniform data not only makes your work tidy but also relieves you from the hustle of analyzing complicated data. A uniform data set is the one that has all cell values described by the column attributes and row cases. Such a structure ensures that data is easily understood and also guarantees you an easy time when analyzing it. Ms.Excel Text to column function provides a way of converting cell values into columns.

Case considered

Consider bank transactions recorded in an Ms. Excel spreadsheet as shown below. As a financial analyst, you want to know the amount transacted in a specific day, month and year. The easiest way is to convert the date column into days ,month and year using Text to Column function in Ms. Excel.

Text to column Procedure in Ms. Excel

In our case, this functionality separates into three columns that is the day month and year.

Assume the data above are bank transactions of a specific bank account. The main aim is to split date column into day, month and year. In the long run, this would help a bank owner know the amount transacted in a specific day, month and year. To achieve this,text to column excel function helps automatically obtain this result in the following manner.

  1. First, identify the common separator (Delimiter) that separates the days months and years. In this case, the hyphen is the common separator. Select the range of dates.

    2.  Navigate to data menu then text to column under the data tools in the ribbon. Click on it then a dialog box will pop-up                 similar to the one shown below.

3. Click on Next to choose the common separator in the dates.

4. Under the delimiters option select other and type hyphen then data preview will show up. Click on next to proceed and finish in the next dialog box.

The data is now separated into three columns which you can rename into day month and Year then proceed to filter based on the columns.

The beauty of the excel feature is that it can convert cell entries in different columns as long as there is a common separator. Even if there is no separator in data, by use of the DATE function one can separate the day months and year in different columns using the Text to Column feature. This would minimize data model time hence increasing productivity in your workplace.

To learn more on how you can separate dates with different delimiters join our upcoming Advanced Excel training.


It does not take long to get left behind

Stay relevant | Be informed | Sign up now and receive the latest tips, updates and industry insights...

You have Successfully Subscribed!