# Converting Text filed into number field problem

#### ktovar

##### New Member
One of the fields in the excel file that I have linked to is a text field representing numbers. For instance:

1,000.0000000 Text Value Desired number value 1000
1.0000000 Text Value Desired number value 1
1.0050000 Text value Desired number value 1.005

I have tried to use the Val vunction in a querry, but since the 1,000.000000 has a comma right after the 1, the function is returning a value of 1 instead of a value of 1000.

Can come one please let me know how to convert text numbers into values when there are both "," and "." in the string??
Kevin

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Norie

##### Well-known Member
Kevin

Why not try one of the other type conversion functions? eg CDec, CDbl etc

#### ktovar

##### New Member
Well, just learning the functions. Let me review what you are recommending and I will let you know how it goes.

Thanks
Kevin

#### ktovar

##### New Member
Well Norie, I have read what the function CDec does and I think it is what I need, however, I am having difficulity using the function. I keep getting an error message saying "The expression you entered has a function containing the wrong number of arguments". I know I am typing it in worng but can't seem to figure it out. My field name is "TDivisor" and I have a blank column in design view next to the field trying to create an identical number column next to it with no luck. Is it possible for you to type the function with the field name and send it to me?

Thanks for the help!

#### Norie

##### Well-known Member
Kevin

How exactly are you using the function?

Can you post the expression it's in?

I rarely, if ever, use CDec but as far as I know it only takes 1 argument - the one you want to convert.

In fact as far as I know none of the type conversion functions take more than 1 argument.

#### ktovar

##### New Member
This is the expression

Divisor= CDec (QTPFR)

QTPFR is the field that is a text (numeric value). I have never used expressions before to convert data, so I think I am typing it in wrong. Any help is relly appriciated!

By the way, what time is it out in the Pacific! It's so cool to chat so far away! I am in So. California trying to learn Access.

Kevin

#### ktovar

##### New Member
Norie, great news, I got CInt to work. Everything seems OK now (I hope)

Kevin

#### Norie

##### Well-known Member
Kevin

Where/how are you actually trying to use this?

If it's in a query then just type in CDec([QTPFR]) on the field row.

Access will probably convert it to something like this, Expr1:CDec([QTPFR]).

Now you could just change Expr1 to Divisor, but you might not even need to use such an expression.

I've no idea what you are trying to do or how your data is structured, but let's say you have 2 fields Amount and QTPFR.

If you wanted to divide Amount by the converted value from QTPFR then you could probably use something like this.

[Amount]/CDec([QTPFR])

Again Access will probably convert that to Expr1 but you can change that something more significant.

#### ktovar

##### New Member
Well, Norie, I thought I would let you know how I fixed the problem in the end. I never could get "CDec" function to work so I utilized the replace function to replae the "," with "" and then utilized the Val function. The Val function seems to handle functions just fine, it just can't handle the ",". Thanks for all the help!

Kevin Tovar
So. California

Replies
22
Views
599
Replies
5
Views
2K
Replies
11
Views
686
Replies
5
Views
369
Replies
7
Views
2K

1,190,691
Messages
5,982,314
Members
439,772
Latest member
KimPhoenixT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back