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

Data Analysis

  • Conditional Formatting Data bars Examples in Excel
  • Get column index in Excel Table
  • How to combine 2 or more chart types in a single chart in Excel
  • How to create Gauge Chart in Excel
  • How To Compare Two Lists in Excel

References

  • Count unique text values with criteria
  • How to get relative row numbers in a range in Excel
  • Approximate match with multiple criteria in Excel
  • Left Lookup in Excel
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in specific year

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:

Worked Example:   How to Check If A Cell Contains Specific Text in Excel

{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.

Worked Example:   How to translate letters to numbers in Excel

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.

Worked Example:   Join Text Strings Using Concatenate or '& Operator' in Excel

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

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

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

  • How to return blank in place of #DIV/0! error in Excel
  • OR function Examples in Excel
  • How to use Excel AND Function
  • Nested IF function example in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate working days left in month in Excel
  • Get days before a date in Excel
  • Extract date from a date and time in Excel
  • Convert decimal hours to Excel time
  • How to enter Today’s Date or Static Date and Time in Excel

Grouping

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

General

  • Excel Default Templates
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • How to create dynamic worksheet reference in Excel
  • Check if multiple cells have same value with case sensitive in Excel
  • Convert column number to letter in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning