Using excel right function

[fllopez65]

Hello
I've converted an ivt file to excel and need to trim some values(drop some zeros) in a column before exporting to access.
The cells in my column look like:
0001.01, I need 1.01
0001.02, I need 1.02
0017.01, I need 17.02
0220.01, I need 220.01

Assuming 0001.01 was cell A2. I believe using an excel right function such as =RIGHT(A2,4) would work but I don't know how to do the rest ie: 0017.01 etc...0220.01 etc...

Is it possible to construct 1 right function that would convert all the cells regardless of the original string length or does it have to be done individually?

Apparently an algorithm is supposed to work but I have no idea what an algorithm is or how to construct one to achieve the required results. I would prefer to stick with excel right functions if possible.
Any tips would be greatly appreciated.

fllopez65

 

[vbaInet]

If you import it into a Number field it will drop the leading zeros.

 

[fllopez65]

VbaInet
I was under the impression I had to fix the column before importing the whole spreadsheet into access. The spreadsheets with these "problem" columns are very large. Are you saving import this excel spreadsheet into access the way it is then fix it in access? Could explain a little further and the steps involved.

Tks
fllopez65

 

[vbaInet]

So you:

1. create a table and name the fields the same names as the Column Headings in your spreadsheet.
2. Set the data types of your fields accordingly so it matches with the type of data you are importing.
3. Ensure that the data type of this particular field is set to Number.
4. Use the import wizard and select the table you created in step 1 and set the Has Field Names option, then complete the import process.

As long as you've set that particular field's data type to Number, it will not include the leading zeros. Number types don't have leading zeros.

 

[fllopez65]

Tks vbaInet, will try your suggestions.