25Jun2015

Text to Columns in Excel

Text to Columns in Excel are used to split a single column of text into multiple columns. This feature is helpful when we import data from word, PDF or any other source. As the imported data may not be usable right away. We can split the text based on fixed width or split it at each comma, or at period or at any other character. For example, assume we have a list of data with Names and their Designation as shown in the image below.

text to columns in excel 2

We are interested in splitting the data into First name, Last name and Designation. This blog explains how to split text to columns in excel.

Step 1: Select data

Once we have the data, select the range.

Step 2: Split text to columns

After selecting the range, go to Text-to-columns. This function is available in Date Tools section in Data tab in Excel ribbon as shown in the image below.

text to columns in excel

Now, we get a pop up window which is a 3-step wizard. In first step, we select ‘Delimited’ as our text is not having fixed width. After selecting the option, click Next.

text to columns in excel 3

Step 2 – In wizard, we select Delimiters as per our requirement. In this example, we selected Space and Other (Enter dash in other field) as Delimiters as shown in the image below. Data Preview in the wizard window will show how the text will be split based on the delimiters selected. Now, click Next.

text to columns in excel 4

 

Step 3 – In wizard, we can set the data format for each column. In this example, we selected ‘General’ as data format. The destination is cell ‘$A$1′, we can assign the cell address based on our requirement. Now click ‘Finish’.

Note that if destination is not specified then the data which has been split will replace the original data.

text to columns in excel 5

 

As we can see from the image below, the text has been split to columns.

text to columns in excel 6

This is how we use Text to Columns in Excel. If you are interested in splitting the text in VBA then, go to the blog over here. Have any questions? Feel free to contact us here.

  • 25 Jun, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, Text to Columns,

Categories

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>