Table of Contents
Using Microsoft Excel, you can easily find the first number in a text string by using the FIND function. This function allows you to search for a specific character or string within a cell and returns the position of the first occurrence of that character or string. By combining this function with the LEFT function, which extracts a specific number of characters from the beginning of a string, you can retrieve the first number in a text string. This method is useful for organizing and analyzing data, especially in large datasets with mixed alphanumeric entries.
Excel: Find First Number in Text String
You can use the following formulas in Excel to find the first number in a text string:
Formula 1: Return Position of First Number
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
This formula returns the position of the first number in a string.
For example, if the string is A0095B then this formula will return 2 since this is the position in the string where the first number occurs.
Formula 2: Return Value of First Number
=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1)
This formula returns the value of the first number in a string.
For example, if the string is A0095B then this formula will return 0 since this is the value of the first number that occurs in the string.
The following example shows how to use each formula in practice with the following list of employee ID strings in Excel:
Example 1: Return Position of First Number
We can type the following formula into cell B2 to return the position of the first number in each Employee ID text string:
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
We can then click and drag this formula down to each remaining cell in column B:
Column B returns the position of the first number in each corresponding string in column A.
- The first number in A0095B occurs in position 2 of the string.
- The first number in 43387BR occurs in position 1 of the string.
- The first number in BCDD7D occurs in position 5 of the string.
And so on.
Example 2: Return Value of First Number
We can type the following formula into cell B2 to return the value of the first number in each Employee ID text string:
=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1)
We can then click and drag this formula down to each remaining cell in column B:
Column B returns the value of the first number in each corresponding string in column A.
For example:
- The value of the first number in A0095B is 0.
- The value of the first number in 43387BR is 4.
- The value of the first number in BCDD7D is 7.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: