Formatting Issues

How to Store Visual Acuity Values as Text in Excel (Stop Date Conversion)

Maira MehtabMaira Mehtab Nov 10, 2026 869 views

Question details

The user needs to enter visual acuity values (like 6/18 or 20/60) into spreadsheets without the software changing them to dates or calculating them as fractions.

How to Store Visual Acuity Values as Text in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Entering specific medical or ratio data into a spreadsheet for recording, counting, or bulk ordering.
Observed behavior
Excel automatically interprets entries formatted like fractions or dates (e.g., 6/18) and alters the original input into a formatted date or a calculated decimal/fraction.
Before you start

Ensure you have not already entered the data into cells formatted as 'General', as Excel may have already converted them to dates. If so, clear the cell contents before applying the correct text format.

Solution 1Recommended

Format Cells as Text Before Data Entry

Change the cell format to Text prior to typing your visual acuity values to force Excel to treat the input exactly as typed.

1
Select the target cells

Highlight the cells, columns, or rows where you plan to enter the visual acuity values.

2
Open Format Cells

Right-click the highlighted selection and choose 'Format Cells' from the context menu.

3
Apply Text formatting

Go to the 'Number' tab, select 'Text' from the Category list, and click 'OK'.

4
Enter the data

Type your visual acuity values (e.g., 6/18 or 20/60). Excel will now preserve the exact text without converting it to a date.

Format Cells as Text Before Data Entry
Manage Data Effectively with WPS Spreadsheet

Easily Prevent Automatic Date Formatting in WPS Office

WPS Spreadsheet provides powerful formatting tools to keep your medical, scientific, or ratio data exactly as you type it. Prevent frustrating auto-conversions with easy text formatting.

  1. 1. Open your spreadsheet: Launch WPS Spreadsheet and open your existing data file or create a new blank workbook.
  2. 2. Open formatting options: Highlight the target cells and press Ctrl+1 to quickly open the 'Format Cells' dialog box.
  3. 3. Set format to Text: Under the 'Number' tab, select 'Text' from the category list and click 'OK'.
  4. 4. Input the values: Type your visual acuity values (e.g., 6/18) directly into the cells without experiencing any auto-conversion issues.
100% compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Intuitive cell formatting to prevent unwanted date or fraction conversions.Lightweight and free to use for efficient data entry and database management.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically change 6/18 to a date?

By default, Excel's 'General' format attempts to guess the data type you are entering. When it sees a number followed by a slash and another number (like 6/18), it assumes you are entering a date (June 18th) and automatically converts the format.

How can I count the cells containing these visual acuity text values?

Once the values are safely stored as text, you can use the COUNTA function instead of the standard COUNT function. COUNTA counts the number of cells that are not empty, including those containing text strings like '6/18'.

Can I keep superscript formatting for text selected from a drop-down list?

Unfortunately, data validation drop-down lists in Excel only support plain text. Any rich text formatting, such as superscript or subscript applied to the source data, will be stripped out when the item is selected in the drop-down menu.