How to Manipulate Text in Excel


Often, the work you do with Excel involves not only calculating numbers but also transforming and shaping data to fit your data models. Many of these activities include manipulating text strings.

Joining Text String

Joining text strings together is one of the most basic text manipulation actions. In this example, you create a full-name by joining together the first and last names.

Text Join

The example here uses the ampersand (&) operator , this operator tells Excel to concatenate values with one another. You can also use the built-in function CONCATENATE to join values without the need for the ampersand.

Setting Text to Sentence Case

Excel provides three useful functions to change the text to upper (UPPER), lower (LOWER) or proper (PROPER) case. But there is no function to help you to convert text to sentence case. You need a formula to force text into text case.

Text Sentence

The above figure illustrate how to use UPPER, LOWER, LEFT, RIGHT, and LEN Functions together to convert a text into a Sentence format.

The LEFT allows you to extract a given number of characters from the left of a given string. it requires two arguments: the text string you are evaluating,and the number of characters you want to extract from the left of the text string.

The RIGHT function is the same as the LEFT, requires two arguments, too. It extract the characters from the right of the text string.

The LEN function requires only one argument: a given text string. it returns the length of the text string.

We combine all these functions together to  get the results in sentence case.


Comments are closed.