Format/parse a string in Vb

Question

I am working with some input that is in the possible forms

$1,200
20 cents/ inch
$10

Is there a way to parse these to numbers in VB? Also printing these numbers?

EDIT: Regular expressions would be great.

EDIT: VB 6 in particular

Answer 1

Ehh...Assuming all that you want is the digits, I would use a Regular Expression to simply replace any non-digit with "".

You would need to reference Microsoft VBScript Regular Expressions in your project. Then let's say that your text was in a variable called strTest. Here is some off the cuff untested code:

Dim oRE As Object
Set oRE = New VBScript_RegExp.RegExp
oRe.Pattern = "\D"
strTest = oRE.Replace(strTest, "")

Answer 2

You mean VB6/VBA or VB.net? In VB6 or VBA you can do CLng() Or CDec() depending on what type of number you want. They will deal with the beginning dollar sign and commas just fine. For dealing with the 20 cents / inch you should probably do something like CLng(split("20 cents / inch", " ")(0)) These functions throw a type conversion error if they can't convert. You can trap that and try converting it another way if they fail.

There is also a function called Val which returns the numeric portion. It doesn't generate type conversion errors like CDec. But it also doesn't handle any non-numeric input. It gives you the decimal value of the digits in a string as long as it starts with a digit. It ignores any trailing characters.

Answer 3

Sometimes it just takes brute force!

Here is a routine that takes in a string with numbers and returns a number properly handled with fractions, M or B suffixes, and more. You can modify i to handle any special condition or text algebra (miles / hour, cents / inch, etc)

This is taken from one of our production applications thus the line numbers which we use in our error handler (ERHandler), as well as the standard exit routine.

Function GetNumberFromString(s As String) As Currency
12800 Const ProcID = "GetNumberFromString"
12810 Dim c                                   As String
12820 Dim d                                   As Integer
12830 Dim Denominator                         As Double ' currency only handles 4 places
12840 Dim HaveDec                             As Boolean
12850 Dim HaveSlash                           As Boolean
12860 Dim HaveSpace                           As Boolean
12870 Dim i                                   As Integer
12880 Dim LenV                                As Integer
12890 Dim NegMult                             As Integer
12900 Dim Numerator                           As Currency
12910 Dim TempVal                             As Currency
12920 Dim v                                   As String

      'Provides the functionality of VAL, but handles commas, fractions
      ' also million and billion

12930 On Error GoTo ErrLbl
12940 oLog.LogProcEntry ModuleID, ProcID, "v=" & v

12950 v = Trim(s)
12960 LenV = Len(v)
12970 If LenV = 0 Then
12980     GetNumberFromString = 0
12990     GoTo ExitProc
13000 End If
13010 TempVal = 0
13020 d = 0
13030 NegMult = 1
      '
13040 For i = 1 To LenV
13050     c = Mid(v, i, 1)
13060     Select Case c
          Case "0" To "9"
13070         If HaveSpace Then
13080             If Not HaveSlash Then
13090                 Numerator = 10 * Numerator + Asc(c) - 48
13100             Else
13110                 Denominator = 10 * Denominator + Asc(c) - 48
13120             End If
13130         ElseIf Not HaveDec Then
13140             TempVal = 10 * TempVal + Asc(c) - 48
13150         Else
13160             TempVal = TempVal + ((Asc(c) - 48)) / (10 ^ d)
13170             d = d + 1
13180         End If
13190     Case ",", "$"
          ' do nothing
13200     Case "-" 'let handle negatives ns 12/20/96
13210         NegMult = -1 * NegMult
13220     Case "(" 'let handle negatives mt 6/9/99
13230         NegMult = -1 * NegMult
13240     Case "."
13250         HaveDec = True
13260         d = 1
13270     Case " "
13280         HaveSpace = True
13290         d = 1
13300     Case "/"
13310         HaveSlash = True
13320         If Not HaveSpace Then
13330             HaveSpace = True
13340             Numerator = TempVal
13350             TempVal = 0
13360         End If
13370     Case "b", "B"
13380         If UCase(Mid(v, i, 7)) = "BILLION" Then
13390             TempVal = TempVal * 1000000000#
13400             Exit For
13410         End If
13420     Case "m", "M"
13430         If UCase(Mid(v, i, 7)) = "MILLION" Then
13440             TempVal = TempVal * 1000000#
13450             Exit For
13460         End If
13470     Case Else
          ' ignore character/error
13480     End Select
13490 Next i

13500 If HaveSlash And Denominator <> 0 Then
13510     TempVal = TempVal + Numerator / Denominator
13520 End If

13530 GetNumberFromString = TempVal * NegMult

ExitProc:
13540 oLog.LogProcExit ModuleID, ProcID
13550 Exit Function

ErrLbl:
13560 Debug.Print Err.Description, Err.Number
13570 Debug.Assert False
13580 ERHandler ModuleID, ProcID
13590 Resume
End Function