Filtering out the percent symbol

Question

I'm using Excel 2010 and I have some code that prompts the user for a percentage then increases the value in cell A1 by that percentage. It works fine if someone plays by the rules and types an integer as a percentage. So, for example, if someone wanted the value to increase by 30%, they would type a 30 into the input box.

If someone adds a percent symbol in their response it wrecks the entire thing. How can I modify this code so it traps a percent symbol out and does the calculation based on the integer alone. Thank you.

Sub Box1()
    Dim myPercent As Integer
    myPercent = InputBox("How much of an increase?", "Input without the percent symbol")
    Range("A1").Value = Range("A1").Value * (1 + (myPercent * 0.01))
End Sub

Michael Liu · Accepted Answer · 2014-04-08T14:05:15.317

3

You can use the Replace function to strip out any percent signs:

myPercent = Replace(InputBox("How much of an increase?", "Input without the percent symbol"), "%", "")

Or you can use the Val function to ignore everything after the first non-numeric character:

myPercent = Val(InputBox("How much of an increase?", "Input without the percent symbol"))

edited Apr 08 '14 at 14:05

answered Apr 08 '14 at 13:37

Michael Liu

52,147
13
117
150

Clever approach indeed. – L42 Apr 08 '14 at 14:07

L42 · Answer 2 · 2014-04-08T13:59:30.773

1

Non-regex solution :)

Sub box()

Dim mypercent

mypercent = InputBox("How much of an increase")
If mypercent Like "*[0-9]" Then
    Range("A1").Value = Range("A1").Value * (1 + (myPercent * 0.01))
Else
    MsgBox "Not valid input. Enter whole numbers only"
End If

End Sub

edited Apr 08 '14 at 13:59

answered Apr 08 '14 at 13:49

L42

19,427
11
44
68

score 0 · Answer 3 · answered Apr 08 '14 at 18:29

Also try this code:

Dim myPercent
Do
    myPercent = InputBox("How much of an increase?", "Input without the percent symbol")
    If Not IsNumeric(myPercent) Then MsgBox "Wrong input! Try again.."
Loop Until IsNumeric(myPercent)
Range("A1").Value = Range("A1").Value * (1 + (myPercent * 0.01))

Filtering out the percent symbol

3 Answers3