10 Examples of Text to Columns || How to Split Cells/Columns in Excel

10 Examples of Text to Columns || How to Split Cells/Columns in Excel

By

Using Excel Text to Columns Wizard to separate Delimited text [the text has some characters or delimiter, such as comma (‘,), tab, underscore (‘_‘), hyphen (‘‘), at the rate (‘@‘), space (‘ ‘), etc.] into the multiple columns.

Convert Text to Columns can work for a single column only at a time, it does not work with multiple columns. 

In advanced Excel, this feature is basically used to convert ‘invalid date formats to a valid date format‘, convert ‘number to text‘ format and ‘text to number‘ format, split a ‘delimited text into multiple columns‘, etc.

There are two separate features of Convert Text to Columns:

(1) Delimited:

This feature splits the text which is being joined by characters, Commas, Tabs, Spaces, Semicolons, Colons, or any other character such as a hyphen (-), underscore ( _), slash ( / ), etc.

(2) Fixed Width:

This feature splits the text having a fixed width (i.e., count of characters in the text remain same in each cell) which is being joined with spaces or hyphen or underscore or slash after some fixed width.


I. Steps to Start Convert Text to Columns Wizard

 

• Step-1: Select the data range, i.e., A2:A14.

• Step-2: Using the Ribbon

After selection, go to the ‘Data‘ tab ➪ click on ‘Text to Columns‘ under the Data Tools section ➪ ‘Convert Text to Columns Wizard‘ appears

Alternatively, Using the Excel Shortcut: 

We can use the Excel shortcut either Alt+D+E or Alt+A+E to open the ‘Convert Text to Columns Wizard’.

Text to column (Steps to Start)

II. Examples of Convert Text to Columns Wizard

Here we explain the usage of Text to Columns in advance excel with 10 different kinds of examples: 

ž

žžžž■ Example-1: Split Names in the First Name & Last Name (having delimiter Comma ‘,’). Then Split Country Name (having delimiter at the rate ‘@’ and space ‘ ‘)

Text to Columns (Split Names and Country Names)-1

 

Here are the steps to split names into the first name and last name:

• Select the data range (A2:A14) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on the keyboard. This will open the ‘Convert Text to Columns Wizard’.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to Columns (Split Names and Country Names)-2

 

• In Step 2, select the checkbox of ‘Comma‘, ‘Space‘ and ‘Other‘ [enter @ in the box to the right of it] as the delimiter. If there are double/triple consecutive spaces between the names, also select ‘Treat consecutive delimiters as one option. Click on Next or press Enter.

Text to Columns (Split Names and Country Names)-3

• Note: We have noticed that there are extra spaces both before and after the ‘@’. If ‘Space’ checkbox is unchecked then the spaces still persist in both before and after the ‘@’. We see the difference between the above and below.  

Text to Columns (Split Names and Country Names)-4

• In Step 3, select the destination cell (e.g., here select B1). If we don’t select a destination cell, it would overwrite our existing database with the first name in the first column (column A), last name in the adjacent column (column B) and country name to another column adjacent to the last name column (column C). If we want to keep our original data intact, we should create a copy of the range to another place or choose a different destination cell.

Text to Columns (Split Names and Country Names)-5

 

• Click on ‘Finish‘ or press Enter on the keyboard.

This would instantly give the results with the first name in one column, last name in another column and country name at the very adjacent column.

Text to Columns (Split Names and Country Names)-6

■ Example 2: Split Date & Time (having delimiter Space ‘ ‘)

Text to column (Split Date and Time having delimiter space)-1

Here are the steps to split the date and time having delimiter space ‘ ‘:

• Select the data range (A2:A14) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on the keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to column (Split Date and Time having delimiter space)-2

• In Step 2, select the only checkbox ‘Space‘. As mentioned above, if there are multiple consecutive spaces as a delimiter, also select ‘Treat consecutive delimiters as one option. Click on Next or press Enter.

• Make sure that other checkboxes should be unchecked (if any).

Text to column (Split Date and Time having delimiter space)-3

 

• In Step 3, select the new destination cell (e.g., Here select B1). If we don’t select a new destination cell, it would overwrite the existing database – such as the date in the first column (column A) and time in the adjacent column (column B). If we want to keep our original data intact, we should create a copy of the range to another place or choose a different destination cell.

Text to column (Split Date and Time having delimiter space)-4

