---

MS Excel 2007: Create a formula to parse out BAG information and BOX information

Question:In Microsoft Excel 2007, I'm trying to parse a cell to split out the BAG information and the BOX information. I can have the following types of values in the cell:

500/BAG 3000/BOX
*
100/BAG

When I parse the cell using the space to divide the BAG from the BOX information, I get an #error on the second and third examples. What I am ultimately trying to do is create a column with the BAG quantities and one with the BOX quantities. This will be used for importing into our accounting system. Can you help?

Answer:Let's look at an example.

In column A, we have the combined BAG and BOX value that we want to parse.

In column B, we want only the BAG information. So in cell B2, we've entered the following formula that uses the IF, ISERROR, SEARCH and LEFT functions:

=IF(A2="*","",IF(ISERROR(SEARCH(" ",A2,1))=TRUE,A2,LEFT(A2,SEARCH(" ",A2,1)-1)))

This will return the BAG information, or a blank value if there is no BAG information available such as in row 3 in the spreadsheet.

In column C, we want only the BOX information. So in cell C2, we've entered the following formula that uses the IF, ISERROR, SEARCH and MID functions:

=IF(ISERROR(SEARCH(" ",A2,1))=TRUE,"",MID(A2,SEARCH(" ",A2,1)+1,20))

This will return the BOX information, or a blank value if there is no BOX information available such as in rows 3 and 4 in the spreadsheet.