Why a Number May Not Behave Like a Number
A column can contain values that look numerical but are stored as text. For example, a file might record a participant’s weight as “72.5 kg” or an expense as “$1,250.00.” A person can read these entries as measurements, but a data program may treat them as character strings because they include letters, symbols, or formatting. Calculations and numerical analyses require values to be represented as numbers, so this distinction matters when preparing data for regression.
The goal is not to remove every non-digit automatically. A symbol or space may be harmless formatting, but it may also carry information about the scale, units, or decimal convention. First determine what the entries mean and whether they follow one consistent, unambiguous format. Convert only when that check supports a clear numeric interpretation.
Separate Meaning, Appearance, and Storage
Three questions help clarify what a column contains. First, what does the variable measure? Second, how are its values written? Third, how does the file store those values? A response such as “weight in grams” answers the first question; “digits followed by a space and ‘g’” answers the second; “text” answers the third. Only the first tells us what the values mean.
A quantitative variable does not become categorical just because its entries are stored as text. Conversely, digits in a text field do not prove that it is a measurement: an identification code made of digits is still an identifier, not a quantity to convert. The variable’s meaning must be established before its storage format is changed.
Text entries can include several kinds of formatting. A leading or trailing space may be accidental. A currency symbol may label a monetary amount. A suffix such as “kg” may indicate a unit. A comma or period may mark a decimal point or separate groups of thousands, depending on the convention used. Spaces can also separate thousands, or be part of a label. These marks cannot all be handled with one universal deletion rule.
A Careful Conversion Process
A reliable conversion is a small data-cleaning decision, not merely a command to strip characters. The following steps make the decision explicit and reduce the chance of producing plausible-looking but incorrect numbers.
Check that the column represents a quantitative measurement or count, rather than an identifier or a category code.
Look for repeated symbols, spaces, suffixes, decimal marks, and grouping marks. Inspect all distinct formats, not just one convenient entry.
Use the data documentation or a reliable source to establish what the marks mean and whether the values measure the same quantity in the same units.
Remove or interpret characters only when their role is known. Keep entries that do not match the verified format for review.
Compare converted values with their original strings and check that the resulting numbers retain the intended scale and units.
This sequence makes an important distinction: a successful software conversion is not necessarily a correct conversion. A program may accept a string after removing punctuation, even when that punctuation was a decimal mark. Correctness depends on the meaning of the original representation, not simply on whether the software returns a number.
Worked Example: Currency Symbols and Grouping Commas
Suppose a hypothetical clinic expense file contains the following text values in a column documented as expenses in U.S. dollars: “$1,250.00,” “$980.50,” and “$1,075.00.” Determine whether these can be converted and show the conversion.
Check the variable. The column records expense amounts, a quantitative variable, rather than account identifiers. The documentation gives the unit as U.S. dollars.
Check the format. Every entry uses a leading dollar sign, a comma between the thousands and hundreds places when needed, and a period followed by two decimal digits. The file documentation confirms that the comma is a thousands separator and the period is the decimal mark. All three values use the same convention and unit.
Convert. Under that verified convention, the dollar sign labels the unit, and the comma groups thousands. Removing those formatting marks while retaining the decimal point gives:
The converted values are numerical amounts in U.S. dollars. For example, the first value means 1,250 dollars, not 1.25 dollars. The conversion is justified by the documented convention; deleting commas without checking that convention would not be justified.
Ambiguous Decimal Conventions Need Investigation
The same punctuation can have different meanings in different data sources. In one convention, “1,72” can mean one and seventy-two hundredths; in another, a comma separates thousands. Periods also have more than one convention. When values come from multiple files or sources, a column may combine conventions even if every entry appears plausible on its own.
A useful check is to inspect several entries alongside their source documentation, rather than guessing from a single value. Also consider whether the proposed interpretation is compatible with the variable’s definition and recorded units. Plausibility can flag a question, but it cannot establish the correct convention by itself.
Worked Example: A Mixed Decimal Format
A hypothetical dataset records room temperatures, but the documentation does not say which decimal convention was used. Three entries in a text column are “21,5,” “22.0,” and “20,8.” Decide whether it is safe to convert the column as it stands.
Identify plausible readings. The comma may be a decimal mark, making “21,5” mean 21.5 and “20,8” mean 20.8. The period in “22.0” may indicate that a different convention was used for that entry. Without documentation, punctuation could also follow a grouping convention, and a mixture of sources or formats is possible.
Do not choose a rule by appearance alone. Replacing commas with periods would produce “21.5,” “22.0,” and “20.8,” but this operation assumes the commas are decimal marks. Removing commas would instead produce “215” and “208,” which would change the apparent scale. Neither action is supported by the entries alone.
Decision. Do not convert automatically. Check the source files or data dictionary to establish the decimal convention and whether the entries have a common format. If the source confirms that commas are decimal marks and the period entry is equivalent notation, convert using that documented interpretation. Until then, preserve these strings and flag the convention as unresolved. A proposed conversion is not reliable merely because its output looks like a plausible temperature.
Units and Spaces Must Be Interpreted, Not Guessed
A suffix can make an entry text even when all measurements use one unit. For example, “42 kg” clearly includes a unit label if the documentation confirms that the column records mass in kilograms. The suffix can then be separated from the numeric part while retaining the unit as part of the variable’s meaning. But if some entries are in kilograms and others are in pounds, stripping the suffix would hide a real difference: the resulting numbers would not be comparable measurements on a common scale.
Spaces also require attention. A space at the beginning or end of an entry can be irrelevant formatting. An internal space can be a thousands separator, as in “1 250,” or it can separate a number from its unit, as in “42 kg.” Removing all spaces may be appropriate for one verified format and damaging for another. Identify the role of each space before deciding what to do.
If a column mixes units, do not treat numeric parsing as a complete solution. A value can be successfully extracted and still be expressed on a different scale from other entries. First establish the unit attached to each value; then make a separate, explicit decision about any necessary unit conversion. Recording units consistently is essential for meaningful numerical comparisons.
Worked Example: Spaces as Thousands Separators
A hypothetical equipment log records sample masses in grams. Its documentation states that spaces group thousands and that every entry is in grams. The text values are “1 250,” “980,” and “1 075.” Show a valid conversion and check the resulting scale.
Check the format and unit. The documentation establishes that the internal spaces group digits and that all values are grams. There are no unit suffixes or alternative punctuation conventions in these entries.
Convert each entry. Here, removing the internal grouping space preserves the written number:
As a simple verification, the mean of the three converted masses is
The result is on the same scale as the original entries: the observations are around one thousand grams, not around one gram or one million grams. The arithmetic checks the converted values for a scale error; it does not replace the documentation that established what the spaces mean.
Why Keep the Original Text?
Before converting, retain the original strings in a separate field or preserve an unchanged source file. This lets you trace each numerical value back to what was recorded. It also makes it possible to identify entries that do not follow the verified format rather than silently changing them.
After conversion, check the results systematically. Confirm that the converted field is numeric, compare its entries with the corresponding source strings, and look for unexpected failures or changes in scale. Do not assume that all values converted correctly just because most did. An entry with an unfamiliar symbol or format deserves review; it should not be forced into a numeric value by deleting characters at random.
These checks are preparation for analysis, not a substitute for understanding the data. A regression procedure can use numerical inputs only in ways that match the variables’ actual meanings. If a formatting error turns 1.25 into 125, the resulting number may be accepted by software but represent the wrong measurement.
Common Mistake: Delete Every Non-Digit
A tempting shortcut is to remove every character that is not a digit. That can erase decimal marks, change a negative value into a positive one, remove a unit distinction, or turn a formatted amount into a number on the wrong scale. For instance, “21,5” might represent 21.5 under a decimal-comma convention; deleting the comma gives 215, which is not an equivalent value.
The correction is to determine what each symbol means before altering it. Remove a symbol only when it is verified as formatting or a label that can be separated without changing the value. If the meaning is uncertain, do not guess: preserve the string and seek documentation or clarification.
Check Your Understanding
Answer each question by explaining what should be checked before conversion.
- A column documented in U.S. dollars contains “$2,400.00” throughout. What does the documentation allow you to infer about the dollar sign, comma, and period?
- A measurement column contains “3,5” and “3.5,” but its decimal convention is undocumented. Why is replacing every comma with a period not yet justified?
- A text field contains “1 200” and “950.” What information would you need before deciding whether the internal space should be removed?
- Why does converting a string successfully into a number not prove that the resulting value is correct?
- A column has values such as “70 kg” and “160 lb.” Why is removing the unit suffix from both entries not enough to make the column comparable?