How to remove numbers from a cell value

This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula and it extracts everything except the numbers from cell B3 shown in the image above.

1. How to remove numbers from a cell value (Excel 2016)

The following formula contains the TEXTJOIN function and it works only in Excel 2016.

It allows you to filter values up to 1000 characters and you can easily change that limit by changing this cell reference: $A$1:$A$1000 in the formula above.

Back to top

Explaining the Excel 2016 array formula in cell C3

Step 1 - Count characters in cell B3

The LEN function returns the number of characters in a cell value.

LEN(text)

LEN(B3)

becomes

LEN("AA123BB")

and returns 7.

Step 2 - Create a reference

The INDEX function returns a value based on a row and column number (optional), however, it can also be used to create a dynamic cell reference.

INDEX($A$1:$A$1000, LEN(B3))

becomes

INDEX($A$1:$A$1000, 7)

and returns A7.

Step 3 - Create a cell reference to a cell range

$A$1:INDEX($A$1:$A$1000, LEN(B3))

becomes

$A$1:A7

Step 4 - Create row numbers based on cell ref

The ROW function returns a number representing the row number of a given cell reference.

ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))

becomes

ROW(A1:A7)

and returns

{1; 2; 3; 4; 5; 6; 7}.

Step 5 - Split characters in cell B3

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)

becomes

MID("AA123BB", {1; 2; 3; 4; 5; 6; 7}, 1)

and returns

{"A"; "A"; "1"; "2"; "3"; "B"; "B"}.

Step 6 - Remove numbers from array

The TEXT function lets you apply formatting to a given value.

TEXT(value, format_text)

TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), "")

becomes

TEXT({"A"; "A"; "1"; "2"; "3"; "B"; "B"}, "")

and returns

{"A"; "A"; ""; ""; ""; "B"; "B"}.

Step 7 - Join remaining characters

The TEXTJOIN function concatenates values in a cell range or array.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("", TRUE, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), ""))

becomes

TEXTJOIN("", TRUE, {"A"; "A"; ""; ""; ""; "B"; "B"})

and returns "AABB".

Back to top

2. How to remove numbers from a cell value (Excel 365)

Update 1/12/2021 new dynamic array formula in cell C3:

This formula works only with Excel 365, it contains the new SEQUENCE function that creates an array containing numbers from 1 to n.

Back to top

2.1 Explaining the Excel 365 dynamic array formula in cell C3

Step 1 - Count characters in cell B3

The LEN function returns the number of characters in a cell value.

LEN(text)

LEN(B3)

becomes

LEN("AA123BB")

and returns 7.

Step 2 - Create an array of numbers from 1 to n

The SEQUENCE fucntion creates a list of sequential numbers to a cell range or array. It is located in the Math and trigonometry category and is only available to Excel 365 subscribers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(7)

and returns

{1; 2; 3; 4; 5; 6; 7}.

Step 3 - Split value into characters

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(B3, SEQUENCE(LEN(B3)), 1)

becomes

MID("AA123BB", {1; 2; 3; 4; 5; 6; 7}, 1)

and returns

{"A"; "A"; "1"; "2"; "3"; "B"; "B"}.

Step 4 - Remove numbers in the array

The TEXT function lets you apply formatting to a given value.

TEXT(value, format_text)

TEXT(MID(B3, SEQUENCE(LEN(B3)), 1), "")

becomes

TEXT({"A"; "A"; "1"; "2"; "3"; "B"; "B"}, "")

and returns

{"A"; "A"; ""; ""; ""; "B"; "B"}.

Step 5 - Join remaining characters

The TEXTJOIN function concatenates values in a cell range or array.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("", TRUE, TEXT(MID(B3, SEQUENCE(LEN(B3)), 1), ""))

becomes

TEXTJOIN("", TRUE, {"A"; "A"; ""; ""; ""; "B"; "B"})

and returns "AABB".

Back to top

Get Excel *.xlsx file

How to remove numbers from cell value.xlsx

4. How to format numbers as text

A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to spot numbers that have been incorrectly formatted as text values.

The first example shows how Excel shows a number formatted as a number. The number is aligned to the right whereas numbers formatted as text are aligned to the left in the cell.

Method 1

Instructions:

1. Select a cell
2. Type '
3. Type the number
4. Press Enter

This example is shown in the second row in the image above.

Method 2

Instructions:

1. Select a cell
2. Type ="number"
3. Press Enter

The number is the number you want to be formatted as text. This example is shown in the third row in the image above.

Method 3

Instructions:

1. Select a cell
2. Type the number
3. Press Enter
4. Press CTRL + 1 to open the "Format Cells" dialog box.
5. Make sure you are on tab "Number".
6. Select Category: "Text".
7. Press with left mouse button on OK button.

Number incorrectly formatted as a text value

A green arrow appears in the upper left corner of the cell if Excel believes you have a number in your worksheet that was formatted incorrectly as text.

Press with left mouse button on the exclamation button and an options menu appears.

This lets you quickly deal with the problem:

Convert to Number	Changes the formatting to a number. Value is now right-aligned.
Help on this Error	Opens a help guide inside Excel.
Ignore Error	Removes the green arrow, everything else remains as it is.
Edit in formula Bar	The prompt is moved to the formula bar where you can edit the cell value.
Error Checking Options...	Opens the "Excel Options" dialog box, see picture below. This allows you to change how Excel behaves when it comes to error values.

If you have multiple values checked as errors you can easily remove the green arrows by selecting the cells containing the error arrow. Then press with left mouse button on the "Exclamation mark" button and lastly press with left mouse button on "Ignore error".

Text string manipultion category

More than 1300 Excel formulas

Excel categories

Leave a Comment