A text string, also known as a string or simply as text, is a group of characters that are used as data in a spreadsheet program. Text strings are most often comprised of words, but may also include letters, numbers, special characters, the dash symbol, or the number sign. By default, text strings are left-aligned in a cell while number data is aligned to the right.

Note:

Format Data as Text

Text strings usually begin with a letter of the alphabet, but any data entry that is formatted as text is interpreted as a string.

The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel Online.

Convert Numbers and Formulas to Text with the Apostrophe

Text strings are also created in Excel and Google Sheets by entering an apostrophe ( ’ ) as the first character of data.

The apostrophe is not visible in the cell but forces the program to interpret whatever numbers or symbols are entered after the apostrophe as text.

For example, to enter a formula, such as =A1+B2, as a text string, type:

‘=A1+B2

The apostrophe, while not visible, prevents the spreadsheet program from interpreting the entry as a formula.

Convert Text Strings to Number Data in Excel

At times, numbers copied or imported into a spreadsheet are changed into text data. This causes problems if the data is being used as an argument for some of the program’s built-in functions, such as SUM or AVERAGE. Options for fixing this problem include using Paste Special or the Error button.

Convert Text to Numbers With Paste Special

Using paste special to convert text data to numbers is relatively easy. It also keeps the converted data in its original location. This is different than the VALUE function which requires the converted data to reside in a different location from the original text data.

Convert Text to Numbers Using the Error Button

As shown in the image above, the Error button, or Error Checking button, in Excel is a small yellow rectangle that appears next to cells that contain data errors. You’ll see it when number data formatted as text is used in a formula.

To use the Error button to convert the text data to numbers:

  • Select the cell(s) containing the bad data.
  • Select the Error button (yellow exclamation sign) next to the cell to open a context menu.
  • Select Convert to Number.

The data in the selected cells is converted to numbers.

Select the cell(s) containing the bad data.

Select the Error button (yellow exclamation sign) next to the cell to open a context menu.

Select Convert to Number.

Concatenate Text Strings in Excel and Google Spreadsheets

In Excel and Google Spreadsheets, the ampersand (&) character joins together or concatenates text strings located in separate cells in a new location. For example, if column A contains first names and column B contains last names of individuals, the two cells of data can be combined together in column C.

The formula that does this is:

=(A1&" “&B1)

Another option for joining text strings is to use the CONCATENATE function.

The ampersand operator doesn’t automatically put spaces between the concatenated text strings. To add spaces to a formula, surround a space character (entered using the space bar on the keyboard) with quotation marks.

Split Text Data into Multiple Cells With Text to Columns

To do the opposite of concatenation, to split one cell of data into two or more separate cells, use the Text to Columns feature.

To split data in a cell, follow these steps:

  • Select the column of cells containing the combined text data.
  • Select the Data tab.
  • Select Text to Columns to open the Convert Text to Columns wizard.
  • Select Delimited and select Next.
  • Choose the correct text separator or delimiter for your data, such as Tab or Space, and select Next.
  • Choose a column data format, such as General, and select Advanced.
  • Choose alternative settings for the Decimal separator and Thousands separator if the defaults, the period and the comma respectively, are not correct. After you’ve made your changes, select OK.
  • Select Finish to close the wizard and return to the worksheet.
  • The text in the selected column is separated into two or more columns.

Select the column of cells containing the combined text data.

Select the Data tab.

Select Text to Columns to open the Convert Text to Columns wizard.

Select Delimited and select Next.

Choose the correct text separator or delimiter for your data, such as Tab or Space, and select Next.

Choose a column data format, such as General, and select Advanced.

Choose alternative settings for the Decimal separator and Thousands separator if the defaults, the period and the comma respectively, are not correct. After you’ve made your changes, select OK.

Select Finish to close the wizard and return to the worksheet.

The text in the selected column is separated into two or more columns.

Get the Latest Tech News Delivered Every Day