you may utilize Apple ’s Numbers app for sophisticated spreadsheet work . But where it falls poor — as does Microsoft Excel — is at cleaning up datum that ’s in the spreadsheet . Where Microsoft Word lets you use traffic pattern to match and replace values , you ca n’t make that happen in Numbers , Excel , or Pages .
This can come up when you have escort , numbers , or currentness value arrange in dissimilar ways that are import or paste into Numbers . Numbers will chasten many kinds of standard initialize when typed in , but the same datum may remain in its original , wide-ranging soma when glue in or when you spread out a CSV ( comma - separated value ) Indian file , a common exportation format from other software and from websites .
You see this most frequently with names , where you have a tilt 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 up first , last , and middle initial into three field . What you require , however , isnormalizeddata — entries that are structured identically to allow them to be sorted , visually scanned , and organized in other style .
There ’s a unco dewy-eyed solvent that be nothing and might hold open a pot of manual re - introduction and editing . numbers racket let you copy the content of any bit of cells and then paste them into a text editor . As long as you retain tabs and arduous returns while editing , you could fix up what you paste in the text edition editor in chief , replicate the revise information , and paste it over the precise area you copied from in number .
I rely onBBEditto execute advanced wildcard matches called “ even look patching matching ” ( regexp , for short ) and to replace information with matched elements . Some apps support wildcards , where you drop in special characters or sequences to match parts of textbook , like any number ( 0 to 9 ) or a full judgment of conviction , defined by being between periods and other ending punctuation mark . Regexp is advanced , almost a round-eyed programming language . constituent you match in a search can be group so that you may rearrange them as part of a text replacement . BBEdit label the find part Grep after a Unix version of regexp . ( The free reading of BBEdit has all the features you require . Download the stock translation and install it , and you have access to all feature article during a 30 - solar day trial . When the test ends , the app“downgrades ” to a more modified characteristic setthat include all the find - and - replace options required in this clause . )
For example , say you have data point that lean name in the following way :
Jane Doe(first and last in one field)JaneandDoe(first and last in disjoined fields)Doe , Jane(last name first , a comma , and then the first name )
For format and spreadsheet purposes , you decide you want all name carve up into a first name and last name champaign , so you may sort by last name , but use the first name on an individual basis when exporting to an email listing program .
First , check that you have two conterminous fields in Numbers for first and last name . If you have a exclusive “ full name ” sphere , pick out a cell in that field and pressure Option - right hand pointer to add a column after that name editorial . Re - mark your name columns First Name and Last Name for clarity .
Next , replicate the information by dragging a survival , either around the contents of a exclusive field or both fields if you have a sundry set of information .
Now , glue into BBEdit . You ’ll see that each field is carve up on a line by a tab . ( If you want to view tabloid , space , returns , and other fictitious character , chooseView > Text Display > Show Invisibles . )
you’re able to compose your find / put back values to fix the data . ChooseSearch > Findand check the Grep loge to enable regexp . To get all theDoe , Janeby order them into two theatre in the right order , you would habituate this search :
^ ( . * ? ) , ( .*)$
That pattern means : match from the start of the origin ( ^ ) , find out all the schoolbook until the first comma butterfly ( ( . * ? ) ) and group that to practice in a substitution parentage ; then find a program line and space , and then make a second group of all the text ( ( . * ) ) to the destruction of the business line ( $ ) .
In the Replace field , you would enter :
2t1
Much bare ! That allege , take the 2d pigeonholing from the Find design ( 2 ) , espouse it with a tab ( t ) , and then with the first group ( 1).Doe , JanebecomesJane[tab]Doe .
you could click put back All if that pattern is isolated or the intact hardening of names . Or you may take a range of textual matter in BBEdit and check “ pick out text only ” in the Find dialog before clicking Replace All .
word of advice : Make a copy before moving back to Numbers : if you have more rows or column 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 care , select it in BBEdit , copy it , and then in Numbers , take the starting cell that you want to paste into . The pasted row will move downwards , and tabs will be converted to past into column cells to the right field .
In one late significance , I had a very small change to make that was still beyond bit capability : date and time were formatted as2019/10/28 , 22:51 , and Numbers does n’t pick out the comma butterfly as part of a datestamp . I could have round - tripped the information through BBEdit for a dim-witted find of the comma butterfly and replaced it with nothing . But it ’s a simple lawsuit to illustrate , as you could see in the figure below .
Consult BBEdit‘s manual of arms for more details on regexp . The developer , Bare Bones Software , extend an thoroughgoing account . But you could often regain the specific recipe you call for through an Internet search engine : type in “ regexp ” and what you ’re assay to do , and someone has usually already puzzle out the problem . stock regexp works within BBEdit ’s Find feature film .
Within the 30 - daytime BBEdit full trial or with a pay up transcript of the app , you’re able to also essay your patterns using the software package ’s Pattern Playground — readBare Bones ’s documentationandthis abbreviated Apple write - up .
Ask Mac 911
We ’ve compiled a lean of the questions we get ask most frequently , along with answers and link to columns : read our crack FAQ to see if your question is covered . If not , we ’re always looking for new problems to resolve ! e-mail yours tomac911@macworld.com , including screen door capture as appropriate and whether you need your full name used . Not every question will be answer , we do n’t respond to electronic mail , and we can not bring home the bacon direct troubleshooting advice .