• Click on ‘Finish‘ or press Enter on the keyboard.

This would instantly give the results with the date in one column and time in another column. Arrange the subject heading accordingly.

Text to column (Split Date and Time having delimiter space)-5

A. Formatting of dates in a valid format:

For data preparation, we should need to arrange the date format to valid date format in Excel such as ‘dd-mmm-yy‘ (or any other format).

Text to Columns (Formatting of dates in a valid format)-1

We can do this in three ways:

(01) Using the Excel Shortcut: Select the date range B2:B14 and then press Ctrl+Shit+#. All are arranged in a valid date format. 

Text to Columns (Formatting of dates in a valid format)-2

(02) Using the Format Cells by Excel Shortcut: Alternatively, select the data range B2:B14 ➪ then press Ctrl+1 to open ‘Format Cells‘ Wizard ➪ go the ‘Customoption under the ‘Number‘ tab ➪ On the right side, write require date format like ‘dd-mmm-yy‘ under ‘Type‘ section.

Text to Columns (Formatting of dates in a valid format)-3

(03) Using the Format Cells by Manual: Alternatively, select the data range B2:B14 ➪ go to Home tab ➪ Click on the right side corner of ‘Alignment‘ Settings or ‘Number‘ settings ➪ open the ‘Format Cells‘ Wizard ➪ ‘Customoption under the ‘Number‘ tab ➪ On the right side, write the valid date format like ‘dd-mmm-yy‘ under ‘Type‘ section.

Text to Columns (Formatting of dates in a valid format)-4
Text to Columns (Formatting of dates in a valid format)-5

Finally, press OK or press Enter on the keyboard. We get the result in the desired format as shown as below:

Text to Columns (Formatting of dates in a valid format)-6

Read-More-6

04 Alternative Ways: How to Change Valid Excel Date Format?

 

B. How to identify & Extract Date from Timestamp Excel?

Extract Date from Timestamp Excel – a big problem to identify those dates and how to extract them?

Cells having ‘Date with time‘ and ‘Date without time‘ – it does matter in a sense, but it really matters in data analysis, MIS preparation when we compare with the two dates.  

Suppose, we have a large master data with some activity dates and want to recheck these dates from another file for billing purposes, both dates are not matched to each other even they are same dates and it was then painful for anyone.

There are 4 methods to extract date from timestamp Excel:

(01) By using the Text to Columns;

(02) By using the INT () function

(03) By using the TRUNC () function

(04) By using the CONCATENATE () function 


■ Example 3: Convert Invalid Date Formats to Valid Date Formats

 

Sometimes we get the lists of invalid date formats during MIS preparation and always we would like to convert these invalid dates to valid date formats by using the following steps:

• Select the data range (A2:A11) or the entire column (Column A)

• Use the Excel shortcut Alt+D+E or Alt+A+E from the keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select ‘Delimited‘ (default option) and then click on Next or press Enter.

Text to Columns (Convert Invalid Date Formats to Valid Date Formats)-1

• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click on Next or press Enter.

Text to Columns (Convert Invalid Date Formats to Valid Date Formats)-2

 

• In Step 3, select the new destination cell (e.g., here selects B2) to avoid overwriting the existing data set.

Text to Columns (Convert Invalid Date Formats to Valid Date Formats)-3

• Click on ‘Finish‘ or press Enter on the keyboard.

This would instantly convert the invalid date format to a valid date format.

Text to Columns (Convert Invalid Date Formats to Valid Date Formats)-4


■ Example 4: Split Dates in the Days, Months & Years (having delimiter Hyphen ‘-‘)

 

Sometimes we require to split the dates into the days, months and years separately. We can do this using the following steps: 

• Select the data range (A2:A14) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on the keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to Columns (Split dates in the days, months and years having delimiter hyphen)-1

• In Step 2, select the only checkbox ‘Other‘ and enter a hyphen () in the box to the right of it. Please note that the hyphen is used here as the delimiter. Click on Next or press Enter.

• Make sure that other checkboxes should be unchecked (if any).

Text to Columns (Split dates in the days, months and years having delimiter hyphen)-2

• In Step 3, select the destination cell (i.e., B1) to avoid the overwriting of existing data.

• Click on ‘Finish‘ or press Enter on the keyboard.

Text to Columns (Split dates in the days, months and years having delimiter hyphen)-3

