Another set of Text Functions in Microsoft Excel are useful to extract the partial text from the given text. These functions have two variations; one variation considers number of characters to return and the other variation (with B suffixed) considers the number of bytes to return from the given text.
The functions (with B suffixed) counts 2 bytes per character when the default language is DBCS (Double Byte Character Set). Otherwise, these functions counts 1 byte per character.
How to get leftmost text in Excel?
LEFT
and LEFTB
functions
LEFT
and LEFTB
functions returns the first N characters from the given text. As mentioned above LEFTB
function works differently if the default language setting sets as DBCS. Otherwise it behaves like LEFT
function. When DBCS is set, it considers 2 bytes per each character; otherwise, 1 byte per each character.
The Syntax of these functions are:
=LEFT(text, [num_chars])
=LEFTB(text, [num_bytes])
Where text is the given text. Second arguments in both these functions are optional. If you do not specify anything; these functions returns the first character from the given text. num_chars is the number of characters to return from left. And num_bytes is the number of bytes to return from left.
Formula | Result |
=LEFT(“Microsoft Excel”) | M |
=LEFT(“Microsoft Excel”, 9) | Microsoft |
How to get rightmost text in Excel?
RIGHT
and RIGHTB
functions
These functions are used to get the partial text from the end (from right) of the given text.
The Syntax of these functions are:
=RIGHT(text, [num_chars])
=RIGHTB(text, [num_bytes])
num_chars and num_bytes arguments are to specify the number of characters or bytes to return from the given text. Second argument in these functions are optional. If we do not specify anything, in the second arguments; these functions returns last character or byte (2-bytes in case DBCS is set as the default language) from the given text.
Below examples shows how these works:
Formula | Result |
=RIGHT(“Microsoft Excel”) | l |
=RIGHT(“Microsoft Excel”, 5) | Excel |
We will discuss more functions of Excel in my Upcoming Articles.
🙂 Sahida
One thought on “Microsoft Excel – Text Functions – LEFT, LEFTB and RIGHT, RIGHTB formulas”