RegEx UDF to Convert 38R2 into 38.2

jonny · Jun 23rd 2020

Guys, need your help with writing a UDF that gets a string and convert it into numeric value.

Rules:

R => "."

K => multiply by 1000

M => multiply by 1000000

Examples:

38R2 = 38.2

45K2 = 45200

1M5 = 1500000

**royUK** · Jun 23rd 2020

Are you sure that you want a UDF? Your multipliers won't return the results that you have in your post.

Maybe something like this

Code

Sub CONVERTNUMERIC()
Dim rCl As Range
For Each rCl In Selection.Cells
If InStr(1, rCl, "R") > 0 Then rCl = Replace(rCl, "R", ".")
If InStr(1, rCl, "K") > 0 Then rCl = Replace(rCl, "K", "") * 100
If InStr(1, rCl, "M") > 0 Then rCl = Replace(rCl, "M", "") * 100000
Next rCl
End Sub

jonny · Jun 24th 2020

Thanks, RoyUK, much appreciate it!

Turned your code into UDF:

Code

Function CONVERTNUMERIC(rCl String) As Double
            If InStr(1, rCl , "R") > 0 Then CONVERTNUMERIC= Replace(rCl , "R", ".")
            If InStr(1, rCl , "K") > 0 Then CONVERTNUMERIC= Replace(rCl , "K", "") * 100
            If InStr(1, rCl , "M") > 0 Then CONVERTNUMERIC= Replace(rCl , "M", "") * 100000
End Function

**royUK** · Jun 24th 2020

I wasn't sure how you intended using a UDF

RegEx UDF to Convert 38R2 into 38.2

Participate now!

Share