• A Microsoft Excel Warning dialogue box appears and asking for want to replace the existing data. Click on OK or press Enter to proceed.

Text to Columns (Split dates in the days, months and years having delimiter hyphen)-4

This would instantly split the days, months and years into different columns. Arrange the subject heading accordingly.

Text to Columns (Split dates in the days, months and years having delimiter hyphen)-5


■ Example 5: Join Days, Months & Years to form valid Date Formats (using delimiter Slash ‘/’)

 

It is a very common problem found in Data preparation how making a valid date format by joining the day, month and year respectively. We can do with the following steps:

(01) Use CONCATENATE() function to join the DAYS, MONTHS and YEARS with delimiter slash ‘/’.  

(02) Go toPaste Specialto convert the formulas into values.

(03) Finally, using the ‘Convert Text to Columns wizard to convert invalid date formats to valid date formats.

 

(01) Use CONCATENATE() function to join the DAYS, MONTHS and YEARS with delimiter slash ‘/’

• In step 1, we use the CONCATENATE function to join the dates, months and years.

• Using equality ‘= ‘sign in cell D2 and just type a few characters ‘con…..‘. Then press the ‘Tab‘ key on the keyboard to select the syntax from the suggestion list and automatically appears ‘CONCATENATE‘ syntax with open parenthesis. 

Text to column(Join days, months and years to form valid date formats)-1

• We should take care of joining the criteria

(i) First select the cell with a day (i.e., C2) inside CONCATENATE function and put a comma (,) to close the text1.

(ii) Use the delimiter slash (/) inside the double inverted comma and put a comma (,) to close the text2.

(iii) Then select the cell with a month (i.e., B2) and put a comma (,) to close the text3.

(iv) Use the delimiter slash (/) inside the double inverted comma and put a comma (,) to close the text4.

(v) Finally, select the cell with a year (i.e., A2) and press ‘Enter’.

Text to column(Join days, months and years to form valid date formats)-2

• So the complete formula is =CONCATENATE(C2,’/’, B2,’/’, A2) and get the output. Copy the formula till the end of the range.

Text to column(Join days, months and years to form valid date formats)-3

 

(02) Go to ‘Paste Special‘ to convert the formulas into values

• Select the range (D2:D14) and then copy (Ctrl+C).

• Remember that we should try to avoid the normal paste (Ctrl+V) in MIS preparation, rather we would like to use ‘Values and number formats‘ (Alt+Ctrl+V+U or Alt+E+S+U) or ‘Values‘ (Alt+Ctrl+V+V or Alt+E+S+V) under Paste Special wizard.

• The reason behind this is to keep our cell formatting (like font, font size, font color, text color, etc.) as it is as already have previously.  

• Then click on OK or Enter.

• The entire range would instantly convert from formulas to values.

Text to column(Join days, months and years to form valid date formats)-4

 

(03) Using the ‘Convert Text to Columns wizard‘ to convert invalid date formats to valid date formats

  • Select the data range (D2:D14) or the entire column (Column D).
  • Then use Excel shortcut Alt+D+E or Alt+A+E from the keyboard. This will open the Convert Text to Columns Wizard.
  • In Step 1, we select Delimited (default option) and then click on Next or press Enter.
Text to column(Join days, months and years to form valid date formats)-5

• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click on Next or press Enter.

Text to column(Join days, months and years to form valid date formats)-6

• In Step 3, select the destination cell (e.g., here selects E2) to avoid the overwriting of existing data. 

• Then select the Date checkbox.

Text to column(Join days, months and years to form valid date formats)-7

• Click on ‘Finish‘ or press Enter on the keyboard.

This would instantly convert invalid date formats to valid date formats.

Text to column(Join days, months and years to form valid date formats)-8


■ Example 6: Split text into Years, Host Names & Winner Team (having delimiter Hyphen ‘-‘ & underscore ‘_’)

 

Both the delimiters hyphen ‘-‘ and underscore ‘_’ didn’t use at a time in Convert Text to Columns Wizard. Because these delimiters are used in ‘Other‘ box, but the Other box only allows single delimiter at a time. So in that case, we should apply the ‘Convert Text to Columns Wizard’ twice to split the delimiters.     

• Select the data range (A3:A13) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on the keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-1

 

• In Step 2, select the only checkbox ‘Other’ and enter an underscore_ in the box to the right of it. Please note that the underscore is used here as the delimiter. Click on Next or press Enter.

