Text to columns

Hello Nimali,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding using the Text to Columns data tool.

There are 2 options which can be used with the Text to Columns tool. One is 'Delimited' and the other is 'Fixed Width'. Delimited is used when the data is of different lengths and is composed of only 2 sets of characters in a cell whereas Fixed Width data must be of equal lengths and can include several sets within a cell or one long data set.

I have attached a file with some examples.

Let's say you have a column of peoples' names, first name and last name only. Insert a new column to the right of the name column and select all the cells with the names. Then go to the Data Tools group on the Data ribbon and click the Text to Columns button. Make sure that you select 'Delimited' and click Next. Here you need to choose the character which separates the first name from the last name (usually this will be Space). After selection click Next and here you can choose the data type for your data. If you are working with text data you don't have to do anything... simply click Finish.

The Text to Columns tool will have split your data into 2 columns.

To use the Fixed Width option your data must be of equal length and the steps are similar to those for Delimited... just follow the guidance shown in each step.

If you find that neither Delimited or Fixed Width are providing you with satisfactory results send a copy of your file to me and I will find another way to do this using formulas. My email address is:

rl@stl-training.co.uk


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer