Extracting an integer from a string

[Derek_35]

A user enters a value in the form of O or U followed by a number. ie) O10, U9.5, etc.

With the following code I am not getting any decimal places. For the entered value 'U9.5' I am getting the value equal to 10.

Dim uValue As String
Dim Leftstring As String
Dim RightString As Integer

    uValue = Cells(row, Colu + 1)
    Leftstring = (Left(uValue, 1))
    RightString = Val(Right(uValue, Len(uValue) - 1))

For uValue = U9.5 I am getting :

Leftstring = 'U'
RightString = 10 ** I want RightString = 9.5

Any help?

[NateO]

Try the mid (you don't want an integer (which is a whole number)):

Dim uValue As String, RightString

    uValue = Cells(row, Colu + 1)

    RightString = Val(mid(uValue, 2,255)

What are you doing with your variable? If you're placing it in a formatted cell, check the formatting, make sure it's supporting decimals, the variable may store decimals while XL is creating an illusion to the contrary.
_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-14 21:05 ]

[Derek_35]

Thanks for the reply...

I changed RighString to an integer, removed the right function & inserted the mid function. However, I am still receiving the rounded number.

-------------

I am comparing the number vs a number in another cell. The O & U stand for Over & Under. So if its O13 and in cell(x,x) = 12. I compare the 12 to the 13.

[ This Message was edited by: Derek_35 on 2002-05-14 21:08 ]

[NateO]

Sorry, having some edit problems. Are you using this number in more procedures or placing it in a cell?

[Derek_35]

RightString is never placed into a cell, but is used later in the function.

[ This Message was edited by: Derek_35 on 2002-05-14 21:11 ]

[NateO]

If you're not using option explicit you should be able to store the number without defining it, while using the mid, no val code necessary.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 21:13 ]

[Derek_35]

Bingo! Thanks so much for your help, it is much appreciated...

-Derek

[Jay Petrulis]

On 2002-05-14 20:37, Derek_35 wrote:
A user enters a value in the form of O or U followed by a number. ie) O10, U9.5, etc.

With the following code I am not getting any decimal places. For the entered value 'U9.5' I am getting the value equal to 10.

Dim uValue As String
Dim Leftstring As String
Dim RightString As Integer

    uValue = Cells(row, Colu + 1)
    Leftstring = (Left(uValue, 1))
    RightString = Val(Right(uValue, Len(uValue) - 1))

For uValue = U9.5 I am getting :

Leftstring = 'U'
RightString = 10 ** I want RightString = 9.5

Any help?

Be careful with using "Row" as the variable. You may want to change that to something less ambiguous, as that means something in VBA.

I cannot determine why you are unable to retrieve whatever is left after you remove the first character.

Try the following. Please amend your code as follows,

Dim RightString as Double

uValue = Trim(WorksheetFunction.Clean(Cells(lRow, Colu + 1)))

RightString = Right(uValue, Len(uValue) - 1)

You may want to coerce it into a double, too.

RightString = CDbl(Right(uValue, Len(uValue) - 1))

Also, is there something at the end, like another period, perhaps? Anything which would cause the problem? I'm stumped if this doesn't work for you.

HTH,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-14 21:27 ]