Category: Data Cleanup

The same country shows up as USA, U.S., US, and United States. A PivotTable counts them as four different countries. Instead of fixing them one by one with Find and Replace, keep a small lookup table that maps every spelling to one standard value, and let a formula do the replacing.

This guide builds that table with ten made-up customers, shows the formula, and lists exactly which labels were mapped and which were not.

Applies to: Microsoft documents VLOOKUP for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Tested in the Excel desktop app with an English interface on Korean-language Windows. We did not test other Excel versions, so results may differ there.

The Messy Column

Our Labels sheet has ten customers and a country_raw column. It holds USA, U.S., United States, US, u.s.a., a United States with two spaces between the words, a typo (Untied States), Canada, CANADA, and U.K. All names are fictional.

Step 1: Build the Lookup Table

On a separate sheet (we called it CountryMap), put the spelling you see in column A and the standard value in column B. We used six rows: USA, U.S., United States, and US all point to United States, Canada points to Canada, and U.K. points to United Kingdom.

The table does not contain u.s.a., CANADA, the double-space version, or the typo. They are there on purpose, to show what the formula does with them.

Step 2: Look Up the Standard Value

In the cell next to the first label (C2), enter this formula and fill it down to C11:

=IFERROR(VLOOKUP(TRIM(B2),CountryMap!$A$2:$B$7,2,FALSE),"NOT MAPPED")

Here is what each part does:

• TRIM(B2) removes extra spaces from the label before the lookup.
• VLOOKUP(...,2,FALSE) finds the label in column A of the table and returns the matching value from column B. FALSE asks for an exact match.
• IFERROR(...,"NOT MAPPED") shows NOT MAPPED when the label is not in the table. Without it, those rows would show #N/A.

What Happened to Each Label

Raw label	Result	Why
USA, U.S., United States, US	United States	Listed in the table
United States (two spaces)	United States	TRIM collapsed the extra space
Canada	Canada	Listed in the table
CANADA	Canada	Matched even though the case differs (our observation)
U.K.	United Kingdom	Listed in the table
u.s.a.	NOT MAPPED	Not in the table
Untied States	NOT MAPPED	A typo, not in the table

Eight of the ten rows were mapped and two were not. The NOT MAPPED text is the point of the formula: it turns a silent mistake into something you can filter for.

Microsoft’s page says leading spaces, trailing spaces, and nonprinting characters can cause unexpected VLOOKUP results. It does not say whether the match ignores case. The CANADA result is what we saw in our test, so check your own data.

How to Handle the NOT MAPPED Rows

Filter column C for NOT MAPPED and decide for each one. For u.s.a., add a new row to the table. For the typo, you can add the typo as another row in the table or fix the source value. We did not re-run the formula after changing the table. To check one list of values against another, see our guide on comparing two lists in Excel.

If you add rows to the table, make sure the formula range still covers them. Ours ended at row 7 ($A$2:$B$7).

Things to Know

• IFERROR hides every error, not only a missing label. A bad range or a broken sheet name would also show NOT MAPPED. If everything shows NOT MAPPED, check the formula first.
• TRIM does not remove non-breaking spaces. If a label with a visible space still does not map, see why TRIM isn’t removing non-breaking spaces.
• If VLOOKUP returns #N/A on values that look identical, see VLOOKUP returns #N/A when values look the same.
• We tested VLOOKUP only. We did not test XLOOKUP for this guide.

One-line Summary

Keep a two-column table of every spelling and its standard value, then use this formula and filter for NOT MAPPED to find the labels you still have to handle:

=IFERROR(VLOOKUP(TRIM(B2),CountryMap!$A$2:$B$7,2,FALSE),"NOT MAPPED")

Sources

• VLOOKUP function — Microsoft Support. Backs the exact-match behavior of FALSE, #N/A when no exact match is found, the warning about leading and trailing spaces and nonprinting characters, and the Applies-to list (Microsoft 365, 2024, 2021, 2019, 2016).
• Note: the ten-row result, the CANADA match, the two NOT MAPPED rows, and the TRIM effect on the double-space label come from our own reproduction in the Excel desktop app, not from this page.

Many report exports print a group label once and leave the cells below it empty. Sorting, filtering, or a PivotTable then treats those empty cells as missing data. You can fill each blank with the value above it in a few steps, without copying anything by hand.

