How to Convert Text to Number or Date in Excel?

What is Data Type Conversion?

Data type conversion is an essential operation for everyone who works with Excel spreadsheets. Understanding the many techniques available for converting text to numbers, dates, or other forms helps save time and prevent errors. In this article, we'll look at how to use Excel's transform to Number function, change formats, use Paste Special, and use formulae to transform data kinds. We'll also go through how to convert text to date values, how to repair dates with two-digit years, and how to style Excel columns to the correct data kinds. By the time you finish this article, you will have a firm grasp of data type conversion in Excel.

How to Convert Text to Number in Excel?

In Excel, text-to-number conversion is a frequent activity, especially when working with data imported from outside sources. Here are a few methods for doing it:

Excel's Convert to Number Feature

Excel has a built-in feature that can convert text to numbers quickly and easily. Simply select the cells containing the text you want to convert, then click the "Convert to Number" button in the Number group on the Home tab

Convert Text into Numbers by Changing the Format

You can also convert text to numbers by changing the cell format. Select the cells containing the text you want to convert, then click the Number Format dropdown in the Number group on the Home tab. Select the appropriate number format, and Excel will convert the text to numbers.

Change Text to Number with Paste Special

Another way to convert text to numbers is to use Excel's Paste Special feature. Copy the text you want to convert, then select the destination cells where you want the numbers to appear. Right-click and select "Paste Special", then choose "Values" and "Multiply". Excel will multiply the values in the selected cells by 1, effectively converting the text to numbers.

Change String to Number with Text to Columns

If you have text that's separated by a delimiter, such as a comma or a space, you can use Excel's Text to Columns feature to split the text into separate columns, and then convert the resulting columns to numbers. Select the cells containing the text, then click the "Text to Columns" button in the Data Tools group on the Data tab. Follow the prompts in the Text to Columns wizard to split the text into columns, then format the resulting columns as numbers.

Convert Text to Number Formula

You can also use a formula to convert text to numbers. The VALUE function is particularly useful for this task. Simply enter =VALUE(cell) in the destination cell where you want the number to appear, replacing "cell" with the cell containing the text you want to convert.

Turn Text into Number with Mathematic Operations

Finally, you can use mathematical operations to convert text to numbers. For example, if you have a cell containing the text "5", you can convert it to a number by entering =cell+0 in a destination cell. Excel will perform the addition operation, effectively converting the text to a number.

How to Convert Text to Date in Excel?

Excel records date as numbers, starting with the value 1 for January 1st, 1900. A certain date format is used to format dates, which affects how the date appears in the cell. You may utilize several techniques to change dates that are text-based in your Excel spreadsheet to the appropriate date format, including:

DATEVALUE Function

The DATEVALUE function converts a text string that represents a date to a serial number that Excel recognizes as a date. The syntax of the function is:

where "date_text" is the text string that represents the date. For example, if you have a date stored as text in cell A1, you can use the formula: =DATEVALUE(A1). This will return the serial number of the date, which you can then format as a date using the desired date format.

VALUE Function

The VALUE function converts a text string that represents a number or a date to a number that Excel recognizes as a date. The syntax of the function is:

where "text" is the text string that represents the date. For example, if you have a date stored as text in cell A1, you can use the formula: =VALUE(A1). This will return the serial number of the date, which you can then format as a date using the desired date format.

Mathematical Operations

You can use mathematical operations to convert a date stored as text to a serial number that Excel recognizes as a date. For example, if you have a date stored as text in cell A1 in the format "dd/mm/yyyy", you can use the formula:

This formula takes the right four characters (the year), the middle two characters (the month), and the left two characters (the day) of the text string, and concatenates them using the "/" separator. The "+0" at the end of the formula forces Excel to recognize the result as a number, which represents the serial number of the date.

Converting Text Strings with Custom Delimiters

If your dates are stored as text in a format that Excel does not recognize, you can use the Text to Columns feature to split the text into separate columns, and then use a formula to combine the columns into a serial number that represents the date. For example, if you have dates stored as text in column A in the format "dd|mm|yyyy", you can use the Text to Columns feature to split the text into three separate columns using the "|" delimiter. Then, you can use the formula:

where column A contains the day, column B contains the month, and column C contains the year. This formula creates a serial number that Excel recognizes as a date.

Text to Column Wizard

Excel's Text to Columns feature is useful when you need to split data within a cell into multiple cells, such as splitting a name into a first name and last name. However, it can also be used to convert text to date format.

To use Text to Columns for date conversion:

• Select the cells containing the text you want to convert.
• Click on the "Data" tab in the Excel ribbon, and then click "Text to Columns".
• In the Text to Columns wizard, select "Delimited" and click "Next".
• In the next step, choose the delimiter that separates the day, month, and year values in your text string. For example, if your text string is "05/12/2022", you would choose "slash" as the delimiter. Then, click "Next".
• In the next step, you can choose the data format for each column. Select "Date" for the column that contains the date information, and then choose the appropriate date format. Click "Finish" to convert the text to date format.

