by Dawn Bjork, MCT, MOSM | The Software Pro

Numbers and formulas aren’t the only important data in Microsoft Excel. Text entries are also a key part of worksheet data. Whether the text is a column heading or a critical data element, Excel includes many ways to improve the layout, appearance, and value of text. Find out how to format text cells, combine data from multiple cells, and other Excel text tips.

1. Formatting Text Cells

If you are having problems sorting or filtering data lists, one of the reasons may be that some data in a column is formatted as text and some as numbers. In an ascending sort order, numbers and punctuation will sort before text. For instance, entries of 22, 1111, 333 would sort as 22, 333, 1111 if formatted as a number, and 1111, 22, 333 if formatted as text (left-to-right). You will get inconsistent results when a combination of formats exists. The Text format is also needed for data that includes a fixed number of places or leading zeros such as 0001234.

One plus in Excel is you can format cells prior to entering data. This is true for text as well as numbers. To format entire columns for text entry, highlight the column(s), then right-click on the column and choose Format Cells. Next, pick the Number tab from the dialog box, and then select the Text format, finish with OK. The Text alignment choice is also found on the Home tab (Alignment group) of the Ribbon. Apply the Text format to existing cells as well for better sorting and filtering results.

2. When Formatting Doesn’t Work

To change a single entry to a text format, type an apostrophe (‘) in front of the data.

3. Combining Two or More Cells (Sounds Like Cat)

Existing data may not be formatted exactly the way you need. For example, if employee names are in 2 columns, Last Name and First Name, but you want to display a full name for reporting as in Last Name, First Name (Smith, Joe) there are several ways to do this, the CONCATENATE or CONCAT functions and the ampersand (&). In the following examples, Column B is Last Name and Column C is First Name. To create the Full Name in Column D, the formula would be similar to (either example works):

=CONCAT(B2,”, “,C2)<—– CONCAT is a function that “combines”; additional text like the comma is included in quotations; CONCAT replaces the CONCATENATE function in Excel 2016/2019 as well as Excel 365. The CONCATENATE function will continue to be supported with all versions of Excel.

=B2 & “, ” & C2<—– the & also combines the values in cells B2 and C2.

4. Another Use of the Ampersand (&) in Excel and Access

In the above example, the ampersand (&) is a character used to combine or add multiple entries. In Excel as well as Microsoft Access, the & is also a special code which means a single ampersand won’t work in most text entries (the result is _ instead). To use an ampersand in a header, footer, or label in Excel or Access, type 2 ampersands, such as TIPS && TRICKS.

5. Wrapping Text in Excel

For multiple line entries of labels, comments, notes, or descriptions in Microsoft Excel, select the cells (or an entire row or column), right-click on the selection and choose Format Cells, pick the Alignment Tab, and check the option for Wrap Text, finish with OK. In Excel, Wrap Text is also found on the Home tab of the Ribbon. The column width will determine how many lines are needed to display the text. Choose this over multiple rows for lengthy text entries for greater readability and for any lists of data. Once text is formatted to enable wrapping, you can further control the layout by pressing [Alt] + [Enter] to force a line break or new line within the same cell.

Try these tips and tricks to master your text entries in Microsoft Excel.

Discover more Excel shortcuts, tips and tricks at TheSoftwarePro.com/Excel.

IAAP works with the best trainers in the industry to ensure you have relevant, engaging, practical content at your fingertips. This blog is written by a speaker with a program in the IAAP Approved Programs database. Search by name or keyword to find their contact information and book them for your Branch or Region event. 

If you’d like to attend one of these events, you can find one in your area here.

Dawn Bjork is The Software Pro^®, a Microsoft Certified Trainer (MCT), and is a certified Microsoft Office Specialist (MOSM) Master and Microsoft Office expert. Dawn is passionate about sharing smart and easy ways to increase your software productivity through her work as a technology speaker, software trainer, computer consultant, and author of 9 books. Discover more software tips, techniques, and timesavers at TheSoftwarePro.com. Dawn also shares oodles of daily tips on Twitter and Facebook.