Announcement

Collapse
No announcement yet.

Finding The First Number In A String

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

  • 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, 09:56.

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

    Try this, using the ISNUMERIC function in VBA

    Code:
    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

    Comment


    • #3
      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
      Code:
      Function Brenden(txt As String) As String
      With CreateObject("VBScript.RegExp")
           .Pattern = "\d"
           Brenden = .execute(txt)(0)
      End With
      End Function

      Comment


      • #4
        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, 14:16.

        Comment


        • #5
          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!

          Comment


          • #6
            Re: Finding The First Number In A String

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

            Comment


            • #7


              Re: Finding The First Number In A String

              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).

              Comment

              Working...
              X