Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Get column name from index in Excel Table
  • Working With Tables in Excel
  • Number and Text Filters Examples in Excel
  • Chart Axes in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Count rows with at least n matching values
  • How to use Excel MMULT Function
  • Find closest match in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation whole percentage only
  • Excel Data validation no punctuation
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list

How to strip numeric characters from cell in Excel

by

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016.

Formula

{=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,
ROW(INDIRECT("1:100")),1)+0),
MID(A1,ROW(INDIRECT("1:100")),1),""))}

Explanation

In the example shown, the formula in C5 is:

=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,
ROW(INDIRECT("1:100")),1)+0),
MID(B5,ROW(INDIRECT("1:100")),1),""))

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

Working from the inside out, the MID formula is used to extract the text in B5, one character at a time.  The key is the ROW/INDIRECT piece:

ROW(INDIRECT("1:100"))

which spins up an array containing 100 numbers like this:

{1,2,3,4,5,6,7,8….99,100}

Note: 100 represents the maximum characters to process. Change to suit your data.

This array goes into the MID function as the start_num argument. For num_chars, we use 1.

The MID function returns an array like this:

{“3″;”4″;”6″;”5″;”3″;” “;”J”;”i”;”m”;” “;”M”;

“c”;”D”;”o”;”n”;”a”;”l”;”d”;””;””;””;…}

(extra items in the array removed for readability)

To this array, we add zero. This is a simple trick that forces Excel to try and coerce text to a number. Numeric text values like “1”,”2″,”3″,”4″ etc. are converted, while non-numeric values fail and throw a #VALUE error. We use the IF and ISERR functions to catch these errors. If get an error we know we have a non-numeric character, so we use another MIN function to bring it into our array:

MID(B5,ROW(INDIRECT("1:100")),1)

If don’t get an error, we know we have a number, so we just insert an empty string (“”) into the array.

The final array result goes into the TEXTJOIN function as the text1 argument. For delimiter, we use an empty string (“”) and for ignore_empty we supply TRUE. TEXTJOIN then concatenates all non-empty values in the array and returns the result.

More precise array length

If it bothers you to hard-code a value like 100 into INDIRECT, you can use the LEN function inside INDIRECT to more intelligently process only the actually number of characters in the cell.

MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

Removing extra space

When you strip numeric characters, you may have extra space characters left over. To strip leading and trailing spaces, and normalize spaces between words, you can wrap the formula shown on this page inside the TRIM function:

=TRIM(formula)

Post navigation

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function Examples in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • How to use Excel AND Function
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert Excel time to decimal minutes
  • Extract time from a date and time in Excel
  • Get first Monday before any date in Excel
  • Convert text to date in Excel
  • Get month name from date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Group times into unequal buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel

General

  • Excel Operators
  • How to fill cell ranges with random text values in Excel
  • Using Existing Templates in Excel
  • How to calculate percentage of total in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning