Have you ever had an excel spreadsheet in which you’d like to split one column into two or more? For instance, say you have a column with names and you want to separate the first and last names into two separate columns. Excel has a built in function called text to columns which enables you to do this very quickly. In this post we’ll show you how to use excel’s text to columns function to separate names.
Separate First and Last Name Using Text to Columns
Here are the steps to use ‘Split Text into Columns’ to separate first and last name: Select the cells that contain the name that you want to split. Click the Data tab. Click on ‘Split Text into Columns’ option. In the Separator box that appears, select Space as the delimiter. Then click insert from the menu and a new column will be inserted. Next, select the column with the first and last names that you want to separate. In our example, that data is in column A so we select column A by clicking in the header area around where it says A. Then, go to the data menu and click text to columns. Select the cell or column that contains the text you want to split. Select Data Text to Columns. In the Convert Text to Columns Wizard, select Delimited Next. Select the Delimiters for your data. Delimited text is text that has some character, such as a comma, tab, or space, separating each group of words that you want placed into its own column. To separate delimited text into multiple columns, follow these steps: Highlight the range of text to be separated. Go to Data, Data Tools, Text to Columns. The Convert Text to Columns Wizard opens.
Open the spreadsheet that has a column of first and last names. Now, assuming this column is first and last names only with a space in between, follow the steps below to separate the first and last names into separate columns.
When you use text to columns your first and last name column will become 2 columns. If you have columns to the right of your name column you’ll have to insert a blank column so you don’t lose any information. This blank column will become your last name column.
To insert a blank column select the column to the RIGHT of where you want to put the blank column by clicking in the header row. In our example, we want to put the blank column in between columns A and B so we select column B by clicking in the header area of column B. Then click insert from the menu and a new column will be inserted.
Next, select the column with the first and last names that you want to separate. In our example, that data is in column A so we select column A by clicking in the header area around where it says A. Then, go to the data menu and click text to columns.
This will open the wizard that walks you through the text to column function. There will be three screens to go through. In the first, you’re going to tell excel how your information is separated. Since our first and last names are separated by a space, we’re going to select delimited, which means that we want the text to be separated at a specific character. In our example, the character being used as a separator is the space. Click next.
Since we are separating our data at the space we check space and make sure the other characters are not checked. You’ll see a preview of your data in the bottom of the window so you can be sure that you’ve got the right combination. When it looks correct, click next.
On the last screen you can identify the data types for columns. We don’t need to do that so we just click finish.
Our names are now split into two columns!We can add a name to our column and our spreadsheet looks like this:
If your data is stored differently or you run into any problem, leave a comment below and we can help!
Also, check out our post on an advanced use of the text to column function where we use it to separate addresses into street, city, state and zip. And subscribe below to get alerted of new Excel and easy tech tutorials:
This MSAccess tutorial explains how to use the Access Split function with syntax and examples.
Description
The Microsoft Access Split function will split a string into substrings based on a delimiter. The result is returned as an array of substrings.
Syntax
The syntax for the Split function in MS Access is:
Parameters or Arguments
Split Text To Columns In Numbers
Optional. This is the type of comparison to perform when parsing the substrings and can be one of the following:
| VBA Constant | Value | Explanation |
|---|---|---|
| CompareMethod.Binary | 0 | Binary comparison |
| CompareMethod.Text | 1 | Textual comparison |
Applies To
The Split function can be used in the following versions of Microsoft Access:
- Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Example
Let's look at examples of how to use the Split function in MS Access:
Example in VBA Code
The Split function can be used in VBA code in Microsoft Access.
Split Text To Columns In For Addresses Word
For example:
Split Text To Columns
In this example, the variable called LArray would now contain the array {'Tech', 'on', 'the', 'Net'}. The four MsgBox statements would display the value stored in each element of the array.