This guide uses a small made-up sales list, selects only the blank cells, and fills them with one formula. We also point out what we did not test.

Applies to: Microsoft documents Go To Special and its Blanks option for Excel for Microsoft 365, Excel 2024, and Excel 2021. Tested in the Excel desktop app with an English interface on Korean-language Windows. We did not test other Excel versions, so menu names may differ.

What the Export Looks Like

Our test sheet has three columns: region, rep, and orders. The region is filled only on the first row of each group (East in row 2, West in row 5, and North in row 7). The cells below each one are empty. All names and numbers are fictional.

Step 1: Select Only the Blank Cells

Select the cells of the region column that hold the data. Then open the Go To dialog (Home > Find & Select > Go To, or press Ctrl+G), click Special, choose Blanks, and click OK.

Excel now has only the empty cells selected. The cells that already hold a region are not part of the selection.

Step 2: Type the Formula and Fill All Blanks at Once

Do not click anywhere yet. The first selected blank cell is the active cell. Type a formula that points to the cell directly above it. In our sheet the first blank cell was A3, so we typed this:

=A2

Then press Ctrl+Enter instead of Enter. Excel enters the formula in every selected cell, and each one points to the cell above it.

The Result

Every blank now shows the value above it: East three times, West twice, and North three times. Cell A3 holds =A2, as the formula bar shows. The status bar counted 5 filled cells, which matches the five blanks we started with.

Check These Before You Rely on It

• The cells are formulas, not values. We did not test converting them to values, so check that before you sort or copy the column somewhere else.
• The formula copies whatever is above. If a blank cell is a real gap and not the continuation of a group, it will be filled too. Scan your data first.
• Cells that look empty may not be selected. If Blanks does not select a cell that looks empty, it may contain an empty string. Our guide on Power Query null vs empty string explains the difference. If you want to delete empty rows instead, see removing blank rows without breaking formulas.
• We tested a single column. We did not try several columns at once, a filtered list, or a Power Query Fill Down step.

One-line Summary

Select the column, use Go To > Special > Blanks, type =A2 (a reference to the cell above the first blank), and press Ctrl+Enter.

Sources

• Find and select cells that meet specific conditions — Microsoft Support. Backs the Home > Find & Select > Go To > Special path, the Blanks option (“Blank cells”), the Ctrl+G shortcut, and the Applies-to list (Microsoft 365, 2024, 2021).
• Note: the =A2 formula, the Ctrl+Enter fill, the East/West/North result, and the Count: 5 status bar come from our own reproduction in the Excel desktop app, not from this page.

You import a CSV and the dates look wrong. Some are stuck as text and will not sort or calculate. Some show a five-digit number such as 46086. And some look fine but may have the day and month swapped, which is the worst case because nothing warns you.

This guide covers those three problems: text dates, serial numbers, and swapped day and month. For each one you will see how to recognize it, which formula or setting fixes it, and what we actually saw in Excel when we tried it. It also shows what Power Query does to dates automatically and how to control it.

Applies to: Microsoft documents the DATE function for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Tested in the Excel desktop app with an English interface on Korean-language Windows. How Excel reads a date typed as text can depend on your regional date settings. We did not change ours, so results may differ on your computer.

The screenshots use one made-up table of five dates. The same five dates appear in several formats: ISO text (2026-03-05), US text (03/05/2026), day-first text (05/03/2026), a second US-style column we call ambiguous, and a serial number (46086). All values are fictional.

Step 1: Find Out What Kind of Date You Have

Look at the alignment first. Numbers align to the right by default and text aligns to the left. In our test the four text columns are left-aligned and the serial number column is right-aligned.

Alignment is only a hint, so confirm with ISTEXT. It returns TRUE for text and FALSE for a real number or date. Then try DATEVALUE, which converts a date written as text into a serial number:

=ISTEXT(B2)
=DATEVALUE(RawDates!B2)

This is what we observed in our environment: ISTEXT was TRUE on all five rows. DATEVALUE converted the ISO text (2026-03-05 became 46086) but returned #VALUE! for both 03/05/2026 and 05/03/2026. Our Windows short date format was yyyy-MM-dd, which fits what we saw, but we did not change that setting to prove it is the cause. So do not assume DATEVALUE will read your format. Test it on a few rows before you rely on it.

Fix Dates Stored as Serial Numbers

