Automating a Numbers Spreadsheet

macOS
1

Howdy folks,

I am wondering if we have any Spreadsheet geeks in the group?

I ask because I have exported my Linkedin contacts into ‘Numbers’ via a delimited CSV.

I have the prerequisite columns having asked for ‘Tabs’ and ‘Comma’s’ as the column separators. However, the last column is the connection date and this has come into numbers in the following way: [9/9/13, 7:09 AM]

I would ideally like to separate these further still, to extract just the date. I don’t care about the time.

Do you know a way I can achieve this?

I’m wondering if it’s possible to break this single one cell… into two cells. Is there some kind of automation that I can do that with?

Thanks in advance for taking the time to read my request.

Marcus

2

What about processing the CSV before you import it to split that date time or simply remove the time and the square brackets?

You could do that with a line be line script of your choice, a tool supporting regular expressions, or if you have a text editor that supports multi caret editing, you could jump backwards from the end of the lines by words and segregate the pieces you want for editing.

Then you import nice clean data into your spreadsheet.

2 Likes
3

I think the quickest and easiest way would be to open the CSV in Numbers.

Then select the column with the date and time.
Then click the inspector and choose Format.
Change the format from Automatic to Date/Time.
Select the date format you want.
And for the time select None.

I’m pretty sure I’ve done this exact thing before and it worked like a charm and only took two minutes to do.

This solution might not be the best if you’re wanting to regularly automate an export of contacts out of LinkedIn and have the date formatted properly but if it’s just a one time or occasional thing then I think it’s the easiest solution.

If you want to make sure you can export the date values exactly as they show up in the column (and making sure the time isn’t also exported) you could take it a step further and select the date column (after changing the format as described above), then click Edit and select copy, then click the Edit menu in the top menu again and click paste values.

I’m posting this by memory on my phone otherwise I’d give you screenshots. If you have any trouble let me know and I can post a screenshot later.

2 Likes
4

I always recommend getting as far as you can elsewhere before creating a CSV - for import into Numbers or Excel.

5

Thanks for that. I’ll give your suggestion a try. For now this is a one time solution, but there may be situations in the future where I need to rethink it. I take on board your point about making sure the info it better formatted prior to import.

Taking out the time prior to import doesn’t appear to be possible.

1 Like
6

Thanks. I won’t pretend to understand all of that but I’ll bear it all in mind.

7

Sorry if that sounded confusing. I wasn’t trying to suggest taking out the time prior to importing. I was suggesting a way to format how the cell appears so you don’t actually see the time.

I just replicated what you are doing and noticed Numbers didn’t have the pasted functionality I was remembering. Maybe it was in Google Sheets. You can still accomplish this pretty quickly by formatting the Date/Time column in the Inspector like I explained before and then exporting your file back to a new CSV.

I made a quick 30 second video showing how to do this in Numbers. I deleted most of the unnecessary columns for my example so I wouldn’t share my LinkedIn contacts info :slight_smile: When you change the format of the date column as suggested and then export to a new CSV and then open the new CSV you’ll see that the only data in the Date column is now the date, the time will not be exported.

Here’s a link to that video for you: Video Example

1 Like
8

Here’s how I automate the “date cells not formatted the way I want them” situation, which is set up so I can use it to automate processing of future CSV files.

I build a separate sheet that is essentially simple direct references for all the original table cells into a new table, and the cells to which I want to do some reformatting, I apply the appropriate formula to that column or columns.

It’s usually a DATEVALUE using a LEN formula to extract dates that Numbers doesn’t recognize (the LEN formula trims off any unwanted characters).

I use this for CSVs downloaded form my online shops, mobile card swiper payment transactions, etc. If Numbers doesn’t recognize it as a date, it can mess up formulas that work on the data as dates (to extract months, years, etc).

In your case, you could use the DATEVALUE to just grab the date content from the original cell.

Then, in the future you just drop the CSV into the raw data table, and the reference table will automatically pull in all the data and reformat it for you.

Now as re-read your question, I am wondering if your question is more about the comma in the date messing up the data detection. And my other question for you is whether or not the brackets are something you added, or if they come in with the raw data.

If you need to remove the brackets, there will probably be some sort of use of multiple cleanup columns using the LEFT (and RIGHT) and LEN formulas with a -1 (to rid yourself of the start/end brackets). Assuming your malformed date is in cell B2, this form,a in C2 would get rid of the left bracket:

RIGHT(B2,LEN(B2)−1)

And then in D2, do similar using the LEFT formula, and change B2 to C2 in both spots.

Then in E2, run the DATEVALUE on D2

There’s probably a far more elegant way, perhaps using wildcards.