What you will fix
The Raw text column contains values that look numeric but are stored as text. You will diagnose the source value, convert it with VALUE in a separate column, fill the formula down, and verify the converted values are usable as numbers without overwriting the source.
Learning objectives
Open the practice workbook
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Diagnose and convert the values
Step 1: Select A2 and inspect the source value. The lesson starts from 120 stored as text; do not fix it by manually retyping 120 because that would hide the original data-type problem.
Step 2: Select B2, enter =VALUE(A2), and press Enter. VALUE interprets the numeric text in A2 and returns the number 120 in B2.
=VALUE(A2)Step 3: Fill B2 down through B4 instead of retyping equivalent formulas. Confirm B2:B4 display 120, 80, and 40 while A2:A4 remain unchanged.
Checkpoint
| Raw text | Converted |
|---|---|
| 120 | 120 |
| 80 | 80 |
| 40 | 40 |
A2:A4 remain the original text values. B2:B4 must be calculated from VALUE and display the corresponding numeric results.
Common mistakes and fixes
If VALUE returns an error, inspect the source for spaces, currency symbols, separators, or other characters that may not match the sheet locale. Fix the actual input pattern deliberately rather than assuming every text value can be converted by the same formula.
If only B2 changes, the formula was not filled down. Copy or fill the B2 formula through B4, then confirm each result references the source cell on the same row.
Independent practice
Temporarily add a fourth text-formatted numeric value in A5, fill the conversion formula to B5, and verify the result is numeric. Then remove the test row to restore the original three-row sample.
Key takeaways
- ✓Numbers can look numeric while still being stored as text.
- ✓Preserve the source column while diagnosing a data-type problem.
- ✓VALUE converts recognized numeric text into a number.
- ✓Enter the conversion formula once, then fill it down for repeated rows.
- ✓Verify the converted cells are formula results rather than manually typed replacements.
Check your understanding
Answer four questions about the practice sheet.
Next lesson
Continue with Fix dates imported as text.
Comments
No comments yet. Be the first to join the discussion.