" ExtendOffice
ExtendOffice !
!
How To Remove Letters
From
Strings/Numbers/Cells In
Excel?
This article is talking about how to remove
letters from text strings/cells/numbers in
Excel. And we provide four methods to solve it
by array formula, user defined function, and
third-party add-in of Kutools for Excel.
Remove letters from
strings/numbers/cells with array formula
Remove letters from
strings/numbers/cells with User Defined
Function
Remove letters from
strings/numbers/cells with Remove
Character utility of Kutools for Excel
!
Remove letters from
strings/numbers/cells with
EXTRACTNUMBER function of Kutools
for Excel !
! Remove Letters From
Strings/Numbers/Cells With Array
Formula
We can apply an array formula to remove
letters from strings/numbers/cells in Excel.
Please do as follows:
1. Select a blank cell you will return the text
string without letters, enter the formula
=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(-
-
MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
(A2 is the cell you will remove letters from)
into it, and press the Ctrl + Shift + Enter
keys at the same time.
2. Keep selecting the cell, and then drag its
Fill Handle to the range as you need. And
now you will see all letters are removed from
original text strings as below screenshot
shown:
Notes:
(1) This array formula will remove all kinds of
characters except the numeric characters.
For example, the original text string is abc-
859*-24test, this array formula will remove all
letters and special characters (* and -) and
return 85924.
(2) If there is no number in the text string,
this array formula will return 0.
Batch Separate Text And Numbers
From One Cell/Column Into Different
Columns/Rows
Kutools for Excel enhances its Split Cells
utility and supports to batch separate all
text characters and numbers of one
cell/column into two columns/rows. Full
Feature Free Trial 30-day!
! Remove Letters From
Strings/Numbers/Cells With User
Defined Function
We can also add a user defined function into
Excel for removing letters from
strings/numbers/cells. Please do as follows:
1. Press Alt + F11 keys at the same time to
open the Microsoft Visual Basic for
Applications window.
2. Click Insert > Module, and then copy and
paste the following code into the new
opening Module window.
VBA: Remove letters from
strings/numbers/cells in Excel
1 Function StripChar(Txt As String
2 With CreateObject("VBScript.RegExp"
3 .Global = True
4 .Pattern = "\D"
5 StripChar = .Replace(Txt, "")
6 End With
7 End Function
3. Save this user defined function. Select a
blank cell you will return the text string
without letters, and enter the formula
=StripChar(A2) (A2 is the cell that you will
remove letters from) into it, and drag the Fill
Handle down to the range as you need. See
screenshots:
Notes:
(1) This user defined function can also
remove all kinds of characters except the
numeric characters. For example, the
original text string is abc-859*-24test, this
array formula will remove letters and special
characters (* and -) and return 85924.
(2) This user defined function will return
numbers stored as text strings.
! Remove Letters From
Strings/Numbers/Cells With
EXTRACTNUMBERS Function Of
Kutools For Excel
This method will introduce Kutools for
Excel’s EXTRACTNUMBERS function to
remove all letters from stings/numbers/cells
easily in Excel. Please do as follows:
Kutools for Excel - Includes more than
300 handy tools for Excel. Full feature
free trial 30-day, no credit card required!
Free Trial Now!
1. Select a blank cell you will return the text
string without letters, and click Kutools >
Functions > Text > EXTRCTNUMBERS.
See screenshot:
2. In the opening Function Arguments dialog
box, specify the cell that you will remove
letters from into the Txt box, it’s optional to
type TRUE or FAlSE into the N box, and
click the OK button.
Note: Typing TRUE will return numeric
numbers, while typing nothing or FALSE will
return numbers stored as text strings.
3. Keep selecting the cell, and drag the Fill
Handle to the range as you need. And then
you will see all letters are removed from
original text strings. See screenshot:
Kutools for Excel - Includes more than 300
handy tools for Excel. Full feature free trial
30-day, no credit card required! Get It Now
Notes:
(1) You can type the formula
=EXTRACTNUMBERS(A2,TRUE) into
selected cell directly, and then drag the Fill
handle to the range as you need.
(2) This EXTRACTNUMBERS function will
also remove all kinds of characters except
the numeric characters.
! Remove Letters From
Strings/Numbers/Cells With Remove
Character Utility Of Kutools For Excel
All above methods will remove all characters
except the numeric characters. But
sometimes you may want to remove only
letters from text string, but remain numeric
characters and others. This method will
introduce Kutools for Excel’s Remove
Characters utility to get it done easily in
Excel.
Kutools for Excel - Includes more than
300 handy tools for Excel. Full feature
free trial 30-day, no credit card required!
Free Trial Now!
1. Select the cells that you will remove
letters from, and click Kutools > Text >
Remove Characters.
2. In the opening Remove Characters dialog
box, check the Alpha option, and click the
Ok button. And then you will see only letters
are removed from selected cells. See
screenshot:
Note: If you indeed want to remove all kinds
of characters except the numeric ones, you
can check the Non-numeric option and
click the Ok button in the Remove
Characters dialog box. See screenshot:
Kutools for Excel - Includes more than 300
handy tools for Excel. Full feature free trial
30-day, no credit card required! Get It Now
! Demo: Remove Letters From
Strings/Numbers/Cells In Excel
Posted by ExtendOffice - Professional Office Add-ins
" Kutools for Excel includes more
than 300 handy tools for Excel, free to try
without limitation in 30 days. Download
and Free Trial Now!
!Related Articles:
How to remove numbers from text strings in
Excel?
The Best Office Productivity
Tools
Kutools For Excel Solves Most Of Your
Problems, And Increases Your
Productivity By 80%
Reuse: Quickly insert complex formulas,
charts and anything that you have used
before; Encrypt Cells with password;
Create Mailing List and send emails...
Super Formula Bar (easily edit multiple
lines of text and formula); Reading Layout
(easily read and edit large numbers of
cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing
Data; Split Cells Content; Combine
Duplicate Rows/Columns... Prevent
Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select
Blank Rows (all cells are empty); Super
Find and Fuzzy Find in Many Workbooks;
Random Select...
Exact Copy Multiple Cells without
changing formula reference; Auto Create
References to Multiple Sheets; Insert
Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by
Position, Remove Space; Create and Print
Paging Subtotals; Convert Between Cells
Content and Comments...
Super Filter (save and apply filter
schemes to other sheets); Advanced Sort
by month/week/day, frequency and more;
Special Filter by bold, italic...
Combine Workbooks and WorkSheets;
Merge Tables based on key columns; Split
Data into Multiple Sheets; Batch Convert
xls, xlsx and PDF...
More than 300 powerful features.
Supports Office/Excel 2007-2019 and
365. Supports all languages. Easy
deploying in your enterprise or
organization. Full features 30-day free
trial. 60-day money back guarantee.
Read More... Free
Download... Purchase...
Office Tab Brings Tabbed Interface To
Office, And Make Your Work Much
Easier
Enable tabbed editing and reading in
Word, Excel, PowerPoint, Publisher,
Access, Visio and Project.
Open and create multiple documents in
new tabs of the same window, rather than
in new windows.
Increases your productivity by 50%, and
reduces hundreds of mouse clicks for you
every day!
Read More... Free
Download... Purchase...
7 Comments Login
Sort by Newest !
Say something here...
John john - 11 months ago
YEAHHHHHHHHH. At last something that works! Gj!
Reply
CABM - 1 years ago
I need to delete all text in a cell EXCEPT a specific
set of words. For example, a list of fruits or
vegetables in column A, and column B has a
description of the vegetables, including color, and I
want to remove all text in the cells of column B
except for color words. Any ideas?
Reply
ed - 1 years ago
hi, i want to ask if theres a formula to add period on
every abbreviation in one cell within a sentence?
example
825 apple rd st #5 - i want to add period on "rd" and
"st" to make it formal
hope you can help me
thanks
Reply
kellytte - 1 years ago
Hi ed,
The Add Text feature of Kutools for Excel
may help you solve the problem:
https://www.extendoffice.com/product/kutools-
for-excel/excel-add-same-text-to-multiple-
cells.html
Reply
Formatunknown - 1 years ago
What if you want to preserve the format of the
number or has decimals? i.e Apple30.01? for answer
of 30.01? your vb drops the spot?
Reply
kellytte - 1 years ago
Hi Formatunknown,
In this situation, Kutools for Excel’s Remove
Characters feature is recommended.
Select the cells, enable the Remove
Characters feature, only check the Alpha
option, and click the Ok button to remove all
alpha characters, and remain numbers
including the decimal places.
doc-comments-
3244.png
Reply
Abi DC - 2 years ago
Thank u so much
Reply
# Home $ Knowledge % Support
& Forum ' Privacy Policy
( About Us
Copyright © 2009 - 2020
Follow us
ExtendOffice.com | All Rights
Reserved. # Sitemap
Microsoft and the Office logo
are trademarks or registered trademarks of
Microsoft Corporation in the United States
and/or other countries.
