Is it possible to automate a series of editing tasks in Numbers?

Question

Is it possible to automate a series of editing tasks in Numbers?

Every week I receive my local beer-shop's stocklist as a table in a Pages document which, in order to make useful, I transfer into Numbers - so there's a bit of repetitive copying and pasting of columns, extracting brewery names from the same column the beers are in, so I can put things into alphabetical order, sort, filter etc - then correct a whole load of spelling errors through 'find and replace' (just cos they irritate me)...

...is there a way to automate some or all of these tasks?

MacBook Pro 15″, macOS 12.4

Posted on Jun 3, 2022 3:45 AM

Reply

Answer 1

Jun 3, 2022 5:19 AM in response to SGIII

The list comes like this...

...the breweries and the beers appear in the same column, not in alphabetical order, and are unsortable and the list is really difficult to use, so I -

copy and paste these columns into a Numbers spreadsheet
extract the brewery name from the column in order to make a new column, purely for the breweries
take the 'ml' out of the size column so it reads as a number
format the price column as 'currency'
and correct spelling mistakes, which are mostly the same every week, particularly with the foreign beers

...so in the end my spreadsheet looks like this:

Reply

Answer 2

SGIII

Level 8

36,026 points

Jun 3, 2022 7:03 AM in response to Robbie Pennington

Well, that's a lot of dirty data wrangling! You ought to persuade them to give you a cleaner table.

Here are a couple of tricks that may cut down the clean-up time.

Your 2. Add a column before and use REGEX to pull the capitalized brewery names into the new column, like this:

=IF(COUNTMATCHES(B2,REGEX("^[A-Z][A-Z]"))>0,B2,A1)

(If your region uses , as a decimal separator use ; instead of , in formulas)

Then select the column, command-c to copy, and Edit > Paste Formula Results to remove the formula.

Your 3. Add a column and use REGEX to pull out the numerical part.

=REGEX.EXTRACT(C3,"\d*")*1

Those error warnings are actually useful. You can sort on that column, and delete the rows with the errors.

Your 4. Probably the easiest is just select the column and format as Currency. That's an easy step.

Your 5. Try to get your sources to give you cleaner data! Trying to automate the spelling check could get complicated.

SG

Reply

Answer 3

Jun 3, 2022 7:20 AM in response to SGIII

I gave him a cleaned up table, but for whatever reason he didn't use it!

That REGEX function is really interesting - I didn't know about that. I did a sort of clunky workaround based on the price sell always being empty in the brewery row...

...then would paste the formula results into my brewery column and pull down each brewery name in turn.

I do delete all the error rows to clean up the table...

...as for the spelling, I'll just have to try to ignore my obsessional side.

Thanks for some great ideas there!

Reply

Answer 4

SGIII

Level 8

36,026 points

Jun 3, 2022 5:01 AM in response to Robbie Pennington

Yes, there may be ways to automate but we would need to see specifics. It's hard to make working suggestions based just on generalities. Can you post screenshots of the Pages table and the result you are trying to achieve in Numbers?

shift--command-4, drag cross-hairs to select area on screen, release, start new post here and insert the screenshots from the Desktop using the 'mountains-and-moon' Image Insertion icon beneath the compose window.

SG

Reply

Is it possible to automate a series of editing tasks in Numbers?

Similar questions