How To Separate Numbers In Excel

You can watch a video tutorial here.

Excel has many features and tools to help with formatting and manipulating text and numbers in cells. When cleaning up data or performing data analysis, you may need to separate the numeric characters from the cells. This happens many times when analyzing or cleaning textual data such as customer feedback or transcripts.

In this example, we will look at a transcript of employee appraisals. The employee code is part of the transcript and needs to be separated into a column so that it can be stored along with the text of the transcript.

The first method is to use a formula. The approach to building the formula works is explained below:
1. Create an array of numbers equal to the length of the text in the cell using the ROW() and INDIRECT() functions
  1. ROW() function: this returns the row number of the cell reference
    1. Syntax: ROW(cell reference)
      1. cell reference: (optional)the reference of the cell for which you want the row number. If this is left blank, it takes the row number of the cell it is in
  2. INDIRECT() function: this returns the reference given by a string. Here it is used to transform the values returned by ROW() into an array
    1. Syntax: INDIRECT(reference)
      1. reference: the cell that contains the reference
2. Put each character in the text into the array using the MID() function
  1. MID() function: this returns a specified number of characters from a string
    1. Syntax: MID(text, start number, number of characters)
      1. text: the string from which the characters are to be extracted
      2. start number: the number of the character from which the extraction is to start
      3. number of characters: the number of characters to extract
3. Multiply the array by 1 to convert non-numeric characters into errors and keep numeric characters as they are
4. Replace the errors with blank spaces using the IFERROR() function.
  1. IFERROR() function: if a formula results in an error, this function replaces the error with a specified value
    1. Syntax: IFERROR(formula,value)
      1. formula: the formula to be evaluated
      2. value: the value to be returned if the formula returns an error
5. Collapse the array into a single string using the TEXTJOIN() function
  1. TEXTJOIN() function: this combines multiple pieces of text into a single string using the specified delimiter
    1. Syntax: TEXTJOIN(delimiter, ignore_empty, text1, text 2…….)
      1. delimiter: the delimiter to be used between the pieces of text
      2. ignore_empty: if set to TRUE, this ignores empty pieces of text
      3. text1, text 2…..: the pieces of text to be joined. At least one piece is mandatory

Note: In some versions of Excel, this formula is to be treated as an array formula. After creating the entire formula, press Ctrl+Shift+Enter instead of just Enter.

The second method is to use the Flash Fill option

Option 1 – Use a formula

Step 1 – Create the array of numbers

Select the cell in which the result is to be displayed
Type the formula using cell references:

=ROW(INDIRECT(“1:”&LEN(Text)))

Press Enter
This creates an array of numbers equal to the length of the text

Note: If you encounter a #SPILL! error then it means that the rows below the selected cell contain data and the array cannot be displayed. Remove the data in the cells below to see the array.

Step 2 – Add each character to the array

Select the cell with the formula
Enhance the formula with the MID() function

=MID(Text,ROW(INDIRECT(“1:”&LEN(Text))),1)

Press Enter
The MID() function is used with the array so the number of characters to be returned is 1 for each character in the array

Step 3 – Multiply the array by 1

Select the cell with the formula
Enhance the formula by adding ‘*1’

=MID(Text,ROW(INDIRECT(“1:”&LEN(Text))),1)*1

Press Enter
Non-numeric characters are replaced by an error value

Step 4 – Replace the errors with blanks

Select the cell with the formula
Enhance the formula with the IFERROR()
function

=IFERROR(MID(Text,ROW(INDIRECT(“1:”&LEN(Text))),1)*1,””)

Press Enter
The errors are replaced by empty values

Step 5 – Collapse the array into a single string

Select the cell with the formula
Enhance the formula with the TEXTJOIN() function

=TEXTJOIN(“”,TRUE,IFERROR(MID(Text,ROW(INDIRECT(“1:”&LEN(Text))),1)*1,””))

Press Enter
No delimiter is used (“”) and the value TRUE tells the function to ignore empty cells

Step 6 – Copy the formula

Using the fill handle from the first cell, drag the formula to the remaining cells

Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)