Everything Is Text Until You Convert It

Every CSV value is a string. Conversion is your job, and it is where the surprises live.

"1,234" has a thousands separator. "1.234" might be a decimal comma from another locale. "12%" and "$1,234" carry units. "N/A" is not a number. "" is unknown.

Two failures worth knowing before they happen.

Leading zeros vanish the instant an identifier is treated as a number. Postcodes, phone numbers and record ids are text, always, no matter how numeric they look.

Sorting numbers as text puts 10 before 9. If a chart's order looks wrong, check this before anything else.

◈ Graded Challenge

Write to_number(value) converting one messy spreadsheet value into a number, or null when it cannot honestly be read as one. Strip surrounding whitespace. Remove any leading currency symbol of $ or £, remove commas used as thousands separators, and remove a single trailing percent sign. Then convert what remains. Return null for an empty string, for "N/A", "NA", "-" and "unknown" in any casing, and for anything else that will not convert.

Name it exactly: to_number(value)

4 visible + 8 hidden

loading editor…

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back