Incorrect Formats

Lesson 4: Incorrect Formats

Data format problems occur when information is stored in a form that is inconsistent, inappropriate or incompatible with the way the data needs to be analyzed.

Incorrect formats are among the most common data-cleaning problems. A dataset may contain correct information, but if dates, numbers, currencies, percentages, text or other values are stored inconsistently, analytical tools may interpret them incorrectly.

For example, these three values may represent the same date:

01/10/2026
2026-10-01
October 1, 2026

To a human reader, they may be easy to understand. To a computer system, however, they may be three different text values unless they are converted into a consistent date format.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what incorrect data formats are.
• Identify common formatting problems in datasets.
• Recognize inconsistent date formats.
• Identify numbers stored as text.
• Clean currency and percentage values.
• Standardize text and categorical formats.
• Identify inconsistent phone-number formats.
• Understand why format consistency matters in data analysis.
• Use Excel functions and tools to correct common format problems.
• Understand how Python and pandas can be used to convert data types.
• Validate converted values before using them for analysis.

What Is an Incorrect Format?

An incorrect format occurs when a value is stored or represented in a way that does not match the intended meaning or expected structure of the data.

For example, if a sales amount should be numeric but is stored as:

"₦150,000"

the currency symbol and comma may prevent some systems from treating the value as a number.

Another example is a date stored as ordinary text:

"10th January 2026"

instead of a consistent machine-readable date.

Why Correct Formats Matter

Data analysis depends on correct data types and consistent representations.

Incorrect formats can affect:

• Sorting.
• Filtering.
• Calculations.
• Grouping.
• Charts.
• Pivot Tables.
• Statistical analysis.
• Database queries.
• Machine-learning models.
• Reports and dashboards.

For example, if a column contains numbers stored as text, Excel may not calculate the total correctly.

Common Types of Format Problems

• Inconsistent dates.
• Numbers stored as text.
• Currency symbols inside numeric values.
• Percentages stored inconsistently.
• Different decimal separators.
• Different thousand separators.
• Inconsistent capitalization.
• Extra spaces.
• Inconsistent phone numbers.
• Inconsistent country codes.
• Mixed units of measurement.
• Incorrect time formats.
• Mixed Boolean values.
• Incorrect categorical labels.

Dates and Date Formats

Date formatting is one of the most common data-cleaning challenges.

Consider these values:

01/02/2026
02/01/2026
2026-01-02
January 2, 2026

There is a potential ambiguity between day-month-year and month-day-year formats.

For example:

03/04/2026

could mean:

• 3 April 2026.
• March 4, 2026.

The analyst must understand the source system and intended date convention before converting the values.

Recommended Date Format

A widely useful standardized representation for dates is:

YYYY-MM-DD

For example:

2026-10-01

This format is clear, sortable and widely supported by databases and programming tools.

Dates in Excel

Excel can store dates as date values rather than ordinary text.

You can check whether a value is recognized as a date by examining how Excel behaves when you:

• Change its date format.
• Sort the values.
• Subtract one date from another.
• Use date functions.

Useful Excel functions include:

=DATE(2026,10,1)
=YEAR(A2)
=MONTH(A2)
=DAY(A2)

Text That Looks Like a Date

A common problem occurs when a date looks correct but is actually stored as text.

For example:

'2026-10-01

The apostrophe may cause Excel to treat the value as text.

Text dates may not behave correctly when used in date calculations or sorting.

Numbers Stored as Text

Numbers can sometimes be imported into Excel as text.

"150000"
"250000"
"350000"

Although these values look like numbers, they may actually be text strings.

This can create problems with:

SUM
AVERAGE
MIN
MAX
Sorting
Charts

Converting Text to Numbers in Excel

Excel provides several methods for converting text to numbers.

You can use:

=VALUE(A2)

Another common method is multiplying the value by 1:

=A2*1

Another option is using:

Data → Text to Columns

depending on how the imported data is structured.

Currency Formats

Currency values often contain symbols and separators.

₦150,000
₦200,000
$500
$750

When currency symbols are stored inside a text field, calculations may fail.

A good approach is to store the numeric amount separately from the currency information where appropriate.

This structure is easier to analyze than mixing currency symbols directly into the numeric values.

Currency Conversion

Currency conversion should not be confused with currency formatting.

These values:

₦150,000
$150,000

do not represent the same monetary value simply because the numeric amount is the same.

Before combining financial datasets, analysts should understand:

• The currency.
• The exchange rate.
• The exchange-rate date.
• The source of the exchange rate.

