Oct 07 2021 07:10 PM
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?
Oct 07 2021 10:00 PM
@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.
Oct 07 2021 10:05 PM
@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")
Oct 15 2021 09:20 AM
@Riny_van_EekelenThank you very much for the advice. I appreciate it.
Oct 15 2021 09:22 AM
Oct 15 2021 11:09 AM