Pieces of text

By implementing business software we usually retrieve Master data from the previously used application, primarily the Partners and Items. Data is usually exported from old software to Excel, it is processed, and imported into a new application. When processing, there is often a need to “cut” the text in one of the columns from the left, from the right or from the given position, or to find a part of it in order to use it to generate some new content …

If we want to extract the first few characters from left of the text in one of the cells we use LEFT:

LEFT (<text>,<number of characters>)

For instance, if we enter a formula:

=LEFT(“Petar Petrović”,5)

return value would be “Petar”. At a similar way, if we want to extract certain number of characters from the right should use the RIGHT function:

RIGHT (<text>,<number of characters>)

For instance, if we enter a formula:

=RIGHT(“Petar Petrović”,8)

the result value would be “Petrović”.

Also, there is a function which could be used to extract certain number of characters from a text, starting from given position. It’s syntax is:

MID (<text>,<starting point>,<number of characters>)

If we enter a formula:

=MID(“Petar Petrović”,7,8)

result would be a string with content “Petrović”.

Function MID could be used in combination with LEN, which has a goal to calculate text length. For instance, if we want to extract word “Petar” from string “Petar Petrović”, as a last argument we would use difference between length of whole string, Last name and one more sign (space between First and Last name):

=MID(“Petar Petrović”;1;LEN(A2)-LEN(“Petrović”)-1)

There are also two functions that are used to locate a position from which a string starts within the given text. It’s about the functions FIND and SEARCH that have the same syntax, and they differ only in the fact that the first one, during search, makes a difference between upper and lower case, and the second does not. Their syntax:

FIND (<text we’re looking for>,<search destination>,<starting position>)

SEARCH (<text we’re looking for>,<search destination>,<starting position>)

If we enter  formula:

=FIND(“Petrović”,”Petar Petrović”,1)

as a result, we will get number 7, because it is the position from which the word “Petrović” begins. If we start searching from the first character in the text, as stated in the previous formula, this argument can also be omitted. By combining the LEFT, RIGHT, and MID functions with LEN, FIND or SEARCH, we can extract any part of the text, which is used in practice to, for example, divide name and surname into two separate columns.

Finally, we will mention two more useful functions. The EXACT function is used to compare two values. The default is to compare strings, although numbers will also pass. Syntax:

EXACT (<text 1>,<text 2>)

If the strings are identical, it returns the value TRUE, and if they are not, it returns FALSE.

To replace text with one value instead of another, use the SUBSTITUTE function. Syntax:

SUBSTITUTE(<text>,<old text>,<new text>,[<instance number>])

The first argument is the text, usually the address of the cell, to be searched. The second argument is the value we want to replace, and the third is the value with which we change it. The last, optional argument, is the instance number. If we omit it, the function will replace all values, and if we specify only the specified value. For example, if we specify the formula:

=SUBSTITUTE(“Peter is a good man”,”Peter”,”John”)

as a result we get the sentence “John is a good man”.