Converting Text filed into number field problem

ktovar

New Member
Joined
Mar 14, 2009
Messages
6
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:confused:
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Kevin

Why not try one of the other type conversion functions? eg CDec, CDbl etc
 
Upvote 0
Well, just learning the functions. Let me review what you are recommending and I will let you know how it goes.

Thanks
Kevin;)
 
Upvote 0
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!
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0
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;)
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top