General discussion

Locked

Auto Format in Excel

By harpua__ ·
In our company our part numbers contain dashes and are very inconsistent. Our numbers range from 8410-10-04, 12-1034 to 4023. The problem I am having is I take a list of numbers from our ERP to Access then to Excel and I loose the integrity of ourpart number.

1. I have added spaces

2. When removing spaces Excel automatically formats the cells begignning with 10-2356 to Oct 56.

I have had success using Trim, then Concatenate. The only problem is this is 2 more steps than I really want to deal with.

Is there a way to force excel not to automatically change the formating of my cells?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Change Book1.xls

by Slacker In reply to Auto Format in Excel

Chnage the test formatting in your default Excel Template. Do this by going to the Book1 template and adjusting the column you want formatted. This will prevent it from reformatting in a way you dislike. Save the template of course.

Collapse -

I changed the default but...

by harpua__ In reply to Change Book1.xls

It still won't work because I copy/paste into the spreadsheet and also move tabs into the workbook. Once I do Any other ideas I can try?


8325
8410
8410-10-04
8410-10-06
8410-6
8420
8420-10-04
8420-10-06
8420-6
8430
8430-10-04
8430-10-06
8430-6
8450
8450-6
8470
8470-6

Collapse -

I changed the default but...

by harpua__ In reply to Change Book1.xls

It still won't work because I copy/paste into the spreadsheet and also move tabs into the workbook. Once I do Any other ideas I can try?


8325
8410
8410-10-04
8410-10-06
8410-6
8420
8420-10-04
8420-10-06
8420-6
8430
8430-10-04
8430-10-06
8430-6
8450
8450-6
8470
8470-6

Collapse -

Change the field setting of the database

by adamjedgar In reply to I changed the default but ...

if the database field is set as text rather than numeric you might find that excel won't have a problem as it will merely copy a text field to cell and format the cell as text!

Collapse -

I changed the default but...

by harpua__ In reply to Change Book1.xls

It still won't work because I copy/paste into the spreadsheet and also move tabs into the workbook.

Any other ideas I can try?

Here is an example of the part numbers.
As soon as I remove xtra spaces it changes to a date format on part numbers with a dash.

8325
8410
8410-10-04
8410-10-06
8410-6
8420
8420-10-04
8420-10-06
8420-6
8430
8430-10-04
8430-10-06
8430-6
8450
8450-6
8470
8470-6

Collapse -

Set up a Template

by adamjedgar In reply to Auto Format in Excel

Providing you don't have to perform any calculations on the part number rows in your spreadsheet, try creating a new template in excel which has the particular row in question preformatted as text.., this will force excel to maintain your part number integrity.

Your could also record a macro which runs when you import data from access that formats the row in any spreadsheet if you dont want to create a template! My experience in using access to automate excel operations when exporting/importing data has shown that this method is a little tricky...good luck!

Hope I've been of help

Cheers
Adam Edgar TAS Dept Davidson High School NSW Australia

Collapse -

Did i say Row??? I meant "Column" Also..

by adamjedgar In reply to Set up a Template

If you are importing data from another source, you should not be cutting and pasting.., especially when its from a database. Excel is excellent at running database queries for importing data. I would strongly suggest that you spend a half hour with a good IT Excel text and learn how to run database queries for importing data. It'll save you hundreds of hours worth the work in the coming days, months and years!!!

Collapse -

tried a template...

by harpua__ In reply to Did i say Row??? I meant ...

And I was still having problems. I am taking some Access classes next week and hopefully will be able to get some answers on the queries and importing of data. I really appreiciate your ideas. I think you are right and I should perfect the variables not just cover up the problem.

Thank you

Back to IT Employment Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums