Die deutsch Fassung finden Sie hier.

Often we get from our manufacturer descriptions that contain more information, which we represent in our shop in different attributes. These different pieces of information are often separated by signal words and now need to be extracted into different columns for us.

If these signal words are "characters" we can easily use the Excel function text in columns. For more information see here.

However, if it is a special word, we need a function that performs a separation.

How this works you can see in the following text.

1. Extract the front part of a cell

Our goal is to take the front part of a cell up to a unique search term. For this we use the following formula:

=IFERROR(LEFT([cell],(SEARCH("search_term",[cell])-1)),[cell])

the formula contains three functions:

  • SEARCH:

Returns the location (a number) at which the search word begins. We calculate minus 1, because we do not want to have the first letter of the search word in the separation of the cell contents.

  • LEFT: 

Returns the front part of a cell

  • IFERROR: 

If the search term does not exist in every cell, the cell itself should be adopted again.

Example:

We want extract the left part of the description to the word delivery
Formula:

=IFERROR(LEFT([p_desc.de],(SEARCH("delivery",[p_desc.de])-1)),[p_desc.de])

2. Extract the back of a cell

If we want to take over the right part of a cell starting with a unique search term, we need the following formula:

=IFERROR(RIGHT([cell],(LEN([cell])-SEARCH("search_term",[cell])+1)),"")

the formula contains four functions:

  • SEARCH: 

Returns the location (a number) at which the search word begins. We calculate plus 1, because we want to have the first letter of the search word in the separation of the cell contents.

  • LEN: 

We calculate the total length of the text minus the number of characters before the search word to get the number of characters that should be taken over by the right.

  • RIGHT: 

Returns the back of a cell.

  • IFERROR:  

If the search term does not exist in every cell, nothing should be adopted.

Example:

We want to take over the right part of the description from the word delivery.

Formula

=IFERROR(RIGHT([p_desc.de],(LEN([p_desc.de])-SEARCH("delivery",[p_desc.de])+1)),"")

Did this answer your question?