Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Find Numbers In Text Strings

  1. #1
    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?

    Thanks in advance!

    - Steve

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2005
    Posts
    894

  3. #3
    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. #4
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Color And Bold Single Text Strings Within Cell Text Strings
    By ExcelJunkie in forum TIP, TRICKS & CODE
    Replies: 10
    Last Post: June 1st, 2015, 05:26
  2. Extract Specific Numbers From Text Strings
    By Gatsby434 in forum Excel General
    Replies: 3
    Last Post: June 26th, 2008, 17:04
  3. Replies: 8
    Last Post: June 23rd, 2008, 10:42
  4. Compare text strings in macro with strings in WS
    By mprija in forum Excel General
    Replies: 1
    Last Post: June 21st, 2006, 22:40
  5. Replies: 10
    Last Post: March 3rd, 2006, 18:49

Bookmarks

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