String or Text String Definition and Use in Excel

Using the Error Button to Convert Text Strings to Numbers in Excel
Using the Error Button to Convert Text Strings to Numbers in Excel. © Ted French

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.

Although text strings are most often comprised of words, they may also include such characters as:

  • letters;
  • numbers;
  • special characters such as periods (.), the dash symbol ( - ), or the number sign ( # ).

Identifying Text Strings at a Glance

By default, text strings are left aligned in a cell while number data is aligned to the right.

Formatting Data as Text

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

Converting Numbers and Formulas to Text with the Apostrophe

A text sting can also be created in both Excel and Google Spreadsheets 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.

Converting Text Strings to Number Data in Excel

At times, numbers copied or imported into a spreadsheet are changed into text data. This can cause problems if the data is being used as an argument for some to the programs' built-in functions such as SUM or AVERAGE.

Options for fixing this problem include:

Option 1: Paste Special in Excel

Using paste special to convert text data to numbers is relatively easy and as the advantage that the converted data remains in its original location - unlike the VALUE function which requires the converted data to reside in a different location from the original text data.

Option 2: Use the Error Button in Excel

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 - such as when number data formatted as text is used in a formula. To use the Error Button to convert the text data to numbers:

  1. Select the cell(s) containing the bad data
  2. Click the error button next to the cell to open the context menu of options
  3. Click on Convert to Number in the menu

The data in the selected cells should be converted to numbers.

Concatenating Text Strings in Excel and Google Spreadsheets

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

The formula that will do this is: =(A1 & " " & B1).

Note: the ampersand operator doesn't automatically put spaces between the concatenated text strings so they must be added to the formula manually. This is done by surrounding a space character (entered using the space bar on the keyboard) with quotation marks as shown in the formula above.

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

Splitting Text Data into Multiple Cells with Text to Columns

To do the opposite of concatenation - to split on cell of data into two or more separate cells - Excel has the Text to Columns feature. The steps to accomplish this task are:

  1. Select the column of cells containing the combined text data
  2. Click on the Data menu of the ribbon menu
  3. Click on Text to Columns to open the Convert Text to Columns wizard
  4. Under Original data type of the first step, click Delimited, and then click Next
  5. Under Step 2, choose the correct text separator or delimiter for your data - such as Tab or Space, and then click Next
  6. Under Step 3, choose an appropriate format under Column data format - such as General
  7. Under the Advanced button option, choose alternative settings for the Decimal separator and Thousands separator, if the defaults - the period and the comma respectively - are not correct
  1. Click Finish to close the wizard and return to the worksheet
  2. The text in the selected column should now be separated into two or more columns