Text to Columns in Excel easily splits cells. For example, it can separate data in a cell like first names from last names. Moreover, it’s especially handy when you often need to deal with data from many sources. These sources include text files and databases.

The Text to Columns tool also fixes common Excel issues. For instance, it helps when VLOOKUP can’t find data due to number formatting errors. By correcting these errors, it ensures accurate and reliable searches.

This keeps your spreadsheet tidy and functional, allowing you to work more efficiently.

How to Separate Names in Excel

A common problem is that you have a list of names where both first and last names are in the same column. We’ll show you how to separate first and last name in Excel with Text to Columns.

Separate names in Excel

But you want to separate the names into separate columns. Text to Columns in Excel can do just that, making your list much easier to manage.

Here’s a quick guide on how to split first and last name in Excel:

  • Select Your Data: Highlight the column that contains the full names.
  • Access the Tool: Click on the ‘Data‘ tab, then select ‘Text to Columns’.
Separate names in Excel with Text to columns
  • Choose Split Type: In the dialog box, you’ll see two options: ‘Delimited’ and ‘Fixed Width’. Choose ‘Delimited‘ if your text is separated by characters such as spaces, commas, or semicolons. Select ‘Fixed Width‘ to divide your data at certain spots, like after a set number of characters. For our names example, we select ‘Delimited’ because the names are separated by spaces.
Separate names in Excel with Text to columns
  • Select the Delimiter: After choosing ‘Delimited’, click ‘Next’ and select the delimiter that corresponds to how your names are separated. For example, use ‘Space’ for names separated by spaces, or choose commas or semicolons for other types of separations. In the data preview window below, you can immediately see if the split works correctly.
Separate names in Excel with Text to columns
  • Specify the Destination: Decide where you want the separated names to go. If you don’t pick a specific cell for the output, Excel will replace your existing data. The first part of the data will appear in the original column, and the next part will go into the column right next to it.

❗ To keep your original data unchanged, make sure to choose a new destination cell. For instance, in this example, we started with cell B2 to keep the original names.

Separate names in Excel with Text to columns
  • Complete the Process: Click ‘Finish’, and it will separate names in Excel into two columns.
Separate names in Excel with Text to columns
  • Capitalize Names: For a polished look, you can use the Excel PROPER function to capitalize each name properly.
= PROPER(B2:C13)
Separate names in Excel with Text to columns and PROPER function

This way, each name in the range from D2 to C13 appears capitalized in two separate columns D and E.

How to Use Excel Text to Columns for Splitting Serial Numbers

A helpful way to use Excel’s Text to Columns tool with the Fixed Width feature is when you want to split complex numbers into easier-to-manage pieces. Let’s say you have a list of serial numbers where each one includes a type, a year, and a unique identifier.

The goal is to separate this data into three columns:

  • Product Type: The first 2 letters (e.g., TV, PH, LA)
  • Manufacture Year: The following 4 digits (e.g., 2018, 2019, 2010)
  • Unique Identifier: The final 5 digits (e.g., 12345, 56789, 45678)

How to Split cells in Excel:

  • Highlight the column that contains the serial numbers.
  • Navigate to the ‘Data’ tab, and select ‘Text to Columns.’
  • Choose ‘Fixed Width.’ Excel won’t automatically know where to split your data, so you’ll need to set this manually in the next step.
Split cells in Excel with Text to columns fixed width

Set Your Breaks: In the preview area, adjust the column breaks:

  • After the 2nd character to isolate the product type.
  • After the 6th character to isolate the manufacture year.

Any remaining characters will be recognized as the product ID.

Split cells in Excel with Text to columns fixed width
  • Specify the Destination: Choose where you want the split data to appear in your sheet. If you don’t select a new destination, Excel will overwrite the existing data.
  • Finish: Click ‘Finish’, and Excel will apply the splits.

Using the technique to split cells in Excel neatly organizes each part of the serial number into its own column, enhancing your data’s clarity and accessibility for analysis or record-keeping.

Solving a Common VLOOKUP Error in Excel

If you’ve ever used Excel’s VLOOKUP function and received a #N/A error, it might be due to a mismatch in data formats. Often, this happens when numbers you’re looking up the result of a copy/paste action from some external data source.

Example of the VLOOKUP Problem:

Imagine you have a table with product codes in column A (formatted as text) and descriptions in column B.

You want to find the description for a product code you enter in cell D2 using VLOOKUP in cell D3:

=VLOOKUP(D2, A2:B11, 2, False)

But instead of a description, the VLOOKUP function returns a #N/A error because the format of D2 (number) doesn’t match the text format of the codes in column A.

Simple Solution Using Text to Columns:

  • Select the Data: Click to highlight column A, which has your product codes.
  • Open Text to Columns: Go to the Data tab, and click ‘Text to Columns’.
  • Convert: Choose ‘Fixed Width’ (since we’re not splitting based on delimiters) and click ‘Finish’ right away without setting any breaks.

This changes the format from text to numbers, matching your VLOOKUP search criteria to the table.

After this quick fix, running the VLOOKUP formula again should give you the correct results, not errors. This method ensures your data types are aligned, allowing Excel’s functions to work smoothly.

Excel Text to Columns transformed all our numbers stored as text into actual numbers.

Effective Use of Excel Text to Columns

  1. Avoid Data Loss: Ensure that the cells where you plan to place the split data are empty. “Text to Columns” can overwrite existing data without warning.
  2. Update Needs: Be aware that Excel Text to Columns does not dynamically update. If the original data changes, you will need to repeat the process. For ongoing updates, use the Excel TEXTSPLIT function (available in Excel for Microsoft 365), which adjusts automatically when source data is modified. For more details on using TEXTSPLIT, click here for the detailed article.
  3. Complex Transformation: For complex data manipulations, Power Query is a better choice as it provides robust tools for transforming and organizing data. For more details on using Power Query, click here for the detailed article.

Remember, each tool has its specific uses and benefits. Choose based on your project needs for the best results.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to separate names in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.