Excel: Extracting numbers from alphanumeric strings
Tuesday, December 25th, 2007This article was written by Ashish Mathur, a Microsoft MVP (Most Valuable Professional). Visit the Microsoft MVP Web site for more information.
In the following article I explain a formula that extracts numbers from strings in the following types of situations:
- When alphabetic and number characters are clustered together, such as
abc123or678sfr. - When alphabetic and number characters are not clustered together, such as
abc15tni.
Problem
How to extract the numeric portion of an alphanumeric string. For example: If cell A1 contains the string “abc123″, return the value 123 in cell B1.
Solution
The underlying logic here is to search for and return the first number in the alphanumeric string, and then return only the numbers that follow.
Popularity: 8% [?]
