VBA extract percentage number

I need to extract the percentage number from cell A1.
The content in the cell varies, it could be "20% (Less applicable fees and taxes)", or pure number like 90%, or 100%. I will later use the number to perform some calculation.

My idea was to firstly get the position of %, so I can get the number.

However, if it is a pure number, I will not be able to get the position of the percentage mark because the value of range A1 is 0.9 or 1.

If it is not a pure number, I would be able to get the position of the percentage and extract the percentage number, but it is a string, I can't use it to perform calculation.

Can anyone give me an idea? thank you very much in advance!

You can get the cell string value with Range.Text.

Dim rg1 As Range
Dim rg2 As Range

Set rg1 = Range("A1")   '20% (Less applicable fees and taxes)
Set rg2 = Range("A2")   '90%

Debug.Print rg1.Value
Debug.Print rg2.Value

Debug.Print Val(Left(rg1.Text, InStr(1, rg1.Text, "%", vbBinaryCompare) - 1))
Debug.Print Val(Left(rg2.Text, InStr(1, rg2.Text, "%", vbBinaryCompare) - 1))

Output:

20% (Less applicable fees and taxes)
 0,9 

 20 
 90

You can use RegEx, which will allow you to extract just the numeric portion of whatever string.

Sub GetPercentageNumber()    

' RegEx variables
Dim RegNum As Object
Dim RegMatches As Variant
Dim myText As String
Dim GetPercentageNumber As Double


myText = "20% (Less applicable fees and taxes)"  ' <-- for testing purposes

' Late binding of setting RegEx object
Set RegNum = CreateObject("VBScript.RegExp")
   
' to remove all non-digits
With RegNum
    .Pattern = "(\d+\.\d+)" ' Match any decimal value
    .Global = True
End With
              
' check if a cell with a decimal value
Set RegMatches = RegNum.Execute(myText)
If RegMatches.Count >= 1 Then ' make sure there is at least 1 match
    GetPercentageNumber = RegMatches(RegMatches.Count - 1)

Else ' failed to retrieve a decimal value --> check if a String with a %
    ' to remove all non digits
    With RegNum
        .Pattern = "\d{1,9}" ' Match any set of 1 to 9 digits
        .Global = True
    End With
    
    Set RegMatches = RegNum.Execute(myText)
    If RegMatches.Count >= 1 Then ' make sure there is at least 1 match
        GetPercentageNumber = RegMatches(RegMatches.Count - 1)
    End If
End If
  
End Sub

I was able to do this using the cell equation =IF(ISNUMBER(A1),A1,VALUE(LEFT(A1,FIND("%",A1,1))))

The trick is the use ISNUMBER() to find out if the contents are numeric or not.

In VBA you can use the IsNumeric() function to do the same.