How to Separate Text and Numbers in Excel

Have you ever encountered a situation where you have a column in Excel that contains both text and numbers, and you need to separate them into different columns? It can be a tedious task to manually extract the text and numbers, especially if you have a large dataset. But fear not, because in this tutorial, I will show you how to easily separate text and numbers in Excel using a few simple techniques.

Method 1: Using Text to Columns

The first method we'll explore is using the Text to Columns feature in Excel. This feature allows you to split the contents of a cell into multiple columns based on a delimiter. Here's how you can do it:

1. Select the range of cells that you want to separate.
2. Go to the Data tab in the Excel ribbon and click on the Text to Columns button.
3. In the Convert Text to Columns Wizard dialog box, choose the Delimited option and click Next.
4. Select the delimiter that separates the text and numbers in your data. This could be a comma, space, or any other character. If your data doesn't have a consistent delimiter, you can choose the Fixed width option and manually specify the positions where you want to split the data.
5. Click Next and choose the data format for each column. For example, if you want to separate a phone number into area code and number, you can choose the Text format for the area code and the General format for the number.
6. Click Finish to complete the process.

Here's an example to illustrate this method. Let's say you have a column that contains names and email addresses in the format "Name <email>". You want to separate the names and email addresses into two separate columns. Here's how you can do it:

1. Select the range of cells that contain the names and email addresses.
2. Go to the Data tab, click on Text to Columns, and choose the Delimited option.
3. In the next step, choose the Other delimiter option and enter "<" (without quotes) in the text box.
4. Click Finish to separate the names and email addresses into two columns.

Method 2: Using Formulas

Another way to separate text and numbers in Excel is by using formulas. This method is useful when you want to extract specific parts of the data or when the data doesn't have a consistent delimiter. Here are a few formulas you can use:

1. LEFT and RIGHT Functions

The LEFT and RIGHT functions allow you to extract a specified number of characters from the left or right side of a cell. Here's how you can use these functions:

• To extract the text from a cell, you can use the following formula:

  =LEFT(A1, FIND(" ", A1) - 1)
  

  This formula finds the position of the first space character in cell A1 and extracts all the characters to the left of it.

• To extract the numbers from a cell, you can use the following formula:

  =RIGHT(A1, LEN(A1) - FIND(" ", A1))
  

  This formula finds the position of the first space character in cell A1 and extracts all the characters to the right of it.

Here's an example to demonstrate these formulas. Let's say you have a column that contains names and phone numbers in the format "Name Phone". You want to separate the names and phone numbers into two separate columns. Here's how you can do it:

1. In a new column, enter the formula =LEFT(A1, FIND(" ", A1) - 1) to extract the names.
2. In another new column, enter the formula =RIGHT(A1, LEN(A1) - FIND(" ", A1)) to extract the phone numbers.

2. MID Function

The MID function allows you to extract a specified number of characters from the middle of a cell. Here's how you can use this function:

• To extract the text from a cell, you can use the following formula:

  =MID(A1, 1, FIND(" ", A1) - 1)
  

  This formula finds the position of the first space character in cell A1 and extracts all the characters from the beginning of the cell up to that position.

• To extract the numbers from a cell, you can use the following formula:

  =MID(A1, FIND(" ", A1) + 1, LEN(A1) - FIND(" ", A1))
  

  This formula finds the position of the first space character in cell A1 and extracts all the characters from that position to the end of the cell.

Here's an example to illustrate the MID function. Let's say you have a column that contains email addresses in the format "Name <email>". You want to separate the names and email addresses into two separate columns. Here's how you can do it:

1. In a new column, enter the formula =MID(A1, 1, FIND("<", A1) - 2) to extract the names.
2. In another new column, enter the formula =MID(A1, FIND("<", A1) + 1, LEN(A1) - FIND("<", A1) - 1) to extract the email addresses.

Method 3: Using Power Query

If you're using Excel 2010 or later, you can take advantage of Power Query to separate text and numbers. Power Query is a powerful data transformation tool that allows you to import, transform, and load data from various sources. Here's how you can use Power Query to separate text and numbers:

1. Select the range of cells that you want to separate.
2. Go to the Data tab in the Excel ribbon and click on the Get Data button.
3. Choose the appropriate data source option. For example, if your data is in an Excel table, you can choose the From Table/Range option.
4. In the Power Query Editor, select the column that contains the text and numbers.
5. Go to the Transform tab and click on the Split Column button.
6. Choose the By Delimiter option and enter the delimiter that separates the text and numbers in your data.
7. Click OK to split the column into multiple columns.
8. Close the Power Query Editor and load the transformed data into a new worksheet.

Here's an example to demonstrate the use of Power Query. Let's say you have a column that contains URLs in the format "https://formularizer.com". You want to separate the protocol (e.g., "https") and the domain (e.g., "formularizer.com") into two separate columns. Here's how you can do it:

1. Select the range of cells that contain the URLs.
2. Go to the Data tab, click on Get Data, and choose the From Table/Range option.
3. In the Power Query Editor, select the column that contains the URLs.
4. Go to the Transform tab and click on the Split Column button.
5. Choose the By Delimiter option and enter "://" (without quotes) as the delimiter.
6. Click OK to split the column into two columns.
7. Close the Power Query Editor and load the transformed data into a new worksheet.

Related Content

• How to Concatenate Text and Numbers in Excel
• How to Remove Text from Numbers in Excel
• How to Extract Numbers from Text in Excel

In this tutorial, we've explored three different methods to separate text and numbers in Excel. Whether you prefer using the Text to Columns feature, formulas, or Power Query, you now have the tools to efficiently split your data. Remember to choose the method that best suits your specific requirements and dataset.

Now go ahead and give it a try! Happy separating!