A value such as 46086 is a real number. Excel stores a date as a number and the cell format decides how it looks. To see the date, keep the number and apply a Date format to the cell:

1. Select the cells that hold the serial numbers.
2. On the Home tab, open the Number Format box and choose Date.

In our test the formula in the new column was just =B2 and the Date format did the rest.

The five serial numbers became 2026-03-05, 2026-03-14, 2026-04-05, 2026-11-30, and 2026-01-02, which matched our answer key. How the date is displayed (2026-03-05 here) can follow your computer’s regional settings, so yours may look different.

Fix Dates Stored as Text When You Know the Order

If DATEVALUE fails, you can build the date from its parts with DATE, RIGHT, LEFT, and MID. This works only if you know whether the text is month-first or day-first. For month-first text such as 03/05/2026, where the month is the first two characters:

=DATE(RIGHT(B2,4),LEFT(B2,2),MID(B2,4,2))

RIGHT(B2,4) is the year, LEFT(B2,2) is the month, and MID(B2,4,2) is the day. Format the result cell as Date.

In our test all five rows came out correct, including 03/14/2026 (2026-03-14) and 11/30/2026 (2026-11-30). This formula assumes a two-digit month and a two-digit day with slashes, as in our file. We did not test text such as 3/5/2026, so check your own values first.

The Risky One: Day and Month Swapped

Some text dates cannot be read in only one way. 03/05/2026 could be March 5 or May 3. In our data the true date was March 5, but nothing in the text says so.

We compared two readings of the same column. The first reads the text as month-first. The second assumes day-first and uses the same functions with the pieces swapped:

=DATE(RIGHT(B2,4),LEFT(B2,2),MID(B2,4,2))
=DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))

Text	Read as month first	Read as day first (assumed)
03/05/2026	2026-03-05	2026-05-03
04/05/2026	2026-04-05	2026-05-04
01/02/2026	2026-01-02	2026-02-01
03/14/2026	2026-03-14	2027-02-03
11/30/2026	2026-11-30	2028-06-11

Two things stand out. When both numbers are 12 or less, both readings are valid dates and they differ, so you cannot tell which is right from the value alone. And when the second number is above 12, such as 03/14/2026, the day-first reading did not return an error. It returned 2027-02-03. Microsoft’s DATE page explains why: if the month is greater than 12, DATE adds that many months to the first month of the year you gave. So an assumed day-first formula on month-first text can quietly produce a wrong date in a later year.

These results come from our assumed day-first formula. They are not what Excel decided on its own. The practical rule: before you convert, scan the column for a value above 12 in the first or second position. A first number above 12 means day-first text. A second number above 12 means month-first text. If every row is 12 or less, ask whoever produced the file which order they used.

What Power Query Does to Dates Automatically

When you import with Data > From Text/CSV or From Table/Range and open the Power Query Editor, Excel adds a Changed Type step for you. In our test it turned both month-first columns into dates and left the day-first column as text.

The values in those two columns looked right (2026-03-05, 2026-03-14, and so on), but the step’s formula does not say which date format it assumed. That is why you should compare a few converted rows against the source, especially rows where both numbers are 12 or less.

If you know the source uses month-first dates, you can set the type with a locale. In our test the Applied Steps list had a Changed Type step that set those two columns back to Text. We selected both columns and opened Change Type with Locale. We chose Date as the data type and English (United States) as the locale.

In our Excel build the Home tab’s Data Type list did not include a Using Locale item, so we reached the dialog another way. Menu names and locations can differ by version. If you cannot find it, look for a Change Type option that mentions a locale.

The result matched our answer key on all five rows. The step’s formula now includes "en-US", so the locale is written down. This is only correct when the source really is month-first. We tested only the English (United States) locale, not other locales.

Which Fix to Use

What you see	Check	Fix	Our test
Five-digit number such as 46086	Right-aligned, ISTEXT is FALSE	Apply the Date format	Worked on 5 of 5 rows
Text date that DATEVALUE can read	ISTEXT is TRUE, DATEVALUE returns a number	DATEVALUE, then Date format	Worked for ISO text
Text date that DATEVALUE rejects	DATEVALUE returns #VALUE!	DATE with LEFT, MID, RIGHT in the right order	Worked on 5 of 5 rows (month-first)
Both numbers 12 or less	Look for rows above 12 or ask the source	Confirm the order first, then convert	Wrong order gave different dates
Dates imported through Power Query	Compare against the source	Change Type with Locale	Worked with en-US

