MS Excel - Text Functions in Excel: LEFT, RIGHT, MID, LEN and TEXTJOIN

Text functions in Microsoft Excel are used to work with words, sentences, codes, names, identification numbers, and other text stored in cells. They are especially useful when information needs to be extracted, separated, counted, combined, or reformatted.

The functions LEFT, RIGHT, MID, LEN, and TEXTJOIN provide different ways of manipulating text data.

1. LEFT Function

The LEFT function extracts a specified number of characters from the beginning (left side) of a text string.

Syntax

=LEFT(text, [num_chars])

Where:

  • text is the cell or text from which characters should be extracted.

  • num_chars specifies how many characters should be returned.

  • If num_chars is omitted, Excel returns one character.

Example

Suppose cell A2 contains:

Bangalore

The formula:

=LEFT(A2,4)

returns:

Bang

Excel starts from the left side and extracts four characters.

Example with Employee ID

Suppose A2 contains:

EMP20260045

To extract the first three characters:

=LEFT(A2,3)

Result:

EMP

This can be useful when the beginning of an ID represents a department, organization, or category.

2. RIGHT Function

The RIGHT function extracts a specified number of characters from the end (right side) of a text string.

Syntax

=RIGHT(text, [num_chars])

For example, if A2 contains:

Bangalore

the formula:

=RIGHT(A2,4)

returns:

lore

The function starts counting characters from the right side.

Example with a Code

Suppose A2 contains:

INV20260985

To extract the final four characters:

=RIGHT(A2,4)

Result:

0985

RIGHT is useful when the important portion of a code, account reference, year, or identification number is located at the end of the text.

3. MID Function

The MID function extracts characters from the middle of a text string.

Unlike LEFT and RIGHT, MID allows you to specify where Excel should start extracting characters.

Syntax

=MID(text, start_num, num_chars)

The arguments are:

  • text: The original text.

  • start_num: The position from which Excel should begin extracting.

  • num_chars: The number of characters to extract.

Example

Suppose A2 contains:

INDIA2026

To extract 2026:

=MID(A2,6,4)

Result:

2026

The sixth character is the starting point, and four characters are extracted.

Another Example

Suppose A2 contains:

STU-4589-BLR

To extract the student number:

=MID(A2,5,4)

Result:

4589

MID is particularly useful when a structured code contains different pieces of information at known positions.

4. LEN Function

The LEN function counts the number of characters in a text string.

Syntax

=LEN(text)

For example:

=LEN("Excel")

returns:

5

If A2 contains:

Microsoft Excel

then:

=LEN(A2)

returns:

15

The space between the two words is also counted as a character.

Why LEN Is Useful

LEN is useful for checking the length of text, validating codes, and determining where specific portions of a text string are located.

For example, suppose all employee IDs should contain exactly eight characters.

You can use:

=IF(LEN(A2)=8,"Valid","Invalid")

If A2 contains eight characters, Excel returns Valid. Otherwise, it returns Invalid.

LEN with Other Text Functions

LEN becomes particularly powerful when combined with other functions.

For example, suppose A2 contains:

Karnataka

To extract everything except the last three characters:

=LEFT(A2,LEN(A2)-3)

Result:

Karnat

Here, LEN first determines the total number of characters, and LEFT then extracts the required portion.

5. TEXTJOIN Function

TEXTJOIN is used to combine text from multiple cells or text values into a single text string. It allows you to specify a delimiter, such as a space, comma, hyphen, or slash.

Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

The main arguments are:

  • delimiter: The character or text placed between the joined values.

  • ignore_empty: Determines whether empty cells should be ignored.

  • text1, text2, ...: The text values or cell references to combine.

Example

Suppose:

A2 = Ramesh
B2 = Kumar

The formula:

=TEXTJOIN(" ",TRUE,A2,B2)

returns:

Ramesh Kumar

The space in the first argument is used as the separator.

Joining Multiple Cells

Suppose:

A2 = Ramesh
B2 = Kumar
C2 = Bangalore

You can use:

=TEXTJOIN(", ",TRUE,A2:C2)

Result:

Ramesh, Kumar, Bangalore

This is useful for combining information stored across several columns.

Ignoring Empty Cells

One important advantage of TEXTJOIN is its ability to ignore empty cells.

Suppose:

A2 = Ramesh
B2 = Kumar
C2 = [empty]
D2 = Bangalore

Using:

=TEXTJOIN(", ",TRUE,A2:D2)

produces:

Ramesh, Kumar, Bangalore

The empty cell is skipped because the second argument is TRUE.

If empty values should not be ignored, the second argument can be set to FALSE.

Combining First Name and Last Name

Suppose a worksheet has:

First Name Last Name
Anil Kumar
Priya Sharma
Ravi Singh

A formula such as:

=TEXTJOIN(" ",TRUE,A2,B2)

can combine the first and last names into a complete name.

The resulting column would contain:

Anil Kumar
Priya Sharma
Ravi Singh

Combining Address Information

Suppose different parts of an address are stored separately:

A2 = 25 MG Road
B2 = Bengaluru
C2 = Karnataka
D2 = 560001

The formula:

=TEXTJOIN(", ",TRUE,A2:D2)

returns:

25 MG Road, Bengaluru, Karnataka, 560001

This can save considerable time when preparing reports or cleaning datasets.

Difference Between LEFT, RIGHT, MID and LEN

Function Purpose
LEFT Extracts characters from the beginning
RIGHT Extracts characters from the end
MID Extracts characters from a specified position
LEN Counts characters
TEXTJOIN Combines multiple text values

Practical Example

Consider the following product code:

LAP-2026-BLR-125

Suppose this information is stored in A2.

To extract the first three characters:

=LEFT(A2,3)

Result:

LAP

To extract the year:

=MID(A2,5,4)

Result:

2026

To extract the final three digits:

=RIGHT(A2,3)

Result:

125

To determine the total number of characters:

=LEN(A2)

Result:

15

If separate cells contain the extracted information, TEXTJOIN can be used to combine them again:

=TEXTJOIN("-",TRUE,B2,C2,D2)

This could produce:

LAP-2026-125

Combining Text Functions

Text functions can be combined to perform more complex operations.

For example, suppose A2 contains:

EMP-BANGALORE-2026

You can use LEFT, MID, RIGHT, and LEN according to the structure of the code to extract individual components.

A major advantage of combining these functions is that Excel can automatically process hundreds or thousands of similar records instead of requiring each value to be manually edited.

Common Uses of Text Functions

These functions are commonly used for:

  1. Extracting prefixes from identification numbers.

  2. Extracting suffixes from product codes.

  3. Separating portions of structured codes.

  4. Checking the length of IDs and reference numbers.

  5. Combining names from separate columns.

  6. Creating complete addresses from separate fields.

  7. Preparing data for reports.

  8. Cleaning and restructuring imported data.

  9. Creating standardized codes.

  10. Automating repetitive text-processing tasks.

Important Points to Remember

  • LEFT works from the beginning of the text.

  • RIGHT works from the end of the text.

  • MID allows extraction from a specific position.

  • LEN counts characters, including spaces.

  • TEXTJOIN combines multiple text values using a specified delimiter.

  • TEXTJOIN can ignore empty cells when its ignore_empty argument is set to TRUE.

  • These functions become more powerful when combined with other Excel functions.

  • The correct starting position and number of characters are especially important when using MID.

  • Text functions are useful for both data cleaning and automated data processing.

Note: Since your goal is to create topics that are genuinely not covered on eVidhya, I recommend treating this as a text-functions expansion topic, rather than claiming every individual function is entirely absent from the site. This avoids repeating concepts already present in the MS Excel chapter.