LibreOffice Calc: Retrieve Substring from String in Formula

Table of Contents

I introduce the way to retrieve substring from string in LibreOffice Calc formula. This way can be also applied for Microsoft Office Excel.

Environment

LibreOffice 5.1.4.2

Here, there are 3 methods to retrieve substring.

LEFT
RIGHT
MID

LEFT

Get characters from left side of the string.

Use it like LEFT("text", [the number of characters you want]). If you omit the number of characters to retrieve, the function returns left most one characters.

LEFT("hello")
# => "h"
LEFT("hello", 1)
# => "h"
LEFT(A1, 2)
# => two characters from left of the text at A1

LEFT("hello")

# => "h"

LEFT("hello", 1)

# => "h"

LEFT(A1, 2)

# => two characters from left of the text at A1

The number you entered is less than the original text, the function returns the original one.

RIGHT

Get characters from right side of the string.

Use it like LEFT("text", [the number of characters you want]). If you omit the number of characters to retrieve, the function returns right most one character.

RIGHT("hello")
# => "o"
RIGHT("hello", 2)
# => "lo"
RIGHT(A1, 1)
# => one right most character of the text in A1

RIGHT("hello")

# => "o"

RIGHT("hello", 2)

# => "lo"

RIGHT(A1, 1)

# => one right most character of the text in A1

The number you entered is less than the original text, the function returns the original one.

MID

MID returns the specified number of characters in a text string, starting from a specified position.

Use it like MID("text", [starting position], [the number of characters you want]). You can’t omit start position or the number of characters.

MID("hello", 1, 1)
# => "h"
MID("abcde", 2, 2)
# => "cd"
MID(A1, 3, 2)
# => 2 characters from the 3rd character of the text in A1

MID("hello", 1, 1)

# => "h"

MID("abcde", 2, 2)

# => "cd"

MID(A1, 3, 2)

# => 2 characters from the 3rd character of the text in A1

If the starting position is larger than the text length, the function returns blank text. If the number of characters you specified is larger than the number of characters you can retrieve, the function returns the text from the starting point to the end.