Which Version of Excel Do You Need?

Microsoft lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 for the DATE function. We tested only one desktop Excel build (English interface, Korean-language Windows). Power Query menus and DATEVALUE results can differ in other versions and regional settings.

One-line Summary

Check whether your dates are text, serial numbers, or ambiguous, then fix each with the matching tool: the Date format for serial numbers, DATE with LEFT, MID, and RIGHT for text with a known order, and Change Type with Locale in Power Query. Never convert text like 03/05/2026 until you know whether the month or the day comes first.

If your imported IDs or long numbers are also changing, see our guides on keeping leading zeros and long numbers that turn into scientific notation.

Sources

• DATE function — Microsoft Support. Backs the statement that a month greater than 12 adds that many months to the first month of the year, and the Applies-to list. It also shows how to build a date from text with DATE, LEFT, MID, and RIGHT.
• Note: the ISTEXT and DATEVALUE results, the serial-number display, the month-first and day-first comparison, the automatic Changed Type step, and the Change Type with Locale result come from our own reproduction in the Excel desktop app, not from this page.

You open a CSV in Excel and a 16-digit tracking number turns into 1.23457E+15. A 12-digit reference number turns into 1.23457E+11. Both look like the same problem, but they are not. Excel only changed how the 12-digit number is displayed. For the 16-digit number, Excel also changed the stored value.

This guide explains how to stop Excel scientific notation on long numbers by importing the CSV with Data > From Text/CSV and setting those columns to Text before the data reaches the worksheet. It also shows why a number that has already lost digits cannot be repaired inside Excel, so the original file is what you need.

Applies to: Microsoft documents the 15-digit limit, the leading-zeros and large-number behavior, and the From Text/CSV import for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Tested in the Excel desktop app with an English interface on Korean-language Windows. We did not test other Excel versions, so menu names may look slightly different there.

The screenshots use a small made-up CSV with a 16-digit tracking_no column (1234567890123456, 9876543210987654, and so on) and a 12-digit ref_no column (123456789012, 987654321098, and so on).

What Goes Wrong When You Double-Click a CSV

When you open a .csv file directly, Excel applies its default format settings to each column. For long digit strings, that means they are read as numbers.

In our test, tracking_no appeared as 1.23457E+15 and ref_no as 1.23457E+11. Click cell E2 and look at the formula bar. The CSV contains 1234567890123456, but the formula bar shows 1234567890123450. The last digit has already changed from 6 to 0.

Two Different Problems: Display vs. Lost Digits

We selected both columns, applied the Number format with 0 decimal places, and widened the columns.

The two columns behaved differently:

Column	In the CSV	In Excel after Number format	What it means
ref_no (12 digits)	123456789012	123456789012	Display problem only. The value was kept.
tracking_no (16 digits)	1234567890123456	1234567890123450	The stored value changed.

Across all 8 rows of our test file, the 12-digit ref_no values matched the CSV exactly once we applied the number format, and the 16-digit tracking_no values had their last digit replaced with 0 in all 8 rows. (We checked the formula bar only for E2, so the other rows are based on the values shown in the cells.)

This matches what Microsoft documents: Excel keeps a maximum of 15 significant digits, and for numbers with 16 or more digits, the digits after the 15th become zeros. A 12-digit number is within that limit, so Excel kept it. Changing the number format afterward did not bring the last digit of tracking_no back, and Microsoft says the same about formats applied after the fact. We tested only the Number format.

The Safest Method: Import With Data > From Text/CSV

This is the same method as in our guide to keeping leading zeros in ZIP codes and ID numbers, applied to long numbers.

1. Do not double-click the CSV. Open a new blank workbook in Excel instead.
2. Go to the Data tab, click From Text/CSV, select your file, and click Import.
3. In the preview window, click Transform Data. This opens the Power Query Editor.
4. In the Applied Steps pane on the right, find the Changed Type step that Excel added automatically. In our test it had set tracking_no and ref_no to whole numbers (the 123 icon in the column header), and the values already appeared as 1.23457E+15 and 1.23457E+11. We deleted this step before setting Text. Click the X next to Changed Type to delete it.

5. Click the data type icon at the left of the tracking_no column header and choose Text. Repeat for ref_no. Both columns should show the ABC icon and the full values, such as 1234567890123456 and 123456789012.

