Using Text Functions in Excel

 Jan 25, 2016

Excel is mainly known for creating spreadsheets. It has many worksheet functions, like =SUM(), for performing mathematical calculations. In addition to those, there are a great number of functions for doing many other tasks, many of which are nothing to do with numerical data.

The 27 Text Functions in Excel 2013 and 2016 can perform a number of actions on text strings such as converting between text and other data types, splitting and joining text strings and finding parts of text strings.

Here is a sample of those functions and their use:

=UPPER(Text)

Converts a text string to uppercase

=LOWER(Text)

Converts a text string to lowercase

=PROPER(Text)

Converts a text string to proper case (ie first letter uppercase; the rest lowercase)

=LEN(Text)

Returns the number of characters in a text string

=TRIM(Text)

Removes spaces from a text string, but leaving a single space between words

=CLEAN(Text)

Removes all non-printable characters from a text string

Another very useful Text Function is =CONCATENATE(). To concatenate text simply means to join text strings together. For example:

If cells A1 and B1 contain the words New and Horizons, respectively: =CONCATENATE(A1,B1) would result in “NewHorizons”. To have a space between the words you would need to add a space to the formula enclosed within quotation marks: =CONCATENATE(A1,” “,B1) would result in “New Horizons”.

For more of Excel Text Functions (plus many other categories of functions) see the New Horizons Excel Level 4 course outline.

How do your Excel skills stack up?   

Test Now  

About the Author:

Ben Kirk  

With over 16 years of experience working as a Desktop Applications specialist for a number of large education services providers, Ben is one of New Horizons most skilled and dynamic instructors. With his Advanced Diploma of Business Skills alongside his practical experience and expertise, Ben is able to provide insight and guidance to students at all skill levels across the entire Microsoft Office suite.

Read full bio
top