WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to Split name into two columns in excel (3 Easy ways)

July 22, 2022

Splitting a name into two columns in Excel is very easy. You can do it with the help of a simple formula or by using a number of functions. You can split a name into two columns if you want to compare it with another name or if you want to split it in order to get the total number of occurrences of each word in that row. For example, if your data contains names and you want to remove all spaces and punctuation marks, then you need to split the values into two columns.

How to Split name into two columns in excel

I have a excel table that contains names information. I need to split the names into two columns so that I can see the first name in one column, and the last name in another column. Following are the steps I will use to split the names in two columns in excel:

This method works best with versions 2016/2016/mac/online.

  1. In order to work with Excel spreadsheets in WPS, you first need to select your target range.


  1. You can do this by clicking on Data > Text to Columns in the WPS interface.

  1. Next, you'll need to choose Delimited in the Convert Text to Columns Wizard interface.

  1. After you click Next in the Convert Text to Columns Wizard interface, check the space box.

5. Once you've selected your options for converting your text into columns, click Finish.

Splitting columns intelligently

You can take advantage of intelligent splitting to quickly identify the content of a table and split the names into two columns in excel. No manual operation is needed!

For example, let's take a look at this table.

  1. We select cell range, and in the Text to Columns drop-down menu, click Smart Split Columns. We can preview it in real time with no loss of speed.

  1. If we don't like the result, we can add or delete dividing lines by clicking Add or Delete.

  1. Finally, when we're done, we can click Finish for the output.

Multiple splitting methods for selection

We can use multiple splitting methods to split names into two columns in excel.

In addition to the automatic splitting methods, we can also manually split columns using the WPS Office Split Columns feature. The automatic splitting methods include Delimiter, Text Type, By Keyword, and Fixed Width. These are a few options that you can use when you need to split your data into different categories.

For example, we can have a table made up of English words and numbers.

  1. To split the table according to that, we need to select Manually Split Columns

  1. Then select Text Type

  1.   Then check Number, and English.

  1. Then click Finish.

Did you learn about how to split names into two columns in excel You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.