How to Store Visual Acuity Values as Text in Excel (Stop Date Conversion)
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.

- 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.
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.
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.
Highlight the cells, columns, or rows where you plan to enter the visual acuity values.
Right-click the highlighted selection and choose 'Format Cells' from the context menu.
Go to the 'Number' tab, select 'Text' from the Category list, and click 'OK'.
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.

Prefix the Value with an Apostrophe
A quick method for entering individual values as text without altering the entire column's formatting.
Store Numerator and Denominator in Separate Columns
Use this method if you need to perform numerical calculations or sort the visual acuity values later.
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. Open your spreadsheet: Launch WPS Spreadsheet and open your existing data file or create a new blank workbook.
- 2. Open formatting options: Highlight the target cells and press Ctrl+1 to quickly open the 'Format Cells' dialog box.
- 3. Set format to Text: Under the 'Number' tab, select 'Text' from the category list and click 'OK'.
- 4. Input the values: Type your visual acuity values (e.g., 6/18) directly into the cells without experiencing any auto-conversion issues.

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.




