Cleaning up text
An export from a CRM is rarely ready to use right away. Here are the orders for one day: there are four orders from Miami on the sheet, but COUNTIF in C8 counted two. SUM in C9 shows 0, even though the amounts are there.
To make an export ready for calculations, you prepare it:
- find and remove whatever stops COUNTIF from recognizing Miami;
- turn the amounts into numbers that SUM can add up;
- join the first and last name into one cell for a mailing.
A space is a character too
C3 and C6 have six characters each. The sixth one is a space: in C3 it comes after the word, in C6 before it. Excel compares text character by character, so “Miami” with a space and plain “Miami” are different words to it, even though you can't tell them apart on the sheet.
Spaces like these are left by the program the data came from, or by a person who typed the city by hand. The TRIM function removes them: it erases spaces at the start and at the end of the text and replaces several spaces in a row between words with one. It has a single argument, the cell with the text: =TRIM(A2).
You write the formula in an empty column next to the data and drag it down. But COUNTIF looks at the original column, so the clean text has to go back there. Copy the cells with the formulas, then paste them into the original column from the right-click menu: Paste Special → Values. A regular paste would bring the formulas along, and this one puts in only the finished text.
Amounts with a label
SUM returned 0 because every amount has “ USD” attached to it. Excel sees text in these cells and skips them.
The Find and Replace window removes extra characters. Open it with Ctrl + H (⌘ + Shift + H on a Mac) or on the Home tab: Find & Select → Replace….
- Select the cells you want to clean up. If only one cell is selected, Excel searches the whole sheet.
- In the Find what box, type what's in the way, for example “ USD” with a space at the start. Leave the Replace with box empty.
- Click Replace All. Excel tells you how many replacements it made.
Joining with &
A mailing needs each client's full name in one cell, but the CRM exports the first and last name in separate columns. You can join them with the & sign. It connects text from cells in order, and the space between the words goes in as a separate piece in quotes:
=A2&" "&B2
The quotes can hold any text, not just a space. That's how you build a greeting for an email, for example: ="Hello, "&A2&"!".
Joining always gives text, even if you joined numbers. If you attach “ USD” to an amount, you get text like “1500 USD” again, and SUM skips it. To show a dollar sign next to a number, give the cell the Currency format.
& in conditions: a threshold from a cell
An order over $5,000 counts as large, but the threshold changes from time to time. To avoid rewriting formulas, it's kept in a separate cell, for example F1. A condition made of just the cell F1 would find only amounts equal to the threshold. And in ">F1" everything is inside the quotes, so to the function F1 is just two characters, not a cell.
What helps is that a condition for COUNTIF is text anyway, and text is built with the & sign. The comparison sign goes in quotes, and the number is attached from the cell:
=COUNTIF(B2:B9,">"&F1)
If F1 holds 5000, joining ">"&F1 gives the text “>5000”: exactly the condition you used to write in quotes as a whole. Change the threshold, and the condition is rebuilt.
SUMIF and SUMIFS work the same way: a condition with a threshold from a cell is built with the same join.