6. On the Home tab, click Close & Load.

Select E2 in the new worksheet. The formula bar should show 1234567890123456.

In our test, all 8 tracking_no values and all 8 ref_no values matched the original CSV. Keep identifiers such as tracking numbers, account numbers, and card-style codes as Text. If you ever need to calculate with a column, convert it to numbers first. Be aware that a number with more than 15 digits would lose digits at that point, so do not convert identifiers like these.

If You Already Opened the CSV the Usual Way

Once Excel has converted a 16-digit number, the cell no longer contains the original last digit. The formula bar in the first screenshot shows 1234567890123450, and reformatting the cell does not change it. Microsoft’s guidance is to use the Text format for codes with 16 or more digits.

Go back to the original file:

1. Close the workbook without saving.
2. Re-import the original CSV with the steps in the previous section.

In our test we closed the double-clicked file without saving and the original CSV was unchanged. The cells hold the changed values, so anything you save from them cannot contain the lost digits. We did not test what happens when you save the workbook over the original, so keep the original file untouched. If you saved the converted data over the only copy of the CSV, check file history, backups, or the system that produced the file. Do not try to guess the missing digits from the worksheet.

A 12-digit number that only looks like 1.23457E+11 is different. Its value was kept, and the number format changes how it is displayed. If you still have the original CSV, importing it as Text is the cleaner path for both columns.

Which Version of Excel Do You Need?

Microsoft lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 as applying to the source pages below. This guide uses no new functions. We tested only one desktop Excel build (English interface, Korean-language Windows), so button names in other versions may differ slightly.

One-line Summary

Excel keeps only 15 significant digits, so do not double-click a CSV that contains 16-digit numbers. Import it with Data > From Text/CSV, click Transform Data, delete the automatic Changed Type step, set those columns to Text, and click Close & Load. If the digits are already zeroed out, re-import the original file.

Sources

• Keeping leading zeros and large numbers — Microsoft Support. Backs the 15-significant-digit limit, the digits after the 15th becoming zeros, the advice to use Text for numbers with 16 or more digits, and that applying a format afterward does not change what was already entered.
• Excel specifications and limits — Microsoft Support. Lists number precision as 15 digits in the calculation specifications.
• Import or export text (.txt or .csv) files — Microsoft Support. Backs the Data > From Text/CSV path and the statement that opening a .csv directly applies Excel’s default format settings.
• Note: the 1.23457E+15 and 1.23457E+11 displays, the 1234567890123450 formula bar value, the 8/8 and 8/8 row comparison, and the full values after Close & Load come from our own reproduction in the Excel desktop app, not from these pages.

You open a CSV in Excel and the ZIP code 02134 turns into 2134. The customer ID 000123 becomes 123. In our test CSV, the zeros remained in the source file. They disappeared when Excel converted those columns to numbers as it opened the file.

The safest fix is to import the file with Data > From Text/CSV and set those columns to Text before the data reaches the worksheet. This guide shows how to keep leading zeros in Excel when you import a CSV: the exact steps, what each screen should look like, and what to do if you have already opened the file the usual way.

Applies to: Microsoft documents the leading-zeros behavior and the From Text/CSV import for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Tested in the Excel desktop app with an English interface on Korean-language Windows. We did not test other Excel versions, so menu names may look slightly different there.

The screenshots use a small made-up CSV with a customer_id column (000123, 000457, 001002, and so on) and a zip column (02134, 07030, 90210, and so on).

Free practice workbook. Download the CSV Import Cleanup Pack (one .xlsx file, no email address or sign-up needed). The CSV files are not included as separate downloads: to practice, copy the text from the workbook’s 1a_orders_csv and 1b_dates_csv sheets into Notepad and save them as .csv files, following the 2_Practice sheet. We tested the workbook in Excel 2021 on Windows with an English interface and Korean regional settings; other versions, regional settings, Mac and Excel Online were not tested.

What Goes Wrong When You Double-Click a CSV

When you open a .csv file directly, Excel interprets each column using its default data format settings, which may not match what you need. Microsoft’s own example is a column of numbers with leading zeros that you want to keep as text. Microsoft also explains that Excel removes leading zeros so formulas and math operations can work on the values.

