Excel

[GregRTicketMan]

Our online ticketing service downloads reports in Excel but Excel is changing the seating numbers to dates. For example Table 4 Seat 1 become 1 April. How do I prevent that?

[#2]

@GregRTicketMan How are you downloading/importing? Important to indicate that the seating part of the data should be treated as text. Otherwise, Excel will try to be smart by assuming that you are importing dates, provided that it can create a date from that text string. This is, depending on your local system settings,  4-1 or 4/1 may become April 1 (US style) or 1 January (European style). But 14-3 or 14/3 will remain 14-3 (or 14/3) in text on a US system, but it will change to 14 March on a European system. Being on a European style date system myself, I played around with this a bit. When I import 3/14 it becomes 1 March 2014.

If you are importing with PowerQuery you need to check the Data Type step(s). If you use the legacy Text import option (or Text to columns), make sure to set the table seating column to type Text.

[#3]

@GregRTicketMan Hi. Three approches for different situations;

With following lines in a text file

4-222
4-223
4-1
4-02
4-34

1. Opening it with Excel you could format the data as Text in step three

The cell contains text

2. If you have already imported the data and it is enough that it looks correct, you could change the format from default

to type: M-D

to get the wanted look even though Excel has interpreted the data as a date.

Finally, align the column to left

3. You may also use a helper column;

=TEXT(@A:A;"M-D") 

[#4]

@Riny_van_EekelenThank you very much for the advice. I appreciate it.

[#5]

This was helpful and I used your advice to make it easier to change the format from a date to what I needed which was a table # and a seat #. I wish there was some way to override Excel so it didn't assume it knew what I wanted in those cells and would just accept what I put in.

[#6]

Yes, in some cases, it looks like a neat possibility to disable default format assumptions.
I've been there before and thus just had to recall.
Glad to help :)