• Make sure that other checkboxes should be unchecked (if any).

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-2
  • In Step 3, select the destination cell (e.g., here selects B2) to avoid the overwriting of existing data. 
  • Click on ‘Finish‘ or press Enter on the keyboard.

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-3

• A Microsoft Excel Warning dialogue box appears and asking for want to replace the existing data. Click on OK or press Enter to proceed.

• This would instantly split the Year from given data. 

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-4

Repeat the same process to split the Host Name and Winner Team. 

• Select the data range (C3:C13) or the entire column (Column C)

• Press Alt+D+E or Alt+A+E on the keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-5

• In Step 2, select the only checkbox ‘Other‘ and enter a hyphen  in the box to the right of it. Please note that the hyphen is used here as the delimiter. Click on Next or press Enter.

• Make sure that other checkboxes should be unchecked (if any).

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-6

• In Step 3, keep the default destination cell (e.g., C2), no need to change the destination cell.

• Click on ‘Finish‘ or press Enter on the keyboard.

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-7

• A Microsoft Excel Warning dialogue box appears and asking for want to replace the existing data. Click on OK or press Enter to proceed.

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-8

• This would instantly split the Hosts name and Winner Team into two parts.  

Text to column (Split text into Years, Hosts Name & Winner Team having delimiter hyphen and underscore)-9

■ Example 7: Convert Text to Numbers

 

Sometimes imported data from databases or other file formats (like CSV), the numbers are converted into text format.

It mainly happens in two ways:

• Having an apostrophe before the number. This leads to the number being treated as text.

• Getting numbers as a result of text functions such as LEFT, RIGHT, MID, CONCATENATE.

The problem arises with these numbers (which are in text format) as those numbers are basically ignored by Excel formulas such as VLOOKUP, SUM, AVERAGE, SUMIFS, etc.

Let’s start with an example database as shown below:

Text to column(Convert Text to Numbers)-1

• Question: How do we identify a Number is either in General format or in Text format? 

It is very simple to identify a number is either in general format or text format in the following ways: 

Text to column(Convert Text to Numbers)-2
Text to column(Convert Text to Numbers)-3

Steps to Start: 

• Select the data range (C3:C12) or the entire column (Column C)

• Use the Excel shortcut Alt+D+E or Alt+A+E to open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to column(Convert Text to Numbers)-4

• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then Click on ‘Next’ or ‘Enter’.

Text to column(Convert Text to Numbers)-5

 

• In Step 3, keep the default destination cell (e.g., C3), no need to change the destination cell. The Column data format should be in ‘General‘ (otherwise select the General checkbox).

Text to column(Convert Text to Numbers)-6
  • Click on ‘Finish‘ or press Enter on the keyboard.
  • This would instantly convert the text to the number. Therefore, the formula works henceforth. 
Text to column(Convert Text to Numbers)-7

■ Example 8: Convert Number to Text

 

• After putting a large number (12 or more digits) in a cell the general format uses scientific (exponential) notation like 9333E+15.

• If we convert this number to number format the last digit has been modified.

So in these cases, we would like to convert the number to text. 

Text to column(Convert Number to text)-1To overcome the situation, we first change the cell format in text format by using ‘Convert Text to Columns Wizard‘.  

• Select the data range (B2:B13) or the entire column (Column B)

• Use the Excel shortcut Alt+D+E or Alt+A+E to open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next or press Enter.

Text to column(Convert Number to text)-2

• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click on Next or press Enter.

Text to column(Convert Number to text)-3

• In Step 3, keep the default destination cell (e.g., B2), no need to change the destination cell. The Column data format should be selected in the ‘Text‘ checkbox.

Text to column(Convert Number to text)-4

• Click on ‘Finish‘ or press Enter on the keyboard.

This would instantly give the results in text format. Arrange the subject heading accordingly.

Text to column(Convert Number to text)-5


■ Example 9: Extract First/Last Few Characters of a Fixed Width Text/String

 

If we require to extract the first 4 characters and last 5 characters from a fixed-width text /string, then we go for the Fixed width option instead of the Delimited (default) option under Convert Text to Columns Wizard.

Suppose we have codes having the same characters (we can apply the LEN function for checking the characters count if require) and we need to extract the first 4 characters and last 5 characters from it. 

Text to column (Extract Few Characters of a fixed width text or String)-1

