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!
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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, just want to invite you depending on where you're located if you happen to be in any of these areas.
I'm going to have a couple of seminars coming up April 20th in Salisbury, Maryland that's the Delmarva area near the Delaware shore.
April 24th, Branson, Missouri and then we're going to have a 3 day seminar May 2nd through May 4th it's called the data analyst boot-camp great for any financial analyst, and that'll be in Dallas,Texas.
If you're interested in any of those feel free to stop by MrExcel.
Today we have a question sent in by Gary, he send it in by email.
If you have a question you can either email it to it or leave your question as a voicemail, be happy to get to you on a future podcast.
So here's Gary's question every day he gets data in Excel and it comes in he has some date fields and some numeric fields all of those fields come in as text.
Very frustrating for Gary figured out that if he chooses all the numeric fields and then hits this little ! sign this is new in Excel 2003.
You can open this up and say convert to number and that solves the problem for all the number fields, but it doesn't solve the problem for the date field.
There's no good way to convert those dates to text because the little ! doesn't show up.
Now, let me go to another worksheet here where we have all the problems in my book, Learn Excel form MrExcel.
I showed a trick where I put a one in a cell, copy the one to the clipboard and then used Edit, Paste Special, multiply to multiply the one by all the text fields and it worked.
Well, a better way is basically to use Paste Special, add with the number zero.
So, what I'm going to do is I'm going to go to any blank cell and use control+C to copy that blank cell to the clipboard that basically will copy the number 0 to the clipboard.
Now, I select all of my cells that are text and I'm gonna use paste special.
I'm gonna choose, I'm gonna paste Values and then Add, click OK.
And instantly all of my text fields both the numeric fields and the date fields are converted to real values.
Unfortunately, the date field is now shown as a serial number instead of a date.
I'll have to go in to Format Cells, control+1 is the fast way to do that and choose the Date field and choose a date format in order to convert those back.
Now, perhaps the quickest way to convert the Date field itself is to use the Text to Columns wizard.
If you go to Data, Text to Columns.
Actually at this point we could just click finish, but I'll show you what finish does.
If we click next and then next, the third option basically says where we have a date in month day year format Excel is smart enough to figure that out.
So, if we just click finish straight away, it would have converted all of our dates and kept the right formatting.
So there you have three different solutions for converting text to numbers.
If you just have numeric fields select the whole range and in Excel 2003 or newer hit the ! isgn to convert to numbers otherwise, copy a blank cell that copy is a zero to the clipboard use Edit, Paste Special, Add and it will add the 0 to all the text converting things over or the fastest way to convert a column of text dates to real dates use data Text to Columns, click Finish, and it will solve the problem.
Thanks to Gary for sending in this question.
If you have a question for the netcast, please feel free to get me the question.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Hey, just want to invite you depending on where you're located if you happen to be in any of these areas.
I'm going to have a couple of seminars coming up April 20th in Salisbury, Maryland that's the Delmarva area near the Delaware shore.
April 24th, Branson, Missouri and then we're going to have a 3 day seminar May 2nd through May 4th it's called the data analyst boot-camp great for any financial analyst, and that'll be in Dallas,Texas.
If you're interested in any of those feel free to stop by MrExcel.
Today we have a question sent in by Gary, he send it in by email.
If you have a question you can either email it to it or leave your question as a voicemail, be happy to get to you on a future podcast.
So here's Gary's question every day he gets data in Excel and it comes in he has some date fields and some numeric fields all of those fields come in as text.
Very frustrating for Gary figured out that if he chooses all the numeric fields and then hits this little ! sign this is new in Excel 2003.
You can open this up and say convert to number and that solves the problem for all the number fields, but it doesn't solve the problem for the date field.
There's no good way to convert those dates to text because the little ! doesn't show up.
Now, let me go to another worksheet here where we have all the problems in my book, Learn Excel form MrExcel.
I showed a trick where I put a one in a cell, copy the one to the clipboard and then used Edit, Paste Special, multiply to multiply the one by all the text fields and it worked.
Well, a better way is basically to use Paste Special, add with the number zero.
So, what I'm going to do is I'm going to go to any blank cell and use control+C to copy that blank cell to the clipboard that basically will copy the number 0 to the clipboard.
Now, I select all of my cells that are text and I'm gonna use paste special.
I'm gonna choose, I'm gonna paste Values and then Add, click OK.
And instantly all of my text fields both the numeric fields and the date fields are converted to real values.
Unfortunately, the date field is now shown as a serial number instead of a date.
I'll have to go in to Format Cells, control+1 is the fast way to do that and choose the Date field and choose a date format in order to convert those back.
Now, perhaps the quickest way to convert the Date field itself is to use the Text to Columns wizard.
If you go to Data, Text to Columns.
Actually at this point we could just click finish, but I'll show you what finish does.
If we click next and then next, the third option basically says where we have a date in month day year format Excel is smart enough to figure that out.
So, if we just click finish straight away, it would have converted all of our dates and kept the right formatting.
So there you have three different solutions for converting text to numbers.
If you just have numeric fields select the whole range and in Excel 2003 or newer hit the ! isgn to convert to numbers otherwise, copy a blank cell that copy is a zero to the clipboard use Edit, Paste Special, Add and it will add the 0 to all the text converting things over or the fastest way to convert a column of text dates to real dates use data Text to Columns, click Finish, and it will solve the problem.
Thanks to Gary for sending in this question.
If you have a question for the netcast, please feel free to get me the question.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
