Can ChatGPT diagnose imported Excel data from a pasted sample?

ChatGPT can identify visible patterns and suggest checks to run in Excel, but a plain-text paste does not preserve cell type, locale settings, number formats, or invisible-character metadata. Verify uncertain conditions in Excel before choosing a cleaning formula.

Excel · ChatGPT

Diagnose Imported Excel Data Before Cleaning It with ChatGPT

Q: How can I check whether imported dates are stored as text?

Run ISNUMBER on representative cells and temporarily display their underlying values with a General number format. A date-looking value for which ISNUMBER returns FALSE is not stored as an Excel date serial. After confirming the source pattern and locale, use the Convert Text to Date in Excel workflow for the conversion method.

Q: How can I identify which invisible character is present in imported data?

First compare LEN results before and after TRIM to test for ordinary spaces. If the mismatch remains, inspect the suspect character with CODE or UNICODE instead of assuming it is a non-breaking space. Once identified, use the Remove Spaces in Excel workflow for the appropriate cleaning method.

Imported Excel data can look clean while hiding text dates, text-formatted amounts, blanks, or invisible characters. Paste a small sample into ChatGPT to identify likely issue types and get targeted Excel checks first — then clean only after the problem is confirmed.

Copy the diagnostic prompt:

You are diagnosing possible data-quality issues in a plain-text paste of Excel data.

Column mapping:
- Column A = [column name]
- Column B = [column name]
- Column C = [column name]
- Column D = [column name]

Sample data:
[paste 5-10 rows including headers]

Diagnose only. Do not clean the data yet.

Rules:
1. Identify potential data-quality issues by column and issue type only.
2. Do not count affected rows or claim you found every affected row.
3. Separate what is directly visible in the paste from what cannot be confirmed from plain text alone.
4. Do not infer Excel cell type, locale, date interpretation, decimal/thousands separator settings, or invisible character type.
5. Suggest only simple Excel diagnostic checks when genuinely useful, such as ISNUMBER, ISTEXT, LEN, or ISBLANK.
6. Do not write specific cell references. Refer to cells by column name and row description instead.
7. For every diagnostic check, state what it confirms and what it does not confirm.
8. For ISBLANK, TRUE confirms a genuinely empty cell; FALSE only confirms that something is present and does not identify what it is.
9. Do not use CELL, CODE, TRIM, CLEAN, SUBSTITUTE, VALUE, DATEVALUE, NUMBERVALUE, or any cleaning formula at this stage.
10. Do not introduce issues or transformations that are not supported by the sample.
11. Do not create a spreadsheet or file.
12. Keep the answer concise.

Your goal is only to identify the types of possible data-quality problems in each relevant column and tell me what I should verify in Excel before cleaning anything.

Paste a small real sample and include the column mapping. ChatGPT should diagnose issue types and verification checks only — not clean the data yet. Use with ChatGPT.

Before — imported data looks inconsistent, cause not yet confirmed

Customer Name	Invoice Date	Invoice Amount	Region
Smith, John	'15/01/2026	£ 4,250.00	North
Jones, Mary	'28/02/2026	£7,800.00	South
Davis, Emma	10/03/2026	£3,400.00	East
Wilson, Anna	22/03/2026	£ 5,600.00	— blank —
Miller, David	03/04/2026	7800	South

          Visible differences are clues, not proof of storage type, locale, or hidden characters.
        

After — ChatGPT turns the sample into checks to run in Excel

Column	Potential issue	Verify in Excel	What it confirms
Invoice Date	Some values show a leading apostrophe	ISTEXT / ISNUMBER	Text vs numeric storage
Invoice Amount	Spacing and display formats differ	ISNUMBER / ISTEXT	Numeric vs text storage
Region	One value appears blank	ISBLANK / LEN	True blank vs stored content

Safe stopping point

Diagnose → verify in Excel → clean only after confirmation.
Do not infer locale, cell type, or invisible characters from the paste alone.

What imported data may be hiding — and what must be verified

Trailing/leading spaces

"London " doesn't match "London" in a lookup

Most common

Numbers stored as text

SUMIFS returns 0 even though matching rows exist

Most common

Dates stored as text

Date column sorts alphabetically instead of chronologically

Easy fix

Invisible / non-breaking spaces

TRIM/CLEAN may not remove every imported space — character 160 can require SUBSTITUTE(...,CHAR(160)," ")

Invisible

How to diagnose imported Excel data step by step

Diagnose before cleaning — 5 steps

Keep the original import unchanged

Do not edit suspicious cells first. Preserve the raw data so every later check can be compared with the source.

Paste a small sample with headers

Copy 5-10 representative rows and give ChatGPT the exact column mapping. The paste is evidence of visible patterns, not proof of Excel cell types.

Ask ChatGPT for diagnostic checks only

Use the prompt above to identify potential issue types and simple checks such as ISNUMBER, ISTEXT, LEN, or ISBLANK.

Run the checks in Excel

Confirm storage type, blanks, and other uncertain conditions in the workbook itself. Do not let ChatGPT infer locale, date order, separators, or invisible characters from appearance alone.

Clean only after the issue is confirmed

Once you know what Excel is actually storing, choose the appropriate helper-column cleaning method and validate it on known rows before applying it broadly.

Diagnosis first: A plain-text paste loses important Excel metadata. Treat ChatGPT's output as a checklist of what to verify, not proof that a particular cleaning formula is safe.

Frequently asked questions

How can I tell whether trailing spaces are causing an Excel mismatch?

Compare the original value with a TRIMmed test value and check whether their lengths or equality results differ. This confirms whether ordinary leading or trailing spaces are involved; if TRIM makes no difference, investigate other invisible characters. For the cleaning methods, use the Remove Spaces in Excel workflow.

Why are my numbers stored as text in Excel?

When data is imported from external systems like CRMs or accounting software, numbers are often formatted with text characters or invisible apostrophes. Excel may treat imported amounts as text, which can cause arithmetic or aggregation formulas to ignore or mishandle them. VALUE() converts text that Excel recognises as a number, but currency and decimal/thousands separators must match the locale or be normalised first.

How can I check whether imported dates are stored as text?

Run ISNUMBER() on representative cells and temporarily display their underlying values with a General number format. A date-looking value for which ISNUMBER returns FALSE is not stored as an Excel date serial. After confirming the source pattern and locale, use the Convert Text to Date in Excel workflow for the conversion method.

How can I identify which invisible character is present in imported data?

First compare LEN() results before and after TRIM() to test for ordinary spaces. If the mismatch remains, inspect the suspect character with CODE() or UNICODE() instead of assuming it is a non-breaking space. Once identified, use the Remove Spaces in Excel workflow for the appropriate cleaning method.

Can ChatGPT clean my Excel data for me automatically?

This workflow uses ChatGPT to diagnose a sample and return auditable Excel formulas. Whether ChatGPT can directly create or modify a workbook depends on the ChatGPT environment and tools you are using.