Note that if your text string does not have a consistent delimiter, you may need to use a combination of Text to Columns and other conversion methods to convert the text to date format.

Paste Special

The Paste Special feature in Excel is useful for performing various calculations and conversions. To use it for text-to-date conversion:

• Copy the text string containing the date information that you want to convert.
• Right-click the cell where you want to paste the converted value, and then click "Paste Special".
• In the Paste Special dialog box, select "Values" and "Add" or "Subtract" (depending on your specific conversion) from the available options.
• Click "OK" to convert the text to date format.

Note that Paste Special can be used for other types of conversions as well, such as converting text to number or converting number to text.

Fixing Dates with Two-digit Years

Sometimes, you may have dates with two-digit years that need to be converted to four-digit years. In such cases, you can use the following formula:

In this formula, "A1" refers to the cell containing the date information. The formula checks whether the year is greater than the current year and subtracts 100 years if it is, thereby converting the two-digit year to a four-digit year.

How to Format Excel Columns to Appropriate Data Types?

It's crucial to format the Excel column properly after converting your data to the necessary data type. Data is easier to comprehend and deal with when it has a better visual appearance thanks to formatting. We'll look at how to format columns in Excel for various data types in this section.

1. Formatting Numeric Data

Excel provides several built-in number formats that you can use to format your numeric data. Here are some commonly used number formats:

• General:
  Displays numbers as they are entered.
• Currency:
  Displays numbers with a currency symbol and two decimal places.
• Accounting:
  Similar to the currency format, but aligns the currency symbol and decimal places in a column.
• Percentage:
  Multiplies the value by 100 and displays the result with a % symbol.
• Scientific:
  Displays numbers in scientific notation with two decimal places.
• Fixed:
  Displays numbers with a fixed number of decimal places.

To apply number formatting in Excel, select the cells you want to format and then click on the "Number Format" drop-down in the "Home" tab. From there, you can select the desired number format.

In addition to formatting, you can also perform calculations on date and time data. Excel stores date and time values as serial numbers, which makes it easy to perform calculations such as adding or subtracting days, weeks, or months from a date.

2. Formatting Date and Time Data

Excel provides several built-in date and time formats that you can use to format your date and time data. Here are some commonly used date and time formats:

• Short Date:
  Displays dates using the system's short date format.
• Long Date:
  Displays dates using the system's long date format.
• Time:
  Displays time using the system's default time format.
• Custom:
  Allows you to create a custom date and time format.

To apply date and time formatting in Excel, select the cells you want to format and then click on the "Number Format" drop-down in the "Home" tab. From there, you can select the desired date or time format.

3. Using Conditional Formatting

Conditional formatting allows users to apply formatting to cells based on specific conditions. This feature can be useful when users need to highlight specific data points or identify trends in the data.

• To apply conditional formatting, select the cells you want to format and click on the "Conditional Formatting" dropdown in the "Home" tab. Then, select the type of condition you want to apply, such as "Highlight Cells Rules" or "Top/Bottom Rules".
• Users can then specify the conditions they want to apply to the cells. For example, to highlight all cells that are greater than a specific value, users can select "Greater Than" and enter the value they want to use as the threshold.
• Excel also allows users to create custom conditional formatting rules using formulas. This can be useful when users need to apply more complex conditions to their data. For example, to highlight all cells in a range that contain a specific word, users can create a custom rule using the formula:

• we can also use conditional formatting to format cells based on certain date and time criteria. For example, you can highlight cells with a date that falls within a certain range or cells that contain a specific day of the week.

Also to format cells based on specific criteria. For example, you can highlight cells with a value greater than a certain number or cells that contain a specific text string.

4. Using Custom Formats

Excel allows users to create custom formats to apply to cells. Custom formats can be useful when there is no pre-existing format that suits the data type, or when users need to apply a specific formatting style.

To create a custom format, select the cells you want to format and click on the "Number Format" dropdown in the "Home" tab. Then, select "Custom" from the list of options.

In the "Type" field, users can create their custom format by using the following placeholders:

• 0: displays a digit or a zero
• -: displays a digit or nothing
• ?: displays a character or a space
• .: displays a decimal point
• ,: displays a thousand separator
• %: displays a percentage
• $: displays a currency symbol

5. Other Formatting Options

In addition to the formatting options discussed above, Excel offers several other tools that users can use to format their data. These include:

• Cell Styles:
  predefined sets of formatting options that can be applied to cells with a single click
• Font Styles:
  options to change the font, size, and color of text in cells
• Borders:
  options to add borders to cells
• Merge and Center:
  an option to merge cells and center the text horizontally and vertically

Users can access these formatting options by selecting the cells they want to format and clicking on the appropriate option in the "Home" tab.

By using these formatting options effectively, users can ensure that their data is clear, easy to read, and visually appealing