Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Finding The First Number In A String

  1. #1
    Join Date
    8th April 2007
    Posts
    3

    Finding The First Number In A String

    Hello all!

    I am a major noob to all of this and I hope this question isn't one that has been asked multiple times already. I searched through these forums, but was unable to find an answer.

    We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:

    #BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

    We want only to find the FIRST NUMBER in this string, so the result should be 7. Any help you could give us on this would be greatly appreciated! Thanks in advance!
    Last edited by Brendenguy; April 8th, 2007 at 09:56.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th August 2004
    Posts
    56

    Re: Finding The First Number In A String

    Try this, using the ISNUMERIC function in VBA

    VB:
    Sub FindNumeric() 
         
        Dim str As String 
        Dim i As Integer 
         
        str = Cells(1, 1).Value 
         
        For i = 1 To Len(str) 
             
            If IsNumeric(Mid(str, i, 1)) Then 
                 
                Debug.Print Mid(str, i, 1) 
                 
            End If 
             
        Next i 
         
         
    End Sub 
    
    
    Only Smarties have the answer


  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,404

    Re: Finding The First Number In A String

    #BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

    UDF
    use in cell like

    =Brenden(A1) as text output
    =Brenden(A1)*1 as number output
    VB:
    Function Brenden(txt As String) As String 
        With CreateObject("VBScript.RegExp") 
            .Pattern = "\d" 
            Brenden = .execute(txt)(0) 
        End With 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    5th April 2007
    Location
    Fort Collins Colorado
    Posts
    103

    Re: Finding The First Number In A String

    Macro is the way to go and there are a couple of good examples posted here for you.

    If you don't want to tackle a macro for any reason, the following highly inelegant (LOL) Excel formula will evaluate the first 5 characters for a numeric value and return the first one it finds. It retuns "nope" if no number within 5 characters.

    You would neeed to replace "C6" with the cell you wish to evaluate.

    =IF(ISNUMBER(VALUE((MID(C6,1,1)))),VALUE((MID(C6,1,1))),IF(ISNUMBER(VALUE((MID(C6,2,1)))),VALUE((MID(C6,2,1))),IF(ISNUMBER(VALUE((MID(C6,3,1)))),VALUE((MID(C6,3,1))),IF(ISNUMBER(VALUE((MID(C6,4,1)))),VALUE((MID(C6,4,1))),IF(ISNUMBER(VALUE((MID(C6,5,1)))),VALUE((MID(C6,5,1))),"nope")))))
    Last edited by ByTheCringe2; April 8th, 2007 at 14:16.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,287

    Re: Finding The First Number In A String

    Try...

    =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1)

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Finding The First Number In A String

    Todkerr, Please don't put code tags round formulas, only round VBA code.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    21st February 2013
    Posts
    1

    Re: Finding The First Number In A String

    Quote Originally Posted by Domenic View Post
    Try...

    =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1)

    Hope this helps!
    This worked perfectly for an application I have as well - but why do you need A1&"0123456789"? I can't figure out what it's doing (though it doesn't work without it).

    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. Replies: 13
    Last Post: August 25th, 2006, 23:46
  2. Replies: 5
    Last Post: November 19th, 2005, 07:04
  3. Replies: 1
    Last Post: February 18th, 2005, 00:29
  4. Formulas: finding text within a string - if statement
    By garethwhieldon in forum EXCEL HELP
    Replies: 2
    Last Post: July 2nd, 2003, 06:03
  5. [Solved] VBA: Finding a String
    By peach in forum EXCEL HELP
    Replies: 3
    Last Post: June 18th, 2003, 17:52

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