Excel data conversion

The UK's most regular instructor-led training courses.
Training information: basic excel training · Microsoft+excel+training · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

· Medium Priority · Version 2007

21 posts

ReplyTue 2 Feb 2010, 10:48Delegate Polly said...

Polly has attended:
Powerpoint Intermediate Advanced course

Excel data conversion

I often need to export information into an excel spreadsheet. some ofo the information refers to a client number and these are in the format of 5/1 or 26/3 with the lower numbers, these are automatically put into a date format. How can I put these back into the original format? Normally when I try to change the format it completely changes the number.

Many thanks

Polly

Version	Date	Location	Places available	Book	Next place rate (£)
Excel VBA Intro Intermediate 2 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Thu 18 + Fri 19 Mar 2010	Southwark	0	FULL
2003	Mon 22 + Tue 23 Mar 2010	Bloomsbury	1		£495	£495
2007	Tue 6 + Wed 7 Apr 2010	Bloomsbury	4		£470	£475
2003	Wed 7 + Thu 8 Apr 2010	Southwark	7		£445	£450
2007	Thu 15 + Fri 16 Apr 2010	Bloomsbury	0	FULL
2003	Wed 21 + Thu 22 Apr 2010	Bloomsbury	7		£445	£450
Full Schedule: See all 36 Excel VBA Intro Intermediate course dates. Bookings currently available until 22nd December 2010.

1,055 posts

ReplyWed 3 Feb 2010, 10:47Trainer Andrew said...

RE: Excel data conversion

Hi Polly

Thank you for your question. Excel will try and convert values to dates. One way around this is in your source sheet to format the values as Text (select data, right click and choose Format cells. From the number categories choose Text). When the data is brought into your destination sheet Excel will not format the values as dates.

If your source data is a text file use step 3 of the import wizard. The import wizard starts automatically after using File > Open and then choosing a text file containing your data. Step three of the wizard lets you specify the format of the columns of data to be imported. You could set the column containing your client number as text. Excel will leave the client reference alone rather than convert into a date.

I hope this helps - let me know if you have any further questions.

Kind regards,
Andrew

21 posts

ReplyWed 3 Feb 2010, 14:41Delegate Polly said...

RE: Excel data conversion

Dear Andrew

Thank you but we cannot change the source data. We only have the ability to manipulate the data once it is in excel.

Or do you mean I should change my standard excel format so the first column will always be text? I am not sure how to do this.

Many thanks

Polly

1,055 posts

ReplyWed 3 Feb 2010, 15:50Trainer Andrew said...

RE: Excel data conversion

Hi Polly,

thanks for your reply. No problem. What format is your source date stored in? Is it text, CSV, Excel or some other type?

Let me know and I will try to assist further.

Kind regards,
Andrew

21 posts

ReplyWed 3 Feb 2010, 17:12Delegate Polly said...

RE: Excel data conversion

We are exporting our source data from a document management system we use so I am not sure exactly what format this would be stored in, some sort of database I would assume.

so 5/1 would become 05/01/1999 or something similar. Would there be a way to convert this back into 5/1? We then print these reports so we just need them to look correct even if they were saved in a format we could not search or sort.

Many thanks

1,055 posts

ReplyMon 8 Feb 2010, 17:05Trainer Andrew said...

RE: Excel data conversion

Hi Polly

thank you for coming back to me. Could you let me know how you get the data into Excel? Depending on the method you use we may be able to ensure Excel doesn't reformat the data.

Kind regards,
Andrew

1,055 posts

ReplyMon 8 Mar 2010, 10:45Trainer Andrew said...

RE: Excel data conversion

Hi Polly

I just wanted to check is this still a problem you are encountering? If so perhaps you could let me know how the data is brought into Excel. This may affect how we can control Excel's behaviour in formatting the values.

Kind regards,
Andrew

21 posts

ReplyTue 9 Mar 2010, 16:39Delegate Polly said...

RE: Excel data conversion

Dear Andrew

This information comes from a datamanagement system. I have contacted them to find out where the excel template comes from as we see the information and then click to export into excel.

I am just waiting from them to get back to me and then I will let you know. Many thanks for your help so far.

Polly

1,055 posts

ReplyTue 9 Mar 2010, 17:12Trainer Andrew said...