Skip to main content

SEARCH function

We often receive descriptions from manufacturers that contain several pieces of information, which we display in our store in different attributes. This different information is often separated by signal words and must now be extracted into different columns for us.

If these signal words are "characters", we can easily use the Excel function Text in columns.

However, if it is a special word, we need a function that performs a separation. We will now show you how to do this.

1. Take the front part of a cell​

Our goal is to take the front part of a cell up to a unique search word. To do this, we use the following formula:

=IFERROR(LEFT([cell];(SEARCH("search word";[cell])-1));[cell])

The formula contains 3 functions:

  • SEARCH: returns the position (a number) where the search word starts. 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: provides the front part of a cell

  • IFERROR: if the search word is not present in every cell, the cell itself should be taken over again

Example: We want to have the left part of the description up to the word Scope of delivery.

Formula:

=IFERROR(LEFT([p_desc.de];(SEARCH("scope_of_delivery";[p_desc.de])-1));[p_desc.de])

2. Take the rear part of a cell​

If we want to take the right part of a cell starting from a unique search word, we need the following formula:

=IFERROR(RIGHT([cell];(LEN([cell])-SEARCH("scope_of_delivery";[cell])+1));"")

The formula contains 4 functions:

  • SEARCH: returns the position (a number) where the search word starts. We calculate plus 1, because we want to have the first letter of the search word in the separation of the cell content.

  • LEN: We calculate the total length of the text minus the number of characters that precede the search word to get the number of characters that should be taken from the right.

  • RIGHT: returns the rear part of a cell

  • IFERROR: If the search word is not present in each cell, nothing should be copied.

Example: We want to take the right part of the description from the word Scope of delivery.

Formula

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