|
Fransiska Kleijer
|
September 9, 2013 at 4:24 am
After exporting attendee data to excel, some numbers (in this case housenumbers with a – in between numbers) are changed into a date or time notation. How can I avoid this or what kind of formula do I need? If I use format cells and text it changes the date or time in five arbitrary numbers…
I have googled for hours on the internet but can’t find a solution.
|
|
Dean
|
September 9, 2013 at 4:46 am
Hi,
Can you give me an example of an address where it does this, it can be a fake address so long as the structure is correct.
|
|
Fransiska Kleijer
|
September 9, 2013 at 4:53 am
number should be
2:00 AM 2 A
12-jan 12-1
6-aug 6-8
|
|
Dean
|
September 9, 2013 at 5:14 am
Thanks for that.
OK, well if the numbers are on the line alone then yes it looks like Excel and probably others like Open Office will convert them to dates.
If it has something after it such as 12-1 My Street, then it wont.
There isn’t much we can do about it as it is the spreadsheet software that is doing the change not us.
Further testing shows that Open Office seems to open the -XLS files without converting them, so you may want to give that a try.
|
|
Fransiska Kleijer
|
September 9, 2013 at 5:24 am
okay, thank you, people tend to forget to fill in the number in their addresses that’s why i made this a seperate field. I’ll try open office.
|
|
Josh
|
September 9, 2013 at 11:24 am
You might also try appending a = before the fields to stop excel from automatically converting these to dates:
http://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates
|
|
Fransiska Kleijer
|
September 9, 2013 at 11:39 am
Wow thanks, a lot of solutions in there!
|
|
Josh
|
September 10, 2013 at 2:16 pm
Indeed there are. It appears to be a common issue with MS Excel.
|
