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.
Table of Contents
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.
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".
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.
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".
Get Excel *.xlsx file
How to remove numbers from cell value.xlsx
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
Identify all characters in a cell value
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
How to use the COUNTBLANK function
The COUNTBLANK function counts empty or blank cells in a range. The picture above demonstrates the COUNTBLANK function entered in […]
Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then […]
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
How to use SELECT CASE statement
The SELECT CASE statement allows you to compare an expression to multiple values. It is similar to the IF THEN ELSE […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
