How to Separate Numbers / Values in Excel & Google Sheets

In this tutorial, you will learn 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, (1) enter “(” (a left parenthesis) in the Find what box, and (2) 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). After that, there are only numbers in Column B.

 

 

3. Now you can use the Text to Columns option to separate the numbers. Select the range with numbers (B2:B6) and in the Ribbon, go to Home > Text to Columns.

 

 

4. In Step 1 of the Text to Columns Wizard, select 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 (3) 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 will 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.

 

 

You can also use VBA code to split numbers by digits.

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.