Click a cell such as A2 and look at the formula bar. It shows 123, not 000123, so the cell now contains the number 123. In our test the zip_plus4 column kept its zeros (02134-1234). Those values contain a hyphen, so Excel did not treat them as plain numbers.

Opening a CSV can change other kinds of data too. See our guides on importing long numbers without scientific notation or lost digits and on fixing dates after importing a CSV. If you have a folder of CSV files to combine, see our guide on combining CSV files from a folder with Power Query.

The Safest Method: Import With Data > From Text/CSV

1. Do not double-click the CSV. Open a new blank workbook in Excel instead.
2. Go to the Data tab, click From Text/CSV, select your file, and click Import.
3. In the preview window, click Transform Data. This opens the Power Query Editor.
4. In the Applied Steps pane on the right, look for a step named Changed Type. Excel added it automatically, and in our test it set customer_id and zip to whole numbers (Int64), which is where the zeros were lost. Click the X next to Changed Type to delete it.

5. Click the data type icon at the left of the customer_id column header and choose Text. Repeat for zip. Both columns should now show the ABC icon and values such as 000123 and 02134.

6. On the Home tab, click Close & Load.

Check the result in the new worksheet. Select A2 and the formula bar should show 000123.

Keep columns such as ZIP codes and IDs as Text. If you later need to calculate with a column, convert it to numbers first.

If You Already Opened the CSV the Usual Way

Once Excel has converted the cells to numbers, the zeros are no longer in the cell values. The formula bar in the first screenshot shows this: it holds 123. Microsoft notes that applying a number format does not bring back leading zeros that were already removed, so reformatting those cells is not a reliable recovery.

The fix is to go back to the original file:

1. Close the workbook without saving.
2. Re-import the original CSV with the steps in the previous section.

In our test we closed the double-clicked file without saving, and the original CSV was unchanged. We opened the same file in Notepad and it still contained 000123 and 02134. We did not test what happens if you save the workbook over the original, so keep the original file untouched. If you saved the converted data over the only copy of the CSV, check file history, backups, or the system that produced the file. Excel cannot reconstruct the original leading zeros from the converted numeric cells alone.

Typing Values Directly Into Cells

This guide covers data that arrives in a CSV. If you type ZIP codes or IDs yourself, Microsoft’s guidance is to format the cells as Text before you enter the values, or to type an apostrophe before the number. We did not test those methods here.

Which Version of Excel Do You Need?

Microsoft lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 as applying to both source pages below. This guide uses no new functions. We tested only one desktop Excel build (English interface, Korean-language Windows), so button names in other versions may differ slightly.

One-line Summary

Do not double-click a CSV that contains ZIP codes or ID numbers. Import it with Data > From Text/CSV, delete the automatic Changed Type step, set those columns to Text, and click Close & Load. If you have already opened the file, close it without saving and import it again.

Sources

• Keeping leading zeros and large numbers — Microsoft Support. Backs the statements that Excel removes leading zeros so formulas and math operations can work, that formatting does not restore zeros removed earlier, and the Text-format and apostrophe options for typed values. It also describes converting columns to Text in the Power Query Editor and using Close & Load.
• Import or export text (.txt or .csv) files — Microsoft Support. Backs the Data > From Text/CSV path and the statement that opening a .csv directly applies Excel’s default format settings, with a column of numbers that contains leading zeros as the example.
• Note: the automatic Changed Type step setting customer_id and zip to Int64, the 123 value in the formula bar, and the 000123 result after Close & Load come from our own reproduction in the Excel desktop app, not from these pages.

Go To Special > Blanks > Delete Entire Row is the shortcut most people reach for first, but it selects blank cells — not rows that are completely empty. If your range contains partially filled records, that shortcut can delete rows you meant to keep. If another formula explicitly references one of the deleted rows, you can also end up with a #REF! error.

This guide shows why the shortcut is risky and walks through a safer method that deletes only rows that are fully blank.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Both the shortcut and the safer method below use only long-standing Excel features, so the behavior is the same across every version in this list.

The Standard Shortcut (and Why It’s Risky)

1. Select the data range you intend to clean.
2. Press Ctrl+G, click Special, choose Blanks, then click OK.
3. Excel selects every blank cell in that range.
4. If you then choose Delete > Entire Row, every row that contains one of those selected blank cells gets deleted — not just rows that are entirely empty.

