Extract characters from the beginning or end of a string.
LEFT(text, [num_chars]), LEFTB(text, [num_bytes])
RIGHT(text, [num_chars]), RIGHTB(text, [num_bytes])
Extract a defined number of characters (or bytes) from either the left or right end of a text string or number using either defined values (e.g. "string") or cell references (e.g. C3).
LEFT and RIGHT syntax requires the defined value or cell reference from which to extract the characters and the number of characters to extract.
LEFT or RIGHT with a text string
In the example below, the LEFT and RIGHT functions are used to extract a defined number of characters from a defined string or a cell reference.
Use the LEFT or RIGHT function to extract a number of characters from a text string.
LEFT or RIGHT with a number
In the example below, the LEFT and RIGHT functions are used to extract a defined number of characters from both a number and a currency value. The LEFT function has also been concatenated with some text to convert a number into millions.
Use the LEFT or RIGHT function to extract a number of characters from a number.
Syntax
The LEFT, LEFTB, RIGHT and RIGHTB functions consists of 2 arguments:
- text. Required. The string from which to extract the characters.
- num_chars or num_bytes. Optional. The number of characters or bytes to extract from either the left or the right of the string.
Try it now!
Enter your formula into the grey cells.
Scope
LEFT(), LEFTB(), RIGHT() and RIGHTB() can be applied to cell references, text or numbers.
|
=LEFT(C3) or =LEFTB(C$4,2) or =RIGHT($C$5,3) or =RIGHTB(C6,4) |
|
=LEFT("South East Asia",5) || returns South. =RIGHT("South East Asia",4) || returns Asia. |
|
=TEXT(LEFT(1552899925233.71,6),"$#,##0")&" mn" || returns $155,289 mn. |
Caveats
- num_chars or num_bytes must be greater than or equal to zero.
- If num_chars or num_bytes is omitted, it is assumed to be 1.
- If num_chars or num_bytes is greater than the length of the string, all of the text is returned.
- Number formatting (e.g. currency symbols $) is not part of a number so is not extracted.
- LEFTB() and RIGHTB() extract the number of bytes from a text string.
- LEFTB() and RIGHTB() count 2 bytes per character for a DBCS language (Japanese, Chinese (Simplified), Chinese (Traditional) and Korean).
- For other languages, LEFTB() and RIGHTB() behave the same as the LEFT() and RIGHT() functions, counting 1 byte per character.
Related Functions
- The MID() function extracts characters from a string by defining the starting point and number of characters (or bytes).
Functions Category