Top 4 Ways to Manipulate Text using Functions

Jul 25, 2019 | Excel Hints and Tips

In Excel there are loads of functions and almost 30 of these are designed to specifically manipulate text.  Here we will look at the top 4 different types of text functions.

Text Functions to Clean your data

There are 2 functions we will look at here.  Trim removes any extra spaces at the beginning and end of the text but leaves any that may be between words.  This could occur due to a data input error or in the way that the data is exported from another package. Clean removes any non-printable characters from text.  These are normally encountered due to the export process from another application.

Image to show TRIM and CLEAN functions

Structure

=TRIM(Text)

=CLEAN(Text)

Text Functions to Change the Case of your Text

If you get left with data that is in the wrong case there is no icon to change the case like you find in PowerPoint or Word.  Instead this is done with functions. There are 3 related functions that change the case to Upper, Lower or Proper case respectively.

Image to show structure of UPPER, LOWE and PROPER functions

Structure

=UPPER(Text)

=LOWER(Text)

=PROPER(Text)

Text Functions that Split your Data

These are a little more complicated than the other sets of functions and there are other ways to get a similar result to what we get here.  Left, Mid and Right extract a certain number of characters from the left, middle or right of text.  They can be combined with other functions such as Find and Len to provide more flexible results when the number of characters is not always the same.

Image to show structure of LEFT, MID and RIGHT functions

Structure

=LEFT(Text, number of characters)

=RIGHT(Text, number of characters)

=MID(Text, start number, number of characters)

Text Functions that Combine Your Data

Conversely concatenate joins several text strings into 1 text string

Image of structure of concatenate function

Structure

=CONCATENATE(Text1, Text2, ………..)

NOTE: in Excel 2019 or O365 there is a new function called CONCAT that has the same structure and does a very similar thing.  Just be careful if you are sharing the file with people who have 2016 version or earlier because it will not work and you will get an error message

The “&” sign can be used to produce the same result

=Text1&Text2&Text3 etc

If you found this useful see what Excel courses are available or email lara@laramellortraining.co.uk to discuss any training requirements.