Course / Clean & Check Data

Repairing numbers and names

Turn text into real numbers, strip units like kg, and learn when capital letters matter and when they don't. Intermediate · 6 min · VALUE, --, SUBSTITUTE + VALUE, COUNTIF and case, EXACT

A farm shop logs its deliveries in a phone app, and the export is a mess. Some crate counts arrived as text, the weights have "kg" glued on, and the supplier names are typed every which way.

None of this is obvious at a glance, but it matters. SUM and AVERAGE skip anything that is text, so totals come out short without any warning. Look at B11: it says 24, but 55 crates were delivered.

You'll repair each column with a formula, then find out which kinds of mess COUNTIF actually cares about.

Steps

1. Half the crate counts in column B are text. In D2:D9, turn every count into a real number with =VALUE(B2), and keep an eye on the total in D11.

2. VALUE can't read 54 kg, because the letters get in the way. In E2:E9, delete the kg with SUBSTITUTE first, then convert what is left with VALUE.

3. Now the supplier names. In F2:F9, tidy them with =PROPER(TRIM(A2)), the pairing you met in the Working with Text track.

4. In B14:B16, count each supplier's deliveries with COUNTIF, searching the clean names in column F.

5. Sometimes case does matter, for example when another system will match names character for character. In G2:G9, check which raw names were already typed exactly right with =EXACT(A2, F2).

Remember

• VALUE(B2) or --B2 turns a number stored as text into a real number.
• Strip units first, then convert: VALUE(SUBSTITUTE(C2, "kg", "")).
• COUNTIF, UNIQUE and = ignore capitals, but not extra spaces.
• EXACT is the case-sensitive check.

Previous: Text surgery with nested formulas · Next: Payoff time, true rates and savings targets