Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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 60-day!
ad split cell separate text numbers

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


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.
doc remove letters from text 1

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.


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

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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 Excel tools. Full feature free trial 60-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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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:
doc remove letters from text 6
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:
doc remove letters from text 7

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: remove letters from strings/numbers/cells in Excel

 

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Related articles:


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ed · 2 months 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
  • To post as a guest, your comment is unpublished.
    Formatunknown · 4 months 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?
    • To post as a guest, your comment is unpublished.
      kellytte · 3 months 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.
  • To post as a guest, your comment is unpublished.
    Abi DC · 1 years ago
    Thank u so much