You can use Apple’s Numbers app for sophisticated spreadsheet work. But where it falls short—as does Microsoft Excel—is at cleaning up data that’s in the spreadsheet. Where Microsoft Word lets you use patterns to match and replace values, you can’t make that happen in Numbers, Excel, or Pages.
This can come up when you have dates, numbers, or currency values formatted in different ways that are imported or pasted into Numbers. Numbers will correct many kinds of standard formatting when typed in, but the same data may remain in its original, varied form when pasted in or when you open a CSV (comma-separated value) file, a common export format from other software and from websites.
You see this most frequently with names, where you receive a list formatted in one fashion—like “last name, first name”—and you want it another. Or each part of the data you have formats the name in different ways. One might split first, last, and middle initial into three fields. What you want, however, is normalized data—entries that are structured identically to allow them to be sorted, visually scanned, and organized in other ways.
There’s a remarkably simple solution that costs nothing and might save a lot of manual re-entry and editing. Numbers lets you copy the contents of any number of cells and then paste them into a text editor. As long as you retain tabs and hard returns while editing, you can fix up what you pasted in the text editor, copy the revised data, and paste it over the exact area you copied from in Numbers.
I rely on BBEdit to perform advanced wildcard matches called “regular expression patching matching” (regexp, for short) and to replace data with matched elements. Some apps support wildcards, where you drop in special characters or sequences to match parts of text, like any number (0 to 9) or a full sentence, defined by being between periods and other ending punctuation. Regexp is sophisticated, almost a simple programming language. Elements you match in a search can be grouped so that you can rearrange them as part of a text replacement. BBEdit labels the find part Grep after a Unix version of regexp. (The free version of BBEdit has all the features you need. Download the standard version and install it, and you have access to all features during a 30-day trial. When the trial ends, the app “downgrades” to a more limited feature set that includes all the find-and-replace options required in this article.)
For instance, say you have data that lists names in the following way:
Jane Doe (first and last in one field) Jane and Doe (first and last in separate fields) Doe, Jane (last name first, a comma, and then the first name)
For formatting and spreadsheet purposes, you decide you want all names divided into a first name and last name field, so you can sort by last name, but use the first name separately when exporting to an email list program.
First, make sure you have two adjacent fields in Numbers for first and last name. If you have a single “full name” field, select a cell in that field and press Option-right arrow to add a column after that name column. Re-label your name columns First Name and Last Name for clarity.
Next, copy the data by dragging a selection, either around the contents of a single field or both fields if you have a mixed set of data.
Now, paste into BBEdit. You’ll see that each field is separated on a line by a tab. (If you want to view tabs, spaces, returns, and other characters, choose View > Text Display > Show Invisibles.)
You can compose your find/replace values to fix the data. Choose Search > Find and check the Grep box to enable regexp. To fix all the Doe, Jane by putting them into two fields in the right order, you would use this search:
^(.*?), (.*)$
That pattern means: match from the start of the line (^), find all the text until the first comma ((.*?)) and group that to use in a replacement line; then find a command and space, and then make a second group of all the text ((.*)) to the end of the line ($).
In the Replace field, you would enter:
2t1
Much simpler! That says, take the second grouping from the Find pattern (2), follow it with a tab (t), and then with the first grouping (1). Doe, Jane becomes Jane[tab]Doe.
You can click Replace All if that pattern is isolated or the entire set of names. Or you can select a range of text in BBEdit and check “Selected text only” in the Find dialog before clicking Replace All.
Warning: Make a copy before moving back to Numbers: if you have more rows or columns in your copied selection than what you started with or intend to fill, Numbers allows a paste to overwrite your other data.
Once all the data has been reformatted as you like, select it in BBEdit, copy it, and then in Numbers, select the starting cell that you want to paste into. The pasted rows will proceed downwards, and tabs will be converted to past into column cells to the right.
In one recent import, I had a very small change to make that was still beyond Numbers capability: dates and times were formatted as 2019/10/28, 22:51, and Numbers doesn’t recognize the comma as part of a datestamp. I could have round-tripped the data through BBEdit for a simple find of the comma and replaced it with nothing. But it’s a simpler case to illustrate, as you can see in the figure below.
Consult BBEdit‘s manual for more details on regexp. The developer, Bare Bones, offers an exhaustive explanation. But you can often find the specific recipe you need through an Internet search engine: type in “regexp” and what you’re trying to do, and someone has usually already solved the problem. Standard regexp works within BBEdit’s Find feature.
Within the 30-day BBEdit full trial or with a paid copy of the app, you can also test your patterns using the software’s Pattern Playground—read Bare Bones’s documentation and this brief Apple write-up.
Ask Mac 911
We’ve compiled a list of the questions we get asked most frequently, along with answers and links to columns: read our super FAQ to see if your question is covered. If not, we’re always looking for new problems to solve! Email yours to mac911@macworld.com, including screen captures as appropriate and whether you want your full name used. Not every question will be answered, we don’t reply to email, and we cannot provide direct troubleshooting advice.