Extracting text from multi line cell



I am collecting orders through a form and it produces a spreadsheet of data by order.  the products ordered by client go in to a single cell.

I would like to extract the qty for each product to a seperate cell so we can tally the total of each required for daily production.  I have tried, left /find /right etc. but can't get my head around it.  

THe text shown below in in a single cell.

THanks in advance, Stuart

Cream Cake (Amount: 24.00 EUR, Quantity: 9)
Cream Cake 2 (Amount: 24.00 EUR, Quantity: 7)
Cream Cake 3 (Amount: 24.00 EUR, Quantity: 3)

Total: 504.00



Selected Answer

I'm afraid I wouldn't be able to make a suggestion that doesn't involve VBA. Perhaps a UDF (User Defined Function) will suit your needs. That's a function composed in VBA but callable from the worksheet like a built-in Excel function.

For the interim, I offer worksheet functions to reach your goal in several steps. Enter this one in cell D2 of your sample workbook and copy it to the right until it shows no return.

  1. There is one reference to $C2. That's the cell where the original text resides.
  2. From this text blank lines are removed: SUBSTITUTE($C2,CHAR(10)&CHAR(10),CHAR(10))
  3. Then it's split into lines using XML.
  4. From these lines the nth one is returned.
    n is determined by =COLUMN()-3. Try this formula by itself so that you understand it. =COLUMN() returns the number of the column in which the function resides. In this case that is column D and therefore returns 4.
    4-3 = 1. COLUMN()-3 = 1. As you copy the formula to the right it counts up, 5-3, 6-3, 7-3 etc. The 1st, 2nd,3rd line from C2 is displayed.
  5. The entire formula is wrapped in an IFERROR() function. If the specified line doesn't exist an error results, and IFERROR displays "" if this happens. Hence, you can copy the formula to the right until it returns nothing, and you get one line of the original text in each cell.

In the second step you can extract information from the lines you have such created. The formula below extracts the item description simply by looking for an opening parenthesis.


Remember that I extracted the lines from the original text in C2 to D2:I2. Therefore D2 contains the first line of the original text. As you copy the formula to the right the description from the 2nd, 3rd and 4th lines are extracted. However, there is no opening parenthesis in the 4th line. Therefore an error results which is captured by the ISERROR wrapper and turned into a null string. Therefore only the first 3 functions will have a visible result, the remainder producing blank cells.

Here is the formula to extract the quantity. It works very similar to the above. It looks for whatever is between the first colon and "EUR", returning a null string if not found.


The last formula returns the quantity. It looks for "y:" (the last character of "Quantity" followed by a colon) and returns the trimmed string from that mark to the end but 1. Copy to the right as required.


In this formula I changed the wrapper. The ISERROR function returns the original string instead of a nullstring. You could expand on this capability and extract the Subtotal, Tax or Total amount from lines that don't have a quantity.


Answer the Question

You must create an account to use the forum. Create an Account or Login