How to Prevent Excel From Auto Converting Data Units When Working With Bulk CSV

This article offers suggestions to deal with Excel automatically converting RedEye Drawing numbers to date formats automatically. This can create problems importing data into Bulk CSV tool when RedEye artefact numbers have a syntax containing continuous numbers and hyphens that Excel incorrectly analyses as probable dates, and updates them to Date format as a consequence.

Note this is an acknowledged issue with MS Excel and not RedEyeDMS.

Microsoft Excel is pre-programmed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately, there is no way to turn this off. But there are ways to get around it.

Option 1 - Configure Excel Preferences

The option to default convert continuous letters and numbers to a date can be disabled in the Excel Desktop app.

This will disable the function on all spreadsheets so consideration of the effect on other work should be made before disabling

Windows

1. Open Files->Options->Data from the Excel menu

2. De-select ‘Convert continuous letters and numbers to a date’ option and Save
   

   how_to_prevent_excel_from_auto_01.png PNG image will display here

   

    

Macintosh

1. Open Microsoft Excel->Preferences menu

2. Select Edit icon

3. De-select ‘Convert continuous letters and numbers to a date’

   how_to_prevent_excel_from_auto_02.png PNG image will display here

   

   how_to_prevent_excel_from_auto_03.png PNG image will display here

   

   
   

Option 2 - Pre-Format Cells

If you find that Excel is automatically converting data to time format, pre-format the cells you want to enter numbers into as Text. This way Excel will not try to change what you enter into dates.

Windows version -

1. Select the cells that you’ll enter numbers into.

2. Press Ctrl + 1 (the 1 in the row of numbers above the QWERTY keys) to open Format Cells.

3. Select Text, and then click OK.

Web Version -

1. Select the cells you want to enter numbers into.

2. Click Home > Number Format > Text.

Option 3 - Edit the existing cell data

• Add a space before you enter a number. The space remains in the cell after you press Enter. (See Notes), or

• Add an apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

Important Considerations

It is recommended to use an apostrophe instead of a space for entering data if you plan on using lookup functions against the data. Functions like MATCH or VLOOKUP overlook the apostrophe when calculating the results.

If a number is left-aligned in a cell that usually means it isn’t formatted as a number.

If you type a number with an “e” in it, such as 1e9, it will automatically result in a scientific number: 1.00E+09. If you don’t want a scientific number, enter an apostrophe before the number: ‘1e9

Depending on the number entered, you may see a small green triangle in the upper left corner of the cell, indicating that a number is stored as text, which to Excel is an error. Either ignore the triangle, or click on it. A box will appear to the left. Click the box, and then select Ignore Error, which will make the triangle go away.