We help IT Professionals succeed at work.

converting text to number

tommytran99 asked
what's the formula for converting text to number in a report? query? thanks
Watch Question

hi tommytran99,

CInt   is the convertor


Dim str, str2 As String
str2 = "200"
str = "222"
MsgBox CInt(str) + CInt(str2)


If you mean like "twenty" to 20, you might try MVPS to see if anyone has that soluiton.

If you mean like 20f to 20, you use the VAL() function.
Here is the opposite formula, maybe it will help.



what's the difference between the Val function and the Clnt?


what's the difference between the Val function and the Clnt?


what's the difference between the Val function and the Clnt?
The CInt will return a number from a string if the string is a valid number. Here are some examples:

CInt("20") will return 20
CInt("20f") will give a type mismatch error
CInt("f20") will give a type mismatch error

The Val function will return a number from inside a string as long as the number is the first character in the string.
Here are some examples:

Val("20") will return 20
Val("20f") will return 20
Val("f20") will return 0
Val("20 + 20f20") will return 20


Val converts text with a "number" and text into the first "number" it can find.  Things like f47, 235zx, etc.

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes &O (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are stripped from the argument.

The following returns the value 1615198:

Val("    1615 198th Street N.E.")

In the code below, Val returns the decimal value -1 for the hexadecimal value shown:


Note   The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators can be used, for example, in international applications, use CDbl instead to convert a string to a number.
Cint() creates the numeric integer portion of a text string or number.

As Joe indicated, my example of f20 is erroneous, only 20f would be recognized.

If the table field is defined as TEXT, you can still do math with that field if the data "looks" like a number (ie 37.5).

[TEXT] * 2 = 75

if the field looks like text (twenty-two), you will get an error.

Hope this all helps .... whatever you are trying to do.


I agree with most of what you stated about the Val function. One point though, if in a string the first character is not one it recognizes to be a number then it will not return the number after that character. For example, f47 will not return 47. It will return 0.



I didn't see your last statement appear before I responded. Sorry about that.



Explore More ContentExplore courses, solutions, and other research materials related to this topic.