Formulas are key in getting things done in excel. One can apply formulas to manipulate texts, work with dates and time, count and sum with criteria, create dynamic ranges, and dynamically rank values. Explained below are formulas one can apply to remove characters in excel cells.
1. The Array Formula
a) Select a bank cell you will return the text string without the letters.
Enter the formula;
=SUM (MID (0&A2, LARGE (INDEX(IS NUMBER(–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 all at the same time.
b) Keep selecting the cell and then drag its fill handle to the range as you wish. You will now see all letters removed from the original text strings.
N/B: This formula removes all kinds of characters except numeric characters. If there's no number in the text string, this array formula will return zero.
2) User of Defined Functions
a) Press Alt + F11 keys simultaneously to open the Microsoft Visual for the app window.
b) Click insert> module and then copy and paste the following;
Function strip Char (Txt as String) As String
With Create Object ("VBScript.RegExp")
Global = True
Pattern = "\D"
StripChar =. Replace (Txt, ")
c) The user-defined function is then saved. A blank cell is selected where a text without strings is returned. Later, the Fill handle s dragged down to the ranges after entering the =Strip Char (A2) formula.
N/B: This function can also remove all kinds of characters except numeric characters and return numbers stored as text strings.
3) Excel Left Function
(EXCEL LEFT) function enables left side extraction of characters in given text. For instance, LEFT ("apple", 3) returns "app"
4) Kutools for Excel
All characters can be removed by the methods mentioned above
Except for the numeric characters. The Kool method is applied where one only needs to remove letters from the text and remain with numeric characters. This method will introduce Kutools is essential in removing characters utility in Excel
For it's for easiness.
a) Select the cells you will remove letters from, then click Kutools>text> Remove characters.
b) In the opening remove characters dialog box, check the Alpha option and then click the OK button.
You'll see only letters are removed from select cells.
N/B: If you 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 character dialog box.
5) Extract Numbers Function of Kutools For Excel.
a) Select a blank cell, you will return the text string without letters and click Kutools>functions> texts> text> EXTRACTNUMBERS.
b) Specify the cells to which letters should be removed and replaced in the TEXT box. The specification should be done in the opening dialog box. The TRUE or FALSE is not compulsory. I typed into the N box and clicked the OK button.
c) Keep selecting the cell and drag the fill handle to the range you need. You'll see all letters removed from the original text strings.