CODEWITHSUNDEEP
excelvba
frisbeee
frisbeee

Reputation: 71

Extract numeric from an alphanumeric string

I have an array, and I'm trying to access an element at the loc1 + 4th index.

I get a type mismatch error.

Dim atype As Variant
Dim loc1 As Integer

atype = Worksheets("Inputs").Range("B21")

loc1 = InStr(atype, "Loan")
loanpct = atype(loc1 + 4)

My cell value is "Bond-61.87% Loan-38.13%", I want to extract "38.13%".

With the comment below, I understand that it is not an array. Should I try to convert the string to an array?

Upvotes: 0

Views: 54

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

To parse the string we use MID. The string is not an array with items that can be referenced like an array.

Dim atype As String
Dim loc1 As Integer
Dim loanpct As Double

atype = Worksheets("Inputs").Range("B21")

loc1 = InStr(atype, "Loan")
loanpct = Application.Evaluate(Mid(atype, loc1 + 5))

Debug.Print loanpct

Now loanpct is a number and can be used in later code.

But this assumes that everything after Loan- can be converted to a number. If there is any non numeric character after Loan- then it will fail.

Upvotes: 1

Related Questions