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 TO CONVERT TEXT TO COLUMNS WIZARD
➢ Method 1: Using the Excel Shortcut
Select the data range, i.e., A2:A14 ➪ Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard’.
■ Note: We had detail discussed on Excel shortcuts in separate two tutorials, suggested you read these tutorials:
80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts
90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |
➢ Method 2: Using the Ribbon
Select the data range, i.e., A2:A14 ➪ Go to the ‘Data‘ tab ➪ Click on ‘Text to Columns‘ under the Data Tools section ➪ ‘Convert Text to Columns Wizard‘ appears.
(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 FIRST NAME, LAST NAME (DELIMITER IS COMMA ‘,’) & COUNTRY NAME (DELIMITERS ARE AT THE RATE ‘@’ AND SPACE ‘ ‘)
• Select the data range (A2:A14) or the entire column (Column A)
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, select the checkboxes of ‘Comma‘, ‘Space‘ and ‘Other‘ [enter @ in the box to the right of it] as the delimiters. If there are double/triple consecutive spaces between the names, additionally select the ‘Treat consecutive delimiters as one‘ checkbox.
Then click Next or press Enter.
■ Note: We have noticed that there are extra spaces both before and after the ‘@’. If the ‘Space’ checkbox is unchecked then the spaces still persist in both before and after the ‘@’. We see the difference between the above and below.
• In Step 3, select the destination cell (e.g., here we select the cell 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.
• Click Finish or press Enter on the keyboard.
This would instantly give the results with the first name, last name and country name are split into different columns.
■ EXAMPLE 2: SPLIT DATE & TIME (DELIMITER IS SPACE ” “)
• Select the data range (A2:A14) or the entire column (Column A).
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, select only the checkbox ‘Space‘. As mentioned above, if there are multiple consecutive spaces as a delimiter, also select the ‘Treat consecutive delimiters as one‘ option.
Then click Next or press Enter.
Make sure that other checkboxes must be unchecked (if any).
• 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 dataset – 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.
• Click 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.
For data preparation, we should need to arrange the date format to a valid date format in Excel such as ‘dd-mmm-yy‘ (or any other format).
We can follow any of the following methods:
(01) Using the Excel Shortcut
Select the date range B2:B14 ➪ Then press Ctrl+Shit+#. All are arranged in a valid date format.
(02) Using the ‘Format Cells’ Dialog Box Via Excel Shortcut
Equivalently, select the data range B2:B14 ➪ then press Ctrl+1 which will open the ‘Format Cells‘ dialog box ➪ Go to the ‘Custom‘ option under the ‘Number‘ tab ➪ On the right side, mentioned a valid date format like ‘dd-mmm-yy‘ under the ‘Type‘ section.
(03) Using the Format Cells Dialog Box Via the Ribbon
Alternatively, select the data range B2:B14 ➪ Go to the Home tab ➪ Either click on the Alignment dialog box launcher, a small square, is located at the right side corner of the ‘Alignment‘ section or click on the Number dialog box launcher, a small square, is located at the right side corner of the ‘Number‘ section.
As a result, the ‘Format Cells‘ dialog box opens ➪ Select the ‘Custom‘ option under the ‘Number‘ tab ➪ On the right side, input a valid date format such as ‘dd-mmm-yy‘ under the ‘Type‘ section.
Finally, press OK or press Enter on the keyboard. We get the result in the desired format as shown as below:
■ Note: We had detail discussed on How to Change Valid Excel Date Format in a separate tutorial, suggested you read this tutorial: BEST 05 WAYS: HOW TO CHANGE DATE FORMAT IN EXCEL?
■ EXAMPLE 3: CONVERT INVALID DATE FORMATS TO VALID DATE FORMATS
Sometimes we get the lists of invalid date formats during data 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)
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click on Next or press Enter.
• In Step 3, select the new destination cell (e.g., in this case, we select the cell B2) to avoid overwriting the existing data set.
• Click ‘Finish‘ or press Enter on the keyboard.
This would instantly convert the invalid date format to a valid date format.
■ EXAMPLE 4: SPLIT DATES IN THE DAYS, MONTHS & YEARS (DELIMITER IS HYPHEN “-“)
Sometimes we require to split the dates into 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 either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• 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 Next or press Enter.
Make sure that other checkboxes should be unchecked (if any).
• In Step 3, select the destination cell (i.e., B1) to avoid the overwriting of existing data.
• Click ‘Finish‘ or press Enter on the keyboard.
• A Microsoft Excel Warning dialog box appears and confirming us do we want to replace the existing data? Click OK or press Enter to proceed.
This would instantly split the days, months and years into different columns. Arrange the subject heading accordingly.
■ EXAMPLE 5: JOIN DAYS, MONTHS & YEARS TO FORM VALID DATE FORMATS (DELIMITER IS 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) To use CONCATENATE() function joining the DAYS, MONTHS and YEARS with delimiter slash ‘/’.
(02) To use ‘Paste Special’ to convert the formulas into values.
(03) Finally, using the ‘Convert Text to Columns wizard’ to convert invalid date formats to valid date formats.
(01) TO USE CONCATENATE() FUNCTION JOINING THE DAYS, MONTHS & YEARS WITH DELIMITER SLASH ‘/’
• In Step 1, we use the CONCATENATE function to join the dates, months and years.
Apply an equality ‘= ‘ sign in cell D2 and then type a few characters of ‘con…..‘. Excel suggests different functions starting with ‘CON‘. Select CONCATENATE from the drop-down list and press the ‘Tab‘ key on the keyboard simultaneously. As a result, the CONCATENATE syntax appears with open parenthesis.
• We should take care of joining the criteria
(i) First select the cell with a day (i.e., C2) in the CONCATENATE function and put a comma (,) to close the text1.
Use a delimiter slash (/) in the double quotes and put a comma (,) after that to close the text2.
(ii) Then select the cell with a month (i.e., B2) and put a comma (,) to close the text3.
Use a delimiter slash (/) in the double quotes and put a comma (,) after that to close the text4.
(iii) Finally, select the cell with a year (i.e., A2) and press ‘Enter’.
• So the complete formula is =CONCATENATE(C2,”/”, B2,”/”, A2) and returns the result. Copy the formula till the end of the range.
(02) TO USE ‘PASTE SPECIAL’ TO CONVERT THE FORMULAS INTO VALUES
The next step converts the formula into values with the help of the Paste Special dialog box. We can follow any of the following methods:
➢ Method 1: Copy the range (D2:D14) ➪ press Alt+E+S+U (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (sequentially press Alt+Ctrl+V, U) to select the ‘Values and number formats‘ option ➪ Press Enter or click OK. As result, the entire range would instantly convert formulas into values.
➢ Method 2: or press Alt+E+S+V (sequentially press Alt, E, S, V) or Alt+Ctrl+V+V (sequentially press Alt+Ctrl+V, V) to select the ‘Values‘ option ➪ Press Enter or click OK. As result, the entire range would instantly convert formulas into values.
(03) USING THE ‘CONVERT TEXT TO COLUMNS WIZARD‘ TO CONVERT INVALID DATE FORMATS TO VALID DATE FORMATS
• Select the data range (A2:A11) or the entire column (Column A)
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click Next or press Enter.
• In Step 3, select the destination cell (i.e., E2) to avoid the overwriting of existing data.
Then select the Date checkbox.
Click ‘Finish‘ or press Enter on the keyboard.
This would instantly convert invalid date formats to valid date formats.
■ EXAMPLE 6: SPLIT TEXT INTO YEARS, HOST NAMES & WINNER TEAM (DELIMITERS ARE 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 the ‘Other‘ box, but the Other box only allows a 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 either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• 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 a delimiter. Click Next or press Enter.
Make sure that other checkboxes should be unchecked (if any).
• In Step 3, select the destination cell (i.e., B2) to avoid the overwriting of existing data.
Click on ‘Finish‘ or press Enter on the keyboard.
• A Microsoft Excel Warning dialog box appears and confirming us do we want to replace the existing data? Click OK or press Enter to proceed.
This would instantly split the Year from given data.
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 either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• 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).
• In Step 3, keep the default destination cell (e.g., C2), no need to change the destination cell.
• Click ‘Finish‘ or press Enter on the keyboard.
• A Microsoft Excel Warning dialog box appears and confirming us do we want to replace the existing data? Click OK or press Enter to proceed.
• This would instantly split the Hosts name and Winner Team into two parts.
■ 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:
It is very simple to identify a number is either in general format or text format in the following ways:
Identify General Format:
Identify Text Format:
Steps to Start:
• Select the data range (C3:C12) or the entire column (Column C)
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click Next or press Enter.
• In Step 3, keep the default destination cell (i.e., C3), no need to change the destination cell. The Column data format should be in ‘General‘ (otherwise select the General checkbox).
Click ‘Finish‘ or press Enter on the keyboard.
This would instantly convert the text to the number. Therefore, the formula works henceforth.
■ 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.
To 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).
• Press either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click Next or press Enter.
• 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.
Click ‘Finish‘ or press Enter on the keyboard.
This would instantly give the results in text format. Arrange the subject heading accordingly.
■ 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 in the 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 the last 5 characters from it.
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 either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, we select Fixed Width (don’t select ‘Delimited’) and then click Next or press Enter.
• 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.
Finally, click on Next or press Enter.
■ Note:
➢ If wrongly placed a break-line anywhere in the text, we can delete/remove it by double-clicking on it.
➢ If we want to move it to another place, simply click and drag it to another location.
• In Step 3, select a new destination cell (e.g., here we select the cell C2) to avoid the overwriting of existing data.
Make sure that the Column data format ‘General‘ should be checked.
Click ‘Finish‘ or press Enter on the keyboard.
• A Microsoft Excel Warning dialog box appears and confirming us do we want to replace the existing data? Click OK or press Enter to proceed.
• 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 with the Excel shortcut Ctrl + minus (-).
If we notice carefully, in some cases leading zero is missing after splitting. We can add this with a separate method.
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.
In the given example, the TEXT function is applied in a separate column and within the TEXT syntax using five-times zeros in 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 Formulas into 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) in the Paste Special dialog box.
■ Note: We had a detail discussed on Paste Special in Excel in a separate tutorial, suggested you read this tutorial: Paste Special in Excel Vs Break Link – Which one is Better?
■ 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 either Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard‘.
• In Step 1, select the Delimited (default option) radio button and then click Next or press Enter.
• In Step 2, please notice that all the checkboxes should be unchecked if checked any. Then click Next or press Enter.
• In Step 3, click on the Advanced button.
In the Advanced Text Import Settings dialog box, select the ‘Trailing minus for negative number‘ option and then click OK or press Enter.
• Select a new destination cell (i.e., B2) to avoid the overwriting of existing data.
• Click ‘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.
(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.
Premium Courses on ed2go
Microsoft Excel 2019/Office 365 Series
Intermediate Microsoft Access 2019/Office 365
Advanced Microsoft Excel 2019/Office 365 (Self-Paced Tutorial)
Microsoft Excel and Project 2019 Suite
Accounting with MS Excel 2019 Suite
Microsoft Excel 2019 Certification Training
Financial Analyst Suite
Introduction to Microsoft Access 2016
Django Training for Python Developers
Premium Courses on Coursera
Data Analysis and Visualization
Excel Fundamentals for Data Analysis
Data Visualization in Excel
Excel Skills for Business
Advanced Data Science with IBM
Data Analysis and Reporting in SAS Visual Analytics
Introduction to Spreadsheets and Models
Accounting Analytics
Introduction to Data Engineering
Premium Courses on Udemy
Master Excel Functions in Office 365 – Excel Dynamic Arrays (Learn to Use Excel’s NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP.) to Dramatically Simplify the Work You Do in Excel.)
Excel Essentials for the Real World (Complete Excel Course) [Microsoft Excel Beginner to Professional. Includes Pivot Tables, Power Query, NEW Formulas. (Excel 365, 2019 & 2016)]
Advanced Excel – Top Excel Tips & Formulas (Master Advanced Excel Formulas. Solve Complex Problems. Learn Advanced Excel Skills to Save Time & Impress (Excel 2010)
The Ultimate Excel Programmer Course (Learn Excel VBA from Scratch with Dan Strong, Bestselling Excel Expert with Over 180K Students Worldwide!)
Microsoft Excel Certification Exam Prep: MO-201 Excel Expert (Ace the Excel MO-201 Exam. Learn advanced data analysis & earn the Excel Expert Certification (MS Excel 2019/Office 365)
Excel Charts, Graphs & Data Visualization in Excel (Master 20+ Advanced Dynamic Excel Charts and Create Impressive Excel Graphs & Data Visualization in Microsoft Excel)
Excel Shortcuts for Management Consultants (Practical guide how to work fast in Excel during Management Consulting projects)
Beginners to Expert Excel and Excel VBA 38 Hours Mega Course (Learn Excel formulas, Pivot Tables, Excel VBA macros, charts and the basics with our Microsoft Excel 38+ hours tutorial)
Microsoft Power BI Masterclass – Expand Excellence (achieve the next Level of Business Intelligence with Microsoft Power BI Desktop. How to master Power BI Desktop)
12 Examples || How to Use Excel Go To Special?
07 Points Guided You How to Find And Replace in Excel?
05 Points Should Learn How to Freeze Panes in Excel?
12 Things Guided You How to Manage An Excel Workbook
03 Useful Methods : Add Numbers With AutoSum Excel
04 Simple to Advanced Methods: How to Filter in Excel?
05 Best Ways: Create Password Protect Excel & Unprotect it
08 Best Examples: How to Use Excel Conditional Formatting?
04 BEST WAYS: HOW TO TRANSPOSE DATA IN EXCEL
Tutorial 01: How to Use Excel SUMIFS Function with Single & Multiple Criteria
Thanks for your interest joining to Advance Excel Forum community.
Something went wrong.
Join Our Community List
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.