Percentage Formats

Percentages can also be represented in different ways.

25%
0.25
25

These values may all be intended to represent 25%, but they are not numerically equivalent in every context.

For calculations, 25% is usually represented as:

0.25

An analyst must know how the source system stores percentages before transforming them.

Decimal Separators

Different countries and software systems may use different decimal conventions.

1,500.75
1.500,75

Depending on the regional convention, these can represent the same number.

If the analyst incorrectly converts them, the resulting data can be severely distorted.

Thousands Separators

150,000
150000
150 000

These may all represent the same number, but they require appropriate parsing and standardization.

Text Formatting Problems

Text fields can contain inconsistent capitalization.

Lagos
lagos
LAGOS
Lagos 

These values may represent the same location but may be treated as separate categories during analysis.

Useful Excel functions include:

=LOWER(A2)
=UPPER(A2)
=PROPER(A2)

Extra Spaces

Invisible spaces are a common source of formatting problems.

"Lagos"
"Lagos "
" Lagos"

These values may look identical but are technically different strings.

Use:

=TRIM(A2)

to remove unnecessary spaces around words and between repeated spaces.

Non-Printing Characters

Data copied from websites, PDFs or external systems may contain hidden characters.

=CLEAN(A2)

can remove many non-printing characters.

Phone Number Formats

Phone numbers are another common format problem.

08123456789
+2348123456789
0812 345 6789
0812-345-6789

These may refer to the same telephone number.

Before analysis, the organization should define a standard representation.

For example:

+2348123456789

could be used as the standard international format.

Important Phone Number Rule

Phone numbers should generally be treated as identifiers rather than quantities.

You normally should not calculate the average phone number or add phone numbers together.

Therefore, storing phone numbers as text is often appropriate, even though they contain digits.

Mixed Data Types in One Column

150000
200000
N/A
Unknown
₦300000
250000

The column contains numeric values, text labels and currency-formatted values.

This should be standardized before analysis.

Boolean Values

Yes
YES
yes
Y
1
True

These may all mean the same thing, but inconsistent representation makes filtering and analysis harder.

A standard could be:

TRUE
FALSE

or:

Yes
No

The important point is consistency.

Category Labels

Male
male
M
MALE

could produce four categories in a poorly cleaned dataset.

The analyst should establish a consistent representation.

Units of Measurement

5 kg
5000 g
2 lb

These values cannot be directly compared without converting them to a common unit.

Possible standard units include:

• Kilograms for weight.
• Metres for distance.
• Litres for volume.
• Celsius for temperature.

Time Formats

09:30
9:30 AM
21:30
9.30

The analyst must determine which values represent actual times and which may simply be text.

Excel Formatting Workflow

1. Inspect the column.
2. Identify inconsistent values.
3. Determine the intended format.
4. Create a backup column.
5. Standardize the values.
6. Validate the results.
7. Replace the original column only after verification.

Useful Excel Functions

=TRIM(A2)
=CLEAN(A2)
=LOWER(A2)
=UPPER(A2)
=PROPER(A2)
=VALUE(A2)
=DATE(2026,10,1)
=YEAR(A2)
=MONTH(A2)
=DAY(A2)

Python and pandas Data Types

Python pandas provides tools for inspecting and converting data types.

df.dtypes

For example:

print(df.dtypes)

Converting Text to Numeric Data

df["amount"] = pd.to_numeric(
    df["amount"],
    errors="coerce"
)

The errors="coerce" option converts values that cannot be interpreted as numbers into missing values. This is useful because the analyst can then investigate those failed conversions.

Converting Dates with pandas

df["date"] = pd.to_datetime(
    df["date"],
    errors="coerce"
)

Invalid values can become missing values that require further investigation.

Standardizing Text with pandas

df["name"] = (
    df["name"]
    .astype("string")
    .str.strip()
    .str.lower()
)

This removes surrounding spaces and converts text to lowercase.

Cleaning Currency Values with pandas

df["amount"] = (
    df["amount"]
    .astype("string")
    .str.replace("₦", "", regex=False)
    .str.replace(",", "", regex=False)
)

df["amount"] = pd.to_numeric(
    df["amount"],
    errors="coerce"
)

Inspect Before Converting

Never convert data blindly.

df["amount"].unique()

or:

df["amount"].value_counts(dropna=False)

This can help identify unexpected formats before transformation.

Format Conversion and Data Loss

Incorrect conversions can destroy information.

For example, if:

00125

represents a product code, converting it to a number may produce:

125

The leading zeros have been lost.

This is why analysts must understand the meaning of a field before changing its data type.

Identifiers vs Numeric Measurements

• Student IDs.
• Phone numbers.
• Account numbers.
• Postal codes.
• Product codes.
• Transaction references.

These are often better treated as text.

Data Type vs Display Format

It is important to distinguish between the underlying data type and how a value is displayed.

150000

may be displayed as:

₦150,000.00

The underlying value may still be numeric. The currency representation is only the display format.

Common Incorrect Format Examples

Safe Format-Cleaning Process

1. Identify the field and its intended meaning.
2. Inspect the existing values.
3. Identify incorrect or inconsistent formats.
4. Define the desired standard.
5. Create a backup of the original data.
6. Apply the transformation.
7. Check invalid conversions.
8. Compare the cleaned values with the original values.
9. Validate the final data type.
10. Document the transformation.

Practical Example: Student Dataset

The data may contain correct information, but the formats are inconsistent.

The analyst may standardize:

• Dates to YYYY-MM-DD.
• Fees to numeric values.
• Phone numbers to a consistent international format.

Validation After Formatting

• Are all dates valid?
• Are all numeric fields actually numeric?
• Are any values unexpectedly missing?
• Were leading zeros preserved where necessary?
• Are currency values correctly interpreted?
• Are units consistent?
• Are categorical values standardized?
• Did the transformation accidentally change valid information?

Practical Exercise

Create a dataset containing at least 20 records with deliberately inconsistent formats.

Include:

• Student ID.
• Student Name.
• Date of Birth.
• Registration Date.
• Course Fee.
• Phone Number.
• Gender.
• City.

Introduce different formats for dates, currency, phone numbers, capitalization and spaces.

Then:

1. Identify every format inconsistency.
2. Define the correct standard for each column.
3. Create backup columns.
4. Clean the values using Excel functions.
5. Convert appropriate columns to the correct data type.
6. Check for values that could not be converted.
7. Validate the cleaned dataset.
8. Document every transformation.

Knowledge Check

1. What is an incorrect data format?
2. Why can inconsistent date formats be dangerous?
3. How can you convert text to numbers in Excel?
4. What does TRIM do?
5. What does CLEAN do?
6. Why should phone numbers usually be treated as text?
7. What is the difference between a numeric value and its display format?
8. Why can currency symbols cause problems during analysis?
9. What does pandas pd.to_numeric() do?
10. What does pd.to_datetime() do?
11. Why should you inspect data before converting it?
12. Why should identifiers such as Student IDs not automatically be converted to numbers?

Key Terms

• Data Format: The way a value is represented or stored.
• Data Type: The kind of value represented, such as text, number, date or Boolean.
• Standardization: Converting values into a consistent representation.
• Parsing: Interpreting a value so that software can understand its structure.
• Normalization: Cleaning values so that equivalent values can be compared consistently.
• Identifier: A value used to identify a record or entity.
• Validation: Checking that cleaned data meets expected rules.

Key Takeaways

• Incorrect formats can make correct information difficult or impossible to analyze.
• Dates should be standardized carefully.
• Numbers stored as text can cause calculation problems.
• Currency symbols and separators may need to be removed before numeric conversion.
• Percentages must have a clearly defined representation.
• Text should be standardized for spaces and capitalization.
• Phone numbers and IDs are identifiers and should not automatically be treated as numeric measurements.
• Mixed units should be converted to a common unit.
• Excel and pandas provide powerful tools for format correction.
• Always validate transformed data before using it for analysis.
• Keep backups and document important transformations.

Lesson Summary

Incorrect formats are a major source of data-quality problems. A dataset may contain accurate information but still produce incorrect analytical results when dates, numbers, currency values, percentages, text, phone numbers or units are represented inconsistently.

Data analysts should first understand the meaning of each field, inspect the original values and define a standard format before making changes. Excel functions such as TRIM, CLEAN, LOWER, UPPER and VALUE can help clean common formatting problems. Python and pandas provide functions such as pd.to_numeric() and pd.to_datetime() for systematic data conversion.

However, format conversion must be performed carefully. Identifiers such as student IDs, account numbers and phone numbers may contain digits but should often remain as text. Analysts must also validate the results and ensure that transformations do not accidentally destroy useful information.

Next Lesson

In the next lesson, we will study Data Consistency and learn how to ensure that the same values, categories, units and business rules are represented consistently throughout a dataset.