Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Find Numbers In Text Strings

1. Member
Join Date
18th July 2006
Posts
21

## Find Numbers In Text Strings

I have several cells in a column that look something like this:

Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s

etc.

Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?

- Steve

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Finding The First Number In A Text String

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Finding The First Number In A Text String

Hi,

In B1 and copied down,

=LOOKUP(9.9999999E+307,CHOOSE({1,2},0,LEFT(REPLACE(A1,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-1,""))+0))

Custom format the cell as

#;[=0]"No Numbers";

Edit: If there is a leading zero , then the formula won't work.

HTH
Last edited by Krishnakumar; November 21st, 2006 at 14:29.

4. Member
Join Date
18th July 2006
Posts
21

## Re: Finding The First Number In A Text String

JMan & Kris -

Thanks to both of you!

- Steve

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno