Discussion Locked

Auto Format in Excel

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

8 replies

by slacker

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.

by harpua__

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

by harpua__

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

by adamjedgar

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!

by harpua__

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

by adamjedgar

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

by adamjedgar

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!!!

by harpua__

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

This discussion is closed to new replies.