IF Statement

I have a spreadsheet with various columns. One column is the “Category” field and another one is the “Description” field.
Where the category field says “Sales” the description field has content made up of a few elements and I want to separate the elements into other fields named Reference1, Reference2, Reference3 and Referance4.

The description field is something like this

Item - Ford Part Number - 1234 Customer Number – XXX111 Customer Name – JB

In the Reference1 field I need something like where IF the category = “Sales” I want the “Item” to appear in Reference1 else Reference1
In the Reference2 field I need something like where IF the category = “Sales” I want the “Part Number” to appear in Reference2 else Reference2
The same for Reference3 and Reference4.

I do not know how to split out the description into the separate parts.

Can anyone help please? Thanks

Please find attached sample workbook. It has a Before and After sheets. Thanks

Copy and Paste the formula

C2:

Please Login or Register  to view this content.

D2:

Please Login or Register  to view this content.

E2:

Please Login or Register  to view this content.

F2:

Please Login or Register  to view this content.

Hi Thanks for the this but does not work for me because not all the rows will the category of Sales as per the example workbook I attached. So I will need some sort of an IF statement to say IF category = "Sales",...................

Why can't you use the texts to columns function with the " - " as the delimiter?

Originally Posted by Sc0tt1e

Why can't you use the texts to columns function with the " - " as the delimiter?

Because the description is like this for only where the category is "Sales" and just the description for this category needs splitting.

If the formulas provided by dluhut return the values you expect, simply wrap the entire formula in an IF statement.

This would make the formula in C2 : =IF(A2="Sales",MID($B2,FIND("Item - ", $B2)+7, FIND("Part Number - ",$B2)-1-FIND("Item - ", $B2)-7),"")

Be warned that if anything gets changed in the Description column for the Sales data the formulas will break. (If "Part Number" somehow gets an extra space, or a double dash is added, for example). Also, depending on how data is getting entered, you may want to use a helper sheet to gather data for all "Sales" entries, and another to input data for "Purchases" entries. Then combine the two after the fact.