Extract Only First Number In String

  • Example:
    O: 14[-2]
    U: -11[4]


    Have tried the following code, and it returns the second number. I need the first number only, and it may range from 1 to 3 digits including negatives.



    Thanks for any help possible

  • Re: Extract Only First Number In String


    Perhaps try next code running with cells from A1 to A2

    Code
    Sub Macro2()
        For I = 1 To 2
            MyVALUE = Cells(I, "A")
            MyPos = InStr(4, MyVALUE, "[", 1)
            MyVALUE = Left(MyVALUE, MyPos - 1)
            MyVALUE = Right(MyVALUE, Len(MyVALUE) - 2)
            Cells(I, "B") = MyVALUE * 1
        Next I
    End Sub
  • Re: Extract Only First Number In String


    That looks good, and can see where it would work but:


    Code
    MyVALUE = Left(MyVALUE, MyPos - 1)


    keeps coming up with an error. I've tried using a Long variable to replace it, but that didn't work either.

  • Re: Extract Only First Number In String


    The VB function Val might be useful


    Val("-123[46]") = -123
    Val("43[-128]") = 43
    Put into a UDF (almost but not quite Excel's VALUE function)


    Code
    Function myVal(inputString as string) as Double
        myVal = Val(inputString)
    End Function


    Later: Oh!!! the string is "O: 43[-123]", in that case


    Code
    myVal = Val(Mid(inputString,3))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!