How to Separate Numbers / Values in Excel & Google Sheets

This tutorial demonstrates how to separate numbers or values in Excel and Google Sheets.

 

 

Separate Numbers or Values

If you have a list of numbers and want to split each by digits, you can use Excel’s Text to Columns functionality.

Look at the list of phone numbers in the picture below.

 

 

Say you want to separate the numbers and get the first three digits in Column C, the middle three digits in Column D, and the last four digits in Column E.

1. First, eliminate all non-numeric characters from the phone numbers. Select the range (B2:B6) and in the Ribbon, go to Home > Find & Select > Replace.

 

 

2. In the Find and Replace window, enter “(” (a left parenthesis) in the Find what box, and click Replace All. Leave the Replace with box blank, as you want to delete all occurrences of the character in the range (replace them with blanks).

 

 

Repeat this step (find and replace all) for “)” (right parenthesis), “–” (hyphen), and ” ” (space) so that there are only numbers in Column B.

 

 

3. Now use Text to Columns to separate the cleaned-up numbers. Select the cells with phone numbers (B2:B6) and in the Ribbon, go to Home > Text to Columns.

 

 

4. In Step 1 of the Text to Columns Wizard, choose Fixed width and click Next. Here, you have to choose Fixed width and split data manually, because there is no delimiter.

 

 

5. In Step 2 of the Wizard, click in the data preview on the position where you want to add a column break (between the third and the fourth digit). Repeat to create a second break line (between the sixth and the seventh digit), and click Next.

 

 

6. In the last step of the wizard, in the Destination box, enter the cell where you want to position split data (C2). In the Data preview, you can see how the data would be split. Click Finish.

 

 

Finally, phone numbers from Column B are split into three columns: the first three digits (area code) in Column C, the middle three digits (exchange code) in Column D, and the last four digits (line number) in Column E.

 

 

Tips

• If all of your numbers are formatted (with text characters) identically, it may be possible to skip Steps 1 and 2 and use delimiters instead, but dealing with numerical values does have other benefits.
• You can also use formulas to split numbers by digits.
• There’s a shortcut to open the Text to Columns Wizard: press ALT > A > E.

Separate Numbers or Values in Google Sheets

In Google Sheets, there is no Text to Columns option to manually split values. You need to use the LEFT, RIGHT, and MID Functions and extract data based on length.