Every check, with its fix

A required cell is empty.

1. Filter the column for empty cells.
2. Fill them in, or untick "required" if the column can be empty.

There are spaces at the start, at the end or doubled inside the value. Two values that look the same can then fail to match.

1. Use the spreadsheet's TRIM function in a helper column.
2. Copy the helper column and paste it over the original as values only.
3. Delete the helper column.

Letters like ñ or é show as "ñ" or "é". The file was saved in one encoding and read in another.

1. Open the original spreadsheet (not the CSV).
2. Save it again choosing "CSV UTF-8".
3. Open the new file in the app.

The digit after the hyphen is the NIT check digit (DV). DIAN computes it from the other digits with módulo 11, so a typo in either part shows up here.

1. Compare the number with the RUT of that company.
2. Correct the base number or the check digit.
3. Keep the format digits, hyphen, digit (for example 900123456-8).

The NIT has no check digit after a hyphen. Systems often store the base number and the check digit separately, so it must be clear which is which.

1. Decide one format for the whole column.
2. Add the check digit after a hyphen, or move it to its own column.

The value is not a NIT: it has letters, too few digits or more than one check digit.

1. Check whether the value belongs in another column.
2. Write the NIT as digits, a hyphen and one check digit.

A cédula, TI, RC or similar document number has digits only. Letters or symbols usually come from a typo or a merged cell.

1. Filter the column for values with letters.
2. Retype them from the document.

The number is shorter or longer than usual for its document type. It may be right, so check it rather than delete it.

1. Look at the document or another record of the same person.
2. Change the range in the column settings if your data is different.

The ID-type column has a value the app does not recognise.

1. Use one code per type: CC, CE, TI, RC, PPT, PA or NIT.
2. Replace long or misspelled forms with the code.

Since the single dialling plan of Resolución CRC 5826 de 2019 (coexistence from 1 September 2021), fixed lines have 10 digits: 60, the department digit and the 7-digit number. A 7-digit number is missing that prefix.

1. Find the department of each number (the app uses the row's department or municipality when it can).
2. Add 601 to 608 in front, by department (see the guide).
3. Keep one format for the whole column.

A Colombian mobile has 10 digits starting with 3; a fixed line has 10 starting with 601 to 608. This value has another length or start.

1. Check for missing or extra digits.
2. Remove text, letters and extensions, or move extensions to their own column.

The cell holds two or more numbers. A system field usually takes one.

1. Split the cell into two columns (Text to columns, or the program's split tool).
2. Mark the second column as Phone too.

The value is not an email address: it has no @, a space inside or no domain ending.

1. Correct it from the original record.
2. Leave the cell empty if there is no email.

The domain looks like a common typo (for example gmial.com for gmail.com). Mail sent there will not arrive.

1. Use Find and Replace on the domain part only.
2. Check the address with the person if unsure.

The value is not a date the app can read, or it is the number a spreadsheet uses internally for a date.

1. Format the column as a date in the spreadsheet before exporting.
2. Use day/month/year, the Colombian order.

The date does not exist (for example 31 February).

1. Check the original record.
2. Correct the day or the month.

Day and month are both 12 or less, and nothing in the column shows which comes first. 03/04 could be 3 April or 4 March.

1. Agree one order for the whole column: day/month/year in Colombia.
2. If possible, write the year first (2024-04-03), which reads only one way.

Most rows write the day first, but this one writes the month first (or the other way round).

1. Sort the column and look at the rows with a first part above 12 and the rest.
2. Rewrite the odd rows in the column's order.

The date is later than today in a column where that should not happen (for example birth dates).

1. Check the year.
2. Untick "no future dates" if future dates are expected.

The value is not an amount: it has text or a separator the app cannot read.

1. Write amounts with digits only, or with "." for thousands and "," for cents.
2. Move currency words to the header.

Some rows use "." for thousands and "," for cents (Colombian style) and others the opposite. A system will read one of the two groups wrongly, by a factor of 1.000.

1. Choose one style for the column.
2. Use Find and Replace carefully, or format the column as a number before exporting.

The amount is below zero. It may be right (a credit note) or a typo.

1. Check the original record.

The amount is far above the others in the column (more than three times the usual spread above the upper quarter). An extra zero or a lost comma often causes it.

1. Sort the column from largest to smallest.
2. Check the first rows against the original records.

A quantity should be a whole number.

1. Remove text and decimals, or use the Amount meaning if the column holds money.

The quantity is below zero.

1. Check whether it is a return or a typo.

The department is not one of the 33 in the DANE DIVIPOLA list.

1. Use the official name (the app shows the nearest).
2. Avoid abbreviations.

The municipality is not in the DANE DIVIPOLA list, even ignoring accents and capital letters.

1. Use the official name (the app shows the nearest).
2. Move neighbourhoods and veredas to an address column.

The municipality exists, but not in the department written on the same row.

1. Check which of the two is right.
2. Some names exist in several departments (La Unión, Granada…): keep the department column filled.

Two or more rows have the same key after ignoring capital letters, accents, spaces and phone or ID punctuation.

1. Sort by the key columns.
2. Merge or delete the repeated rows in the spreadsheet.
3. Choose other key columns in the settings if these are not unique by nature.