CODEWITHSUNDEEP
excelvbaif-statement
Dullspark
Dullspark

Reputation: 215

Excel VBA - Take only the numeric value in a cell

I have an IF function in VBA that has a mathematical formula in it:- (Cells(i3, 10).Value > 30)

The issue i'm having is that the values in column 10 are all alphanumeric. For example they're all " 1 - Hard", "2 - Moderate", "3 - Easy".

Is there anyway to make VBA only look at the number so when it's testing if the value is more than 30, it actually works?

Thanks

Upvotes: 1

Views: 6972

Answers (3)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

You can get the leading numeric characters from a string using the VBA Val function.

To use this function on a worksheet you will need to create a User Defined Function (UDF) in a standard VBA module.

Function LeadingNumbers(Str As String) As Double
    LeadingNumbers = Val(Str)
End Function

So in your context use (LeadingNumbers(Cells(i3, 10).Value) > 30).

Upvotes: 0

FunThomas
FunThomas

Reputation: 29286

The val function should do what you need. It returns the number at the left of a string until a non-number character is found (0 if string contains no number at the left)

debug.print val("1 - Hard")   ' prints 1
debug.print val("  31 - Something else")   ' prints 31
debug.print val("123abc")   ' prints 123
debug.print val("x  2 - You name it")   ' prints 0
debug.print val("-31.5.3 - Negative")   ' prints -31.5

So just change your if:

 if val(Cells(i3, 10).Value) > 30 then

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

Get the left part of the string (before the dash), trim it and convert to INT.

'item = your cell value
CInt(Trim(Left(item, InStr(1, item, "-") - 1)))

Upvotes: 1

Related Questions