Excel VBA - Take only the numeric value in a cell

Question

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

You will need to parse the string with Left() and Instr() to pull the number. — Scott Craner
– Scott Craner, Commented Jun 1, 2017 at 13:58
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) For cell A1. — M--
– M--, Commented Jun 1, 2017 at 14:12

FunThomas · Accepted Answer · 2017-06-01 14:23:07Z

2

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

answered Jun 1, 2017 at 14:23

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kostas K. · Accepted Answer · 2017-06-01 14:21:13Z

1

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

answered Jun 1, 2017 at 14:21

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Comments

Mark Fitzgerald · Accepted Answer · 2017-06-01 14:34:48Z

0

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

answered Jun 1, 2017 at 14:34

Mark Fitzgerald

3,0683 gold badges27 silver badges29 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Take only the numeric value in a cell

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related