Hi PPP,
Unless you are in a region where three and five tenths would be written 3,5 rather than 3.5, I can't see a syntax error in the formulas. If your region's decimal separator is a comma, then changing the commas in the formulas to semi-colons ( ; ) should correct the syntax error.
If copy your formula from here and paste it into B2 of a table where A2 contains a 'name' (text string containing one or more letters, a space, and one or more letters, I do get an error triangle, but the message is ' "start" is not a valid reference. '
That will happen if you have entered the text "start-pos" from the keyboard, and it appears as plain text in the formula. If you inserted the formula, "start-pos" should be enclosed in a lozenge shape, similar to the illustration below, and the formula will work as intended.
From the notes on FIND in the Function Browser:
start-pos:
"An optional argument that specifies the position within the specified string at which the action should begin. start-pos is a number value that must be greater than or equal to 1 and less than or equal to the number of characters in source-string."
You can omit this optional value by deleting the lozenge AND the comma immediately before it. Numbers will assume a value of 1, and start searching at the first character in the 'name'.
Here's a sample table with your two formulas (with ",start-pos" deleted from each) entered in cells B2 and C2:
B2: =LEFT(A,FIND(" ",A)-1)
C2: =RIGHT(A,LEN(A)-FIND(" ",A))
A formula places its result in the cell containing the formula.
To 'apply the formula to an entire column,' you will need to place a copy of the formula in each cell where you expect to see a result.
Start with the formula entered into the top cell that is to contain it. Select that cell by clicking on it (once).
Now grab the Fill handle (small circle) and drag down.
In Numbers v2, the fill handle is at the bottom right corner of the selected cell(s). Here, two cells, B2 and C2 are selected. The fill handle is shown in the green circle. In Numbers 3, the fill (down) handle has been moved to the center of the bottom of the selection rectangle bordering the selected cells.
Place the mouse on the fill handle. When the pointer changes to a black + sign, press the mouse button and drag down to fill the formula(s) into the rows below.
As the formula is filled into each row, the result will appear in that row.
As the formula is written to separate the 'name' at the space between the first and last name, it will work correctly only if the name consists of a group of letters (first name) then a single space, then a second group of letters (last name), as in rows 2, 3 and 4 of the table.
If the name consists of three 'words,' as in rows 5 and 6, FIND will find the first space, and the formulas will split the name there.
If the cell in column A is empty (or if it does not contain a space), FIND will throw an error saying it could not find " " and the cell will display an error triangle, as in rows 7 through 11.
For tidiness, those error triangles can be eliminated by enclosing the formula in an IFERROR statement:
B2: =IFERROR(LEFT(A,FIND(" ",A)-1),"")
I would suggest doing this only after you have the basic formula working, and have tested filling it into a few rows. Then edit the top row version and repeat the fill, this time continuing to the bottom row of the table.
Regards,
Barry
