A string, also known as a string or simply text, is a group of characters used as data in a spreadsheet program. Text strings are usually words, but can also contain letters, numbers, special characters, a dash, or hash. By default, text strings are left-aligned in a cell and numeric data are right-aligned.
Remark † The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel Online.
- Format data as text
- Convert numbers and formulas to text with an apostrophe
- Convert text strings to numeric data in Excel
- Convert text to numbers with Paste Special
- Convert text to numbers with an error button
- Merge text strings in Excel and Google Sheets
- Split text data into multiple cells with text in columns
Format data as text
Text strings usually start with an alphabetical letter, but data entries formatted as text are interpreted as a string.
Convert numbers and formulas to text with an apostrophe
Text strings are also created in Excel and Google Sheets by typing an apostrophe ( †) as the first character of the data.
The apostrophe is not visible in the cell, but forces the program to interpret any numbers or characters entered after the apostrophe as text.
For example, to enter a formula such as =A1 + B2 as a string, you would type:
'= А1 + В2
The apostrophe, although not visible, prevents the spreadsheet program from interpreting the input as a formula.
Convert text strings to numeric data in Excel
Sometimes numbers copied or imported into a spreadsheet are converted to text data. This causes problems if the data is used as an argument to some of the program’s built-in functions, such as SUM or AVERAGE. Options for resolving this issue 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 saves the converted data in its original location. This is different from the VALUE function, which requires the converted data to be in a different location from the original text data.
Convert text to numbers with an error button
As shown in the image above, the “Error” or “Error Check” button is in Excel, it’s a small yellow rectangle that appears next to cells with data errors. You see this when numeric data formatted as text is used in a formula.
To use the Error button to convert text data to numbers:
Select cells that contain invalid data.
Click the Error (yellow exclamation mark) button next to the cell to open the context menu.
Select Convert to Number †
The data in the selected cells is converted to numbers.
Merge text strings in Excel and Google Sheets
In Excel and Google Sheets, the ampersand (&) character joins or concatenates strings of text in individual cells in a new location. For example, if column A contains first names and column B contains last names of people, the two data cells can be merged into column C.
The formula that does this is:
= (A1 & "" & B1)
The ampersand operator does not automatically insert spaces between concatenated text strings. To add spaces to a formula, enclose a space (entered with the spacebar on your keyboard) in quotation marks.
Another option for concatenating text strings is to use the CONCATENATE function.
Split text data into multiple cells with text in columns
To unmerge, split a data cell into two or more separate cells and use the Text to Columns feature.
To split data in a cell, please do as follows:
Select the cell column containing the merged text data.
Select tab Facts †
Select Text to Columns to open the Text to Columns wizard.
Select with separators and select Further †
Choose the correct text separator or separator for your data, eg tab or Room and select Further †
For example, select the column data format Occur often and select Additionally.
Select alternative decimal and thousands separator settings if the default values, respectively, dot and comma are not correct. After making changes, select Okay †
Select Finished to close the wizard and return to the worksheet.
The text in the selected column is divided into two or more columns.