Our royalty software’s sales import functionality requires a unique identifier for each product sold; ISBN, SKU or Vendor Code, in the sales import file. This ProductID must be in a separate field; column, for each record in the import file.
Some import files concatenate the title and ProductID into a single field.
This lesson covers how to extract a ProductID that is at the end of a text field.
Step 1. Add 4 columns to the right of the Title-ProductID column.
Step 2. In the first new column, add a formula to calculate the length of the Title-ProductID column. The function for this is =LEN(text)
Step 3. In the second new column, add a formula that locates the character that seperates the title and the ProductID. The function for this is =FIND(find_text,within_text,[start_num])
- find_text is required. The text string that you want to find, in quotes.
- within_text is required. The text (cell) containing the text you want to find.
- start_num is optional. Specifies the character at which to start the search. If you omit start_num, it is assumed to be 1.
Step 4. In the third new column enter the formula that will extract the product ID. This formula will use the MID function.
The MID function returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
MID(text, start_num, num_chars)
- text is required. The text string (cell) containing the characters that you want to extract.
- start_num is required. The position of the first character that you want to extract in the text (cell).
- num_chars is required. Specifies the number of characters that you want to extract.
The specific formula that you will use depends on how the title and ProductID are separated. Is the separator character at the start and end of the ProductID or is it only at the beginning?
The table below presents two example. In one case the ProductID is enclosed – “(T100”). The Mid calculations starts at the character after the separator; the “(“. This is the find result +1. It extends to the end of the character string minus 1 to exclude the ending separator; the “)”. This is the length result -1.
The formula is = MID(A2, Find result +1, Length -1) or MID(A2,FIND(A2,”(“,LEN(A2)). Many users prefer to see the Find and Length results in different columns. The formula column reference the calculated values in these result columns; =MID(A2,C2+1,B2-1)
In the second example the product ID is separated from the title by the pipe character – “|T100”. In this case the end of the text strength is the length (LEN) result. It is not reduced by 1 because there is no ending separator after the ProductID.
Step 5. The last step is to convert the calculated ProductID to a value only text. This is done using the Paste Special > Values only functionality in Excel.
To do this select the new column 3 with the calculated ProductID, right click the mouse and select copy. Then after moving your mouse to the top of column 3 or column four right click the mouse again and select Paste Special: Values. This will remove the formula from the cell leaving only the calculated value.
NOTE: QuickBooks Desktop has an option in preferences to export the product name and product number in one field; with the product number in parenthesis, or two separate fields.