Is there a Numbers equivalent to Excel TEXT function?

igmackenzie wrote:
Cell A1= 11/04/2010
Cell B1 formula is =TEXT(A1,"d mmm") would return '11 Apr'.
Is there any equivalent in Numbers?

If the value in A1 is interpreted by Numbers as a Date and Time value, you can display that value in B1 using the formula =A1 (ie, simply transferring the D&T value to B1) and formatting B1 as Date & Time > 5-Jan using the Cell Format Inspector. To display without the hyphen will require using a Custom format > Date and Time... Then building the format using only the Day of Month and Month elements, separated by a space.

If you need the actual text string "11 Apr", your formula gets a bit more complicated.

=DAY(A1)&" "&MONTHNAME(MONTH(A1))

will give you "11 April" (or "15 December" for a different date)

=DAY(A1)&" "&LEFT(MONTHNAME(MONTH(A1)),3)

will use only the first three letters of the monthname (eg. "11 Apr" or "15 Dec")

Regards,
Barry

In B1, insert :

=""&DAY(B)&" "&MONTHNAME(MONTH(B))

It would be a good idea to download and study *_iWork Formulas and Functions User Guide_*



Yvan KOENIG (VALLAURIS, France) samedi 8 janvier 2011 20:31:57

The custom format imports and exports from/to Excel, at least for my quick test with Excel 2011. Unless you need the date to be the actual text string of "11 Apr" for use in a concatenated string or a formula in Excel, simply applying a custom format will work. In Numbers, the formatted date can be concatenated to a string with no problem.

EDIT: I see the formula in question in your spreadsheet. Once I figure it out I'll see what can be done. So far I have parsed out the part inside the INDIRECT and the result is "11 Apr!AR7". Seems like a long formula just to get to that string, which is the heart of the formula, but maybe it was required.

Creating a custom format is easy and quick.

Cell Inspector
Choose "custom" for the format
For "Type", choose Date & Time
Drag the day so it is before the month
With the day selected, right-arrow so you are between the day and month.
Type a space
Delete the comma and the year
Click OK

Message was edited by: Badunit

Well, looks like you have more than one problem to figure out. One is the problem with dates and TEXT, another is that ROW in Excel can return an array but ROW in Numbers cannot, and a third is that cell references in Numbers (Sheet::Table::Cell) are different than those in Excel (Worksheet!Cell). So, you cannot create an array of sequential numbers (and therefore an array of dates/table names) using ROW in Numbers and the reference strings you are creating for the outermost INDIRECT in your formula will be invalid in one of the two applications. That formula simply will not work in Numbers and appears to be a nonstandard, but clever, use of ROW in Excel.

It appears what you want to do is add up cell AR7 of each worksheet whose name is within the range of dates given by columns H and I. I'm not sure how I would do this in Numbers, especially in a way that would also work in Excel. Maybe someone else can assist.

Thanks for jumping in **. I caught some of the same issues when I looked at the formula, but with tiny amounts of Excel experience, most of it not involving formulas operating across sheets, I thought it prudent to step back and wait.

Regards,
Barry

Looking at the tables and your formulas, each person is in a unique set of rows in the weekly tables. If this is always the case, wouldn't it be better to use Excel's 3-D references to do your sums rather than the complex formula?

=SUM('11 Apr:25 Apr'!AQ7) would sum up AQ7 for Bush
=SUM('11 Apr:25 Apr'!AQ16) would do AQ16 for Stevie, even though he didn't start on the 11th.

A 3-D reference refers to all tables from the first one to the last one you specify, including all those in between. What you can do is create a dummy blank "Bookend" table and insert your new weekly tables before it. That way the new weekly tables get included automatically by the formulas without having to change the formulas.

=SUM('11 Apr:Bookend':AQ7) would be the formula for Bush
=SUM('11 Apr:Bookend'!AQ16) would be the one for Stevie

This only works for Excel, though. Numbers does not have 3-D formulas

Numbers does not have an easy way to sum a cell from a range of sheets. There are numerous threads here on ways to do it. It often requires an extra table that contains a column listing all the names (sheet::table) of all the tables that will be summed and a row that lists of all the cells to be summed (AQ7, AQ16, etc). Each cell in this array uses INDIRECT to get the value of the specified cell of the specified table. The sum of a column is the result for the cell across all the tables. All the sums are done in that table and then you reference them. Search the forum for 'sum cells of range of sheets' and you'll get a bunch of hits. You may want to transpose this table (put the sheet::table names in row 1 and cell names in column A) due to the limit of 256 columns in Numbers.

The Numbers method requires INDIRECT and strings that represent the cell addresses. This means it will fail in Excel due to the difference in how cells are referenced. However, if you duplicate the table and make a version that uses the Excel way of referencing cells and then choose between the two results, it should work in both.

=IFERROR('cell from the Numbers version', 'cell from the Excel version')

Badunit wrote:

The Numbers method requires INDIRECT and strings that represent the cell addresses. This means it will fail in Excel due to the difference in how cells are referenced. However, if you duplicate the table and make a version that uses the Excel way of referencing cells and then choose between the two results, it should work in both.

=IFERROR('cell from the Numbers version', 'cell from the Excel version')

At first look, this scheme seems to be really interesting but, to be short, neither IFERROR nor ISERROR survive to the export to Excel process.

Formulas using these functions are dropped and replaced by their value.

Yvan KOENIG (VALLAURIS, France) mercredi 12 janvier 2011 14:33:03

My recommendation is to give up the idea.

I tried it and, as Yvan said, IFERROR gets replaced by the value. It is a major pain to get around the lack of that function. I thought I could get around some of it with a checkbox to select Numbers/Excel but checkboxes are not exportable. I got around that by using a cell where I type in an E or N. That is workable but not ideal. One problem solved, sort of.

Second problem is that, without IFERROR, I cannot "pre-fill" the names of tables that don't yet exist. That makes it more difficult to use but not impossible. You would have to type in the table names as you create them.

But then we get down to the problem that exporting to Excel changes the names of the sheets. Instead of 11 Apr we end up with 11 Apr - Table 1. Okay, I can change the table names in all the INDIRECT functions to have " - Table 1" appended to them. That exports and works fine in Excel the first time but the next time through Numbers it will be 11 Apr - Table 1 - Table 1. That's a huge problem but inconsequential compared with Numbers removing all the INDIRECT formulas upon import because "the INDIRECT function may produce a different result", thus destroying the spreadsheet.

I have no solution.