how can i extract the numbers only from cells that has both numbers and text in a cell

Hi pvr,

Here are some solutions using Numbers '09. The formulas should be the same in Numbers 3.

Common to all three solutions:

D2: =C*1.03

This increases the amount represented in column C by 3%, and returns the appropriate number.

Cells in the first two groups in column D are formatted as Number with two places after the decimal.

Cells in the third group are formatted as Currency with two places after the decimal.

Solution 1: multiple SUBSTITUTE, rows 2, 3, 4

The SUBSTITUTE function can be used to replace an existing text string within a larger string with a different string. Here it is used to replace two strings, "$" and " Serving". Each of these is replaced with a null string ( "" ), effectively deleting them and leaving only the string representing a number.

B2: =SUBSTITUTE(SUBSTITUTE(B2,"$","")," Serving","")

The part in italics acts first, removing the $. What's left is acted on by the outer formula, removing the space and the word Serving.

The solution requires that the currency sign used is $, that there is no space between the currency sign and the 'number' part, and that the only text following the 'number' is " Serving" (including the leading space).

Note that the extracted 'number' is actually a text string (automatically aligned to the left edge of its cell). Fortunately, the formula in column D is able to interpret this text as a number value and perform the conversion to the new value. The result is a number, as evidenced by the right alignment in this column.

Solution 2: single SUBSTITUTE, rows 5, 6, 7

On being reminded that Numbers can read a text string representing a number, I realized that removing the currency sign might not be necessary. The revised formula in B5 removes only the " Serving" text:

B5: =SUBSTITUTE(B6," Serving","")

As can be seen in D5-D7, Numbers is able to do the math with text representing a money amount.

Solution 3: LEFT and FIND, rows 9, 10, 11

B9: =LEFT(B10,FIND(" ",B10)-1)

FIND returns a number indicating the position of the first occurrence of the searched for character, in this case a space. -1 reduces that number by one, telling LEFT to return as many characters as there are before, but not including, the space.

This solution is a little more flexible, as it requires only that there be a space following the 'number' in B, and that it be the first space to appear in the string in column B.

Regards,

Barry

I am not entirely sure that Numbers has this function but in Excel you choose the Text to Columns command and choose a space as the delimiter, you would then have 2 columns, one containing the number and the other containing the text. The 3% increase is simple to apply then. Optionally Excel could return them to their pre-separation state after the price increase is done. I would leave them as two columns though.

Maybe a Numbers maven will come by and clarify.

You're welcome.

Hi pvrcomm,

This is not hard to do in numbers if you don't want to move between the two programs.

FIND() locates the space and uses that count minus one for the string length in MID(). I could have used LEFT() and had a simpler formula but MID() would allow you to get rid of the "$" by changing the first "1" to "2". I kept the "$" because that lets Numbers know this is a currency You can get your 3% increase by multiplying by 1.03 either in this cell or in another one.

quinn

Hi Barry,

Oh, I do like SUBSTITUTE(). Simple and direct. When you know what you are eliminating it does the job.

quinn