Here are the steps to quickly extract the characters from a text/string using the Convert Text to Columns Wizard:

• Select the data range (A2:A13) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on keyboard. This will open the Convert Text to Columns Wizard.

• In Step 1, we select Fixed Width (don’t select ‘Delimited’) and then click on Next or press Enter.

Text to column (Extract Few Characters of a fixed width text or String)-2

 

• In Step 2, in the Data preview section, click after the first 4 characters in the text to create a break-line and then create another break-line to click from the last 5 characters before.  Click on Next or press Enter.

Note:

(i) If wrongly placed a break-line anywhere in the text, we can delete/remove it by double-clicking on it.

(ii) If we want to move it to another place, simply click and drag it in another location.

Text to column (Extract Few Characters of a fixed width text or String)-3

• In Step 3, select the destination cell (e.g., here selects C2) to avoid the overwriting of existing data. 

• Make sure that the Column data format ‘General‘ should be checked.

• Click on ‘Finish‘ or press Enter on the keyboard.

Text to column (Extract Few Characters of a fixed width text or String)-4

• A Microsoft Excel Warning dialogue box appears and asking for want to replace the existing data. Click on OK or press Enter to proceed.

Text to column (Extract Few Characters of a fixed width text or String)-5

• This would split the data into three parts – the first part contains 4 characters (required), the last part contains 5 characters (required) and the middle part contains rest characters (not required). We should delete this column by Excel shortcut Ctrl + minus (-).

Text to column (Extract Few Characters of a fixed width text or String)-6

Question: How do we add Leading zero(s) of any number?

Please keep in mind that the number with starting zero value is omitted by default in Excel. For this reason, we find some numbers having 4 digits after splitting in spite of 5 digits.

If it is the mandates to keep the last 5 digits intact, obviously we need to add zero before the number. This is done more dynamically with the TEXT function.    

Text to Columns (Syntax of TEXT function)
Text to Columns (Add zero value before any number by TEXT function)

As shown above, within the TEXT syntax using five time zeros with double quotation likes ‘00000’ which refers that our text value should be 5 characters, any shortage of character it replaces with leading zero(s).    

 

• Convert the formulas to values:

Either Copy (Ctrl+C) the selected range F1:F13 or the entire range A1:F13 and then select either the ‘Values and number formats(Alt+Ctrl+V+U or Alt+E+S+U) or ‘Values‘ (Alt+Ctrl+V+V or Alt+E+S+V) under the Paste Special dialog box.


■ Example 10: Convert Numbers with Trailing Minus Sign to Negative Numbers 

 

Sometimes we find a range of numbers with trailing minus signs and we want to make these numbers negative.

Text to Columns gives the perfect solution regarding this.

Here are the steps to convert this trailing minus into negative numbers:

• Select the data range (A2:A13) or the entire column (Column A)

• Press Alt+D+E or Alt+A+E on the keyboard to open the Convert Text to Columns Wizard.

• In Step 1, we select Delimited (default option) and then click on Next.

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-1

• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click on Next or press Enter.

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-2

• In Step 3, click on the Advanced button.

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-3

In the Advanced Text Import Settings dialog box, select the ‘Trailing minus for negative number‘ option and then click on OK or press Enter.

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-4

• Select the destination cell (e.g., here selects B2) to avoid the overwriting of existing data. 

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-5

• Click on ‘Finish‘ or press Enter. This would instantly place the minus sign from the end of the number to the beginning of it. Now we can easily use these numbers in formulas and calculations.

Text to Columns (Convert Numbers with Trailing Minus Sign to negative numbers)-6

III. Conclusion

  • Convert Text to Columns is basically used for the splitting of delimited text in Excel. 
  • Convert Text to Columns can work for a single column only at a time, it does not work with multiple columns.  
  • Other than the splitting of delimited text, it can also perform to convert ‘invalid date formats to a valid date format‘, convert ‘number to text‘ format and ‘text to number‘ format.
  • It’s another feature used in Excel to Convert Numbers with Trailing Minus Sign to negative numbers.
  • It is also used to extract First/Last Few Characters of a fixed-width String in Excel.   

Read-More-6

90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key

80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts

09 Steps to Reduce Excel File size || Evaluate Existing Formulas ||


Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF
Advance Excel Forum

10 Examples of Text to Columns || How to Split Cells/Columns in Excel

Editor's Rating:
5

Leave a Comment