Numbers vs. Excel

Numbers is not 100% compatible with Excel. I suggest you use Excel. Buy Office 2011 for OS X.

Patrick,

The CSV problem is caused because Numbers attempts to infer the "type" of the information in each cell. So if you look at the actual data often it is something that confuses Numbers as to the type. This can be avoided ( at the expense of time) by formatting the table as text, then pasting the contents of the CSV into the preformatted table.

You should also know that this forum is answered by fellow users who have no ability whatsoever to affect future features. Kappy's answer is, infact, and answer because neither he nor I nor anyone else in this forum is responsible for feature that do, or do not , work in Numbers.

My guess is you aren't going to want to open a CSV file, then replace all the comma delimeters with tabes, then copy, then paste into Numbers. If you are (that's what I do) great, otherwise you are stuck with having to use Excel (or an office clone like LibreOffice).

For large datasets I would recommend avoiding Numbers anyway. Try LibreOffice.... you may like it AND it's free.

Best regards

Wayne

I'm trying to help you out. You're just being rude.

Patrick,

If you would like to make a suggestion regarding Numbers features/bugs use the menu item in Numbers "Numbers > Provide Numbers Feedback"

Hi Patrick,

I just opened a csv from my bank and tried what you tried.

Can't set a single column to a Monetary value

Do the positive values have a + sign in front of them? That makes them Text. Find and replace to remove the +

Can't 'Freeze Panes'

True. That is a feature of Numbers. However, you can do:

Menu > Table > Freeze Header Rows or Freeze Header Columns

I guess your CSV has no Header Rows or Columns (it is a plain text file with commas to separate columns. Numbers *is* smart enough to translate commas as separators!). You will need to add a Header Row:

Menu > Table > Header Rows > 1

Correct date display changes half way down to a weird value after opening a .csv

Try opening with TextEdit to see if there is anything strange.

I guess banks produce statements that look nice in a web browser.

Regards,

Ian.

Message was edited by: Yellowbox. Changed 'on a computer screen' to 'in a web browser'

Hi Patrick,

1. Dates.

As far as I can see, the dates make sense. The cell format has changed for some reason.

6/17/13 I intrerpret as June 17 2013.

0006-12-13 I interpret as June 12 2013.

Select Column A by clicking once on the label "A"

Format that Column in Inspector > Cells > Date and Time > choose the Date format you want for that whole Column. Set the Time display to None:

2. Freeze Header Rows.

Numbers will not split panes, but it will freeze Header Rows (or Header Columns) but first you need a Header Row!

I suggest that you first insert a blank Row above your data. That will give you a spare Row to play with, and you can delete it if you find you didn't need it.

Your CSV in Numbers might look something like this:

Click on the grey label ("1") to the left of Row 1. Hover over the "1" until you see a downward arrow, then click on the arrow to see a menu:

To get this:

Now you can go to Menu > Table > Header Rows > 1

and type in your Column Labels:

OK, I was wrong. You don't need that spare Row (now Row 2). You can delete it.

Now Menu > Table > Freeze Header Rows

and you can slide the table under Row 1. Row 1 is frozen and stays put as you slide the table.

The Numbers User Guide explains this. The Guide is searchable. Download the Guide from the Help Menu in Numbers.

Regards,

Ian.

Message was edited by: Yellowbox. Oops, my fault in reading USA/Canada dates. I hope I now have them right.

Patrick, what is your localisation setting on your computer?

My computer handles those dates the same way yours has; it is interpreting the dates as DD/MM/YY, but those dates in the csv are in US order - MM/DD/YY.

If you are in a country that uses MM/DD/YY, I don't know why one app would suddenly have problems when others don't.

In the meantime, one possible solution is this:

Make a copy of the original csv.

Open the copy in Textedit.

Find-Replace all slashes to commas.

This has the effect of creating three columns for the date.

Open the file in Numbers.

Create an extra column at the front of the table, and use this formula: =DATE(2000+C2,A2,B2)

Copy the new date column, and choose Edit > Paste Values.

Delete the work columns.

The problem is that the data has been entered as month/day/year, whereas your system default is day/month/year.

As you say, it's a bother, and every time I download my Paypal history, I have this problem.

Just did some research ... seems that Excel has a useful "Text to Columns" feature in the Tools menu, that allows dates to be read the right way.

Numbers does not have an equivalent of this feature.

There are several threads on this:

• Re: How to "text to column" (feature of Excel) in Numbers
• Text to column function
• "TEXT TO COLUMN" formula for numbers
• Re: Converting "text to column" command
• Is there a Numbers equivalent to Excel's "Text to Columns"?
• "Text to Column" 's excel equivalent in Numbers

One workaround is to set up a couple of tables that already have the required formulae for converting the date.

That way, you just have to plug the data into the first table, then read it from the converted table.

The conversion for the dates would involve 4 columns (for clarity)

Year = 2000+RIGHT($A2,2)

Month = LEFT(A2, FIND("/",A2)-1 )

Day = MID(A2, FIND("/",A2)+1, FIND("/",$A2,FIND("/",$A2)+1)-FIND("/",A2)-1)

DMYY = DATE(Year,Month,Day)

Tables:

1. original

2. convert (formulae above)

3. proper values, where the date column reads from the converted table.

These could be set up in a template, so that you don't need to keep fussing each time.

Then just use Edit > Paste Values before proceeding with any other work.

And put in a request to Apple to include the "Text to Columns" feature in future releases 🙂