Look Who it Was Random video

Excel - Converting Text to Numbers in Excel: Easy Solutions for Numbers and Dates - Episode 480

574 views · Published 1 May 2009 · 3:52 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Converting Text to Numbers in Excel: Easy Solutions for Numbers and Dates.

Welcome back to the MrExcel netcast! I'm Bill Jelen and I have some exciting news for those of you located in Salisbury, Maryland, Branson, Missouri, or Dallas, Texas. I will be hosting seminars in these areas on April 20th, April 24th, and May 2nd-4th respectively. These seminars are perfect for financial analysts and will cover a variety of topics. If you're interested, be sure to stop by MrExcel for more information.

Today's question comes from Gary, who emailed me about a common issue he faces in Excel. Every day, he receives data with both date and numeric fields, but they all come in as text. This can be frustrating, but luckily there are a few solutions. In Excel 2003 or newer, you can simply select all the numeric fields and click the "!" sign to convert them to numbers. However, this doesn't work for date fields.

In my book, "Learn Excel from MrExcel," I shared a trick where you can copy a "1" to the clipboard and use Paste Special to multiply it by all the text fields, converting them to numbers. But there's an even better way! Copy a blank cell (which contains the number 0) to the clipboard, select all the text fields, and use Paste Special to add the 0 to them. This will convert them to numbers, but the date field will now be shown as a serial number.

To convert the date field back to a date format, you can use the Text to Columns wizard. Simply go to Data, Text to Columns, and click Finish. This will automatically convert the dates to the correct format. Alternatively, you can use the third option in the wizard to specify the date format if it's not automatically recognized.

Thank you to Gary for sending in this question. If you have a question for the netcast, please feel free to email it to me or leave a voicemail. I'm always happy to answer your Excel questions and share helpful tips and tricks. Thanks for watching and be sure to tune in next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


After importing data, your numbers and dates might be stored as text. While newer versions of Excel allow you to solve the numbers stored as text easily, the exclamation point dropdown never appears for the dates stored as text. Episode 480 takes a look at several methods for solving the problem.

This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn an Excel tip!

Table of Contents:
(00:00) Introduction and upcoming seminars
(00:41) Question from Gary about converting text to numbers in Excel
(00:51) Gary's specific issue with converting dates
(01:05) New feature in Excel 2003 for converting numbers
(01:16) Alternative method for converting text to numbers
(02:00) Quickest way to convert dates using Text to Columns wizard
(02:56) Recap of three solutions for converting text to numbers
(03:26) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Branson, Missouri
Convert text to number
Dallas, Texas
Data analyst boot-camp
Delaware shore
Delmarva area
Excel seminar
Financial analyst
Paste Special
Salisbury, Maryland
Text to Columns


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152382/

More from this channel