How to use Text to Columns in Excel

How to use Text to Columns in Excel

The text to column function in Exel lets you “split” a column of organised data into multiple ones.

Let’s take the example of participating to a compensation survey. You need to provide employee name, employee title and employee date of birth. The date of birth should be in format YY, ie only 2 digits for the year of birth.However, your HR system stores full birth dates in the format DD/MM/YYYY.

The Text to Columns function will be helpful to avoid manually copying the birth year in the required format. Here is how to use it :

Select the column with the birth date issued from your system. Go to Data and select “Text to columns” (in Excel 2010). A window pops up.

In Step 1, select “Delimited – characters such as commas or tabs separate each field”. In our case, the character is the forward slash / in-between the day, month and year. Click Next.

In Step 2, you need to choose or define the delimiter. Default options are tab, semicolon, comma or space. In our case, select “other” and insert the forward slash / in the required space. You will notice that the display at the bottom of the screen immediately shows what the results will look like. In our case, 3 neat columns of information. You can scroll down your whole file in case you want to check if there is any anomaly. Click Next.

The final step lets you format your results as per Excel “style” for each column newly created : general, text, or date. It also allows you to select a column and “skip” it (ie do not import it). In our case, we will select the first column in the Data preview field, and choose skip as it contains the day of birth, not needed for our survey. Then select the second column and repeat as it contains month of birth which we don’t need for the survey. The third column is the birth of year, which is what we want. I usually keep the format to “general” as default. Click Finish.

Voila ! Your spreadsheet now contains one new column at the right of your original birthdate information (this original data may look changed depending on the format of your orginal Excel sheet, don’t worry about it as you don’t need that data any longer). Simply delete the old data and you’re done – you have a column with birth years only.

TIP : don’t  forget to use this function either at the far end of your spreadsheet, or to include columns after your original data, because Text to columns does not insert new columns but replaces their content with your results – you don’t want to over-write on other useful data in your report.

“Ah”, but you say, “Sandrine, remember, my birth year is formatted YYYY (eg 1971) while the survey provider requires YY (eg 71). How do I get there ?

Easy ! Let’s use Text to columns again. This time in Step 1, select “Fixed width – Fields are aligned in columns with spaces between each field”. Click Next.

In Step 2, you want to create your break line. In the Data preview field at the bottom of the wizard, simply click where you want to separate your column for example between 19 and 71. Click Next.

Step 3 is the same as above, format the columns as you wish then click Finish.

 

For those of you who learn better through visuals, I have created a Powerpoint overview of the steps to follow.

 



And  there you go ! In less than 2 minutes, by using the Text to Column function twice on your DD/MM/YYYY data, you have created the data in the YY format required for your survey. No lengthy manual data entry. No risk of typing mistake.

You can also use this tool for “splitting” employee ID numbers if they all have a similar composition (for example they all start with a 2-digit code for the year of hire), or for splitting job codes (for example if they all have a format like country – job family – function – level). It works for any kind of data which is structured in a consitent manner throughout.

Let me know if you found this useful !

Print Friendly, PDF & Email
Share the Knowledge !
Get Free Updates

Comments

  1. Fantastic wordpress web site right here.. It is tough to uncover high quality composing like yours as of late. I actually enjoy folks like you!

Trackbacks

  1. […] October 2, 2011 By Sandrine Leave a Comment In the previous instalment of Sunday Skills, I shared how to use Text to Columns in order to “split” columns of information. Today, let me demonstrate the […]

Speak Your Mind

*