Announcement

Collapse
No announcement yet.

Find Numbers In Text Strings

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

    Thanks in advance!

    - Steve

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

    http://www.ozgrid.com/VBA/ExtractNum.htm

    Comment


    • #3
      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, 14:29.
      Kris

      ExcelFox

      Comment


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

        JMan & Kris -

        Thanks to both of you!

        - Steve

        Comment

        Working...
        X