# 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

Is the number to extract the one between : and [

Triumph without peril brings no glory: Just try

• Re: Extract Only First Number In String

Yes

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

Triumph without peril brings no glory: Just try

• 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

Perhaps make a try with the file attached or/and send you file

## Files

Triumph without peril brings no glory: Just try

• Re: Extract Only First Number In String

Ok I see it now. I thank you much sir.

• Re: Extract Only First Number In String

Hi

Given those values in a cell here is a potential native formula solution:

=LOOKUP(9.999E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0,"-"},A1&"1234567890-")),ROW(INDIRECT("1:"&LEN(A1)))))

Richard

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