That last step is the trap: a row with just one missing field looks nothing like a “blank row” to the person cleaning the data, but Excel treats it the same way once any cell in it is selected as blank.

Why Partially Blank Rows Can Disappear

Suppose a customer list contains:

• a row that is completely blank,
• a record for Bob with a missing phone number,
• a record for Dave with a missing email address.

Bob and Dave are not blank records — they are incomplete records. But Go To Special > Blanks selects Bob’s empty phone cell and Dave’s empty email cell right alongside the cells in the truly blank row. Delete entire rows from that selection, and Bob and Dave can be removed along with the actual blank rows.

That’s a data-loss problem on its own, before any formula is even involved.

If your report prints a group label only on the first row, see our guide on filling blank cells with the value above.

When #REF! Shows Up

Microsoft documents #REF! as the error Excel shows when a formula’s cell reference is no longer valid — most often because the referenced cells, rows, or columns were deleted. Deleting rows doesn’t automatically break every formula in a sheet; Excel adjusts many range references on its own. The failure case is clearest when a formula explicitly names a row that then gets deleted by the blanks shortcut. In that situation, the formula returns #REF! because the exact cell it pointed to no longer exists.

If you catch the mistake right away, Ctrl+Z is the fastest way to recover before doing anything else.

The Safer Method: Delete Only Fully Blank Rows

Add a helper column that counts how many non-empty cells each record has, using COUNTA.

For a three-column dataset in columns A:C, starting at row 4:

=COUNTA(A4:C4)

Fill the formula down the full range. Then read the result:

• 0 — the row is completely blank across the checked columns.
• 1 or more — at least one field has data, so the row is incomplete, not blank, and shouldn’t be deleted by this pass.

Now clean up safely:

1. Filter the helper column to 0.
2. Delete only the visible, fully-blank rows.
3. Clear the filter.
4. Remove the helper column once you’re done.

This keeps Bob’s and Dave’s partially filled records intact while still clearing out the rows that are genuinely empty.

Formula Design Also Reduces the Risk

Beyond the helper-column cleanup itself, how you write formulas in the first place affects how much a future row deletion can break. A continuous range adjusts automatically when rows inside it are removed; a formula built from a list of individual cell references does not adjust the same way. For example:

=SUM(B2:D2)

is generally more resilient to structural edits than:

=SUM(B2,C2,D2)

This is a separate safeguard from the COUNTA helper — the helper stops you from deleting the wrong rows in the first place, while range-based formulas reduce how much damage a future structural change can cause even when it does happen.

One-line Summary

Go To Special > Blanks selects blank cells, so deleting entire rows from that selection can remove partially filled records along with truly empty ones — and can break any formula that explicitly references a deleted row with #REF!. If you only want rows that are completely empty, filter on a COUNTA(...)=0 helper column first.

Sources

• Find and select cells that meet specific conditions in Excel — Microsoft Support. Documents the Go To Special > Blanks behavior this article builds on.
• How to correct a #REF! error — Microsoft Support. Backs the explanation of when and why #REF! appears after a deletion.
• COUNTA function — Microsoft Support. Reference for the COUNTA helper-column method.
• Range-vs-list formula resilience and the exact #REF! reproduction (a formula explicitly referencing a row deleted by the Blanks shortcut) were confirmed by this project’s own reproduction in Excel, recorded in the Pilot G entry of the project decision log and this article’s QA package — not solely from the official pages above.

If a column of numbers is left-aligned, shows a small green triangle in the corner of each cell, or calculations such as SUM ignore the values, Excel may be treating those values as text rather than numbers. This is common after importing data from CSV files, databases, or copy-pasting from a website.

Here are four ways to fix it in bulk, plus a hidden-character case that can make a value look like it’s still text after you’ve tried one of them.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. All four methods below use only long-standing Excel features (no new dynamic-array functions), so they work the same way across every version in this list.

Method 1: Error Checking Button (fastest when available)

If Excel’s background error checking is on, cells with text-stored numbers can show a small green triangle in the top-left corner.

1. Select the cells you want to convert.
2. Click the warning icon that appears next to the selection.
3. Choose Convert to Number.

If you do not see the warning icon, background error checking may be disabled under File > Options > Formulas > Enable background error checking.

Method 2: Paste Special → Multiply

This method works even when the error-checking indicator is unavailable:

1. Type 1 in an empty cell.
2. Copy that cell.
3. Select the range containing numbers stored as text.
4. Open Paste Special.
5. Choose Multiply under Operation, then click OK.

Multiplying by 1 forces text-formatted numeric values to become numeric values while leaving their displayed numbers unchanged.

Method 3: VALUE() Helper Column

For mixed or messy imports, a helper column makes the conversion visible and easier to audit:

1. In a new column, enter =VALUE(A2) and adjust the reference as needed.
2. Fill the formula down.
3. Copy the converted results.
4. Use Paste Special > Values if you want to replace the original text values.
5. Remove the helper column when finished.

VALUE converts text that Excel recognizes as a valid number, date, or time into a numeric value.

Method 4: Text to Columns

Text to Columns can also cause Excel to reinterpret simple numeric text using the General column format.

1. Select the column containing the text-stored numbers.
2. Go to Data > Text to Columns.
3. Keep the column format as General.
4. Finish the wizard.

Use this method carefully if the source contains leading zeros, long identifiers, dates, or mixed text-and-number values, because automatic conversion may change how those values are represented.

If Conversion Still Fails, Check for Hidden Characters

A value can look numeric while still containing a hidden character, such as a non-breaking space (NBSP, Unicode character 160) left over from a web copy-paste or an export from another system. When a value contains one, the methods above may not convert it, because Excel still sees a text string rather than a clean number.

TRIM removes ordinary spaces (the character you get from pressing the spacebar, code 32) and collapses multiple spaces between words down to one — but by itself it does not remove the non-breaking space character. CLEAN removes the first 32 nonprinting ASCII control characters (codes 0–31); the non-breaking space falls outside that range, so CLEAN does not address it either. In other words, TRIM and CLEAN are each scoped to a different kind of unwanted character, and neither is designed to target a literal U+00A0.

For a closer look at this, see our guide on why TRIM isn’t removing non-breaking spaces.

To target the non-breaking space specifically, use UNICHAR(160), which returns the Unicode character for code point 160 — the non-breaking space — so it can be matched and replaced explicitly:

=VALUE(TRIM(SUBSTITUTE(CLEAN(A2),UNICHAR(160)," ")))

For readability on smaller screens, here is the same formula wrapped across lines (the single-line version above is the one to copy):

=VALUE(
  TRIM(
    SUBSTITUTE(
      CLEAN(A2),
      UNICHAR(160),
      " "
    )
  )
)

This formula uses CLEAN to strip ordinary control characters, SUBSTITUTE with UNICHAR(160) to swap the literal non-breaking space for a regular space, TRIM to clean up the result, and VALUE to convert the cleaned text into a number.

Which Version of Excel Do You Need?

All five techniques in this article — the four bulk-conversion methods and the UNICHAR fix — work in Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. None of them depend on newer dynamic-array functions like UNIQUE, FILTER, or TEXTSPLIT, so there is no version-specific fallback needed here.

One-line Summary

For a quick one-time fix, use Convert to Number or Paste Special > Multiply. For mixed imported data, use a helper formula such as VALUE() so you can see which rows converted successfully before replacing the source values. If a value still won’t convert, check for a hidden non-breaking space and use the UNICHAR(160) formula above.

Sources

• Convert numbers stored as text to numbers in Excel — Microsoft Support. Backs Method 1 (error-checking indicator, Convert to Number) and Method 3 (VALUE as a helper-column method).
• Fix text-formatted numbers by applying a number format — Microsoft Support. Backs Method 2 (Paste Special with Multiply).
• VALUE function — Microsoft Support. Reference for the VALUE function used in Method 3 and in the hidden-character formula.
• TRIM function — Microsoft Support. Explicitly documents the non-breaking space (decimal 160) and states that TRIM alone does not remove it — the basis for the “Check for Hidden Characters” section.
• CLEAN function — Microsoft Support. Documents that CLEAN removes only the first 32 nonprinting ASCII control characters, which do not include the non-breaking space.
• UNICHAR function — Microsoft Support. Reference for UNICHAR, which returns the Unicode character for a given code point (160 for the non-breaking space).
• Split text into different columns with the Convert Text to Columns Wizard — Microsoft Support. General reference for the Text to Columns wizard used in Method 4. Note: this page does not document the specific behavior of “General” format reinterpreting text-stored numbers — that claim rests on this project’s own reproduction in Excel, not on this official page.