Top 7 text functions in excel

Using excel first things which comes to mind is working with numbers but excel can do a lot with text. Excel has several text function to access these use short cut key ALT+MT (version 2010 onwards), other way of access it to go to formula ribbon ->text function.

Top 7 text functions in excel

UPPER

This function converts the given text string to all upper case letter.

Syntax

=UPPER(text)

Example

=UPPER(A1)

A1 is cell reference; this can be changed as per requirement.

LOWER

This function converts the given text string to all lower case letter.

Syntax

=LOWER(text)

Example

=LOWER(A1)

A1 is cell reference; this can be changed as per requirement.

PROPER

This function converts the given text string to all proper sentence case.

Syntax

=PROPER(text)

Example

=PROPER(A1)

A1 is cell reference; this can be changed as per requirement.

CONCATENATE

This function joins two or more text strings.

Syntax

=CONCATENATE(text,text1,text2….)

Example

=CONCATENATE(A1,A2,A3….)

=CONCAT(A1,A2,A3……)

=CONCATENATE( “Color of car is “, A2)

A1,A2,A3 is cell reference; this can be changed as per requirement. Also CONCAT is available with similar functionality. CONCATENATE may not be functional in future versions of excel.  

LEFT

LEFT function returns first character from left of string or specified number of character from left of string

Syntax

=LEFT(text, number of characters)

If number of character is not specified, only first character will be returned.

=LEFT(A1)

=LEFT(A1,2)

A1 is cell reference; this can be changed as per requirement.

LEN

LEN returns number of character in a string.

Syntax

=LEN(text)

Example

=LEN(A1)

A1 is cell reference; this can be changed as per requirement.

TRIM

TRIM function removes all space from a string leaving only single space between words

Syntax

=TRIM(text)

Example

=TRIM(A1)

A1 is cell reference; this can be changed as per requirement.