Excel import scientific format

[Maximnl]

I just stucked.
It seems to be an excel weird feature but it should be well known.

How to import numeric data from excel column if the column has the following format: 600.000.055

The problem is that although the data is numeric it comes into access as a text but then the dots can be unpredictable (one but sumetimes two).

I will appreciate you help.

 

[Brianwarnock]

600.000.055
Displays as text in the cell in Excel, ie it is left justified, and an attempt at arithmetic meets with a value error.
Scientific notation would display in the cell as 6.0E+05 and in the formula bar as 600000.055

Brian

 

[Maximnl]

I just stucked.
It seems to be an excel weird feature but it should be well known.

How to import numeric data from excel column if the column has the following format: 600.000.055

The problem is that although the data is numeric it comes into access as a text but then the dots can be unpredictable (one but sumetimes two).

I will appreciate you help.

Yes, what I mean is how to convet text 600.000.055 into a number 600000055

 

[Brianwarnock]

Presumeably you wish to do this in Access.
Use rhe Replace function to replace all "." with "", then a convert function to convert the string to a number Cint Cdbl, read Access help.

Brian

 

[Maximnl]

Presumeably you wish to do this in Access.
Use rhe Replace function to replace all "." with "", then a convert function to convert the string to a number Cint Cdbl, read Access help.

Brian

Hallo Brian,

thanks, it workst just fine. This is the final function
Artikel_num: CDbl(Replace(Nz([artikelnummer];"0");".";""))

Cdbl is better because it can manage long number. Nz will manage missing values.

Regards
Maximnl

http://4suc6.com

 

[Brianwarnock]

Glad to help, its good when the original poster doesn't need their hand holding all the way.

Brian