How to remove a label but keep the data

RLLets

New Member
Joined
Jan 21, 2008
Messages
7
I periodically copy and paste lines of data from a financial web page into Excel. The first field of each line is a date. Some times Excel recognizes these as dates and sometimes it doesn't. As I've learned over time how to control and format dates, I've been able to bring most of them into consistency with the others but not all. I've finally taken the time to look into the odd balls and I'm stumped by what I've found.

When I format the date column, the compliant fields, the ones that Excel stored as dates, fall into line. The odd balls, however, won't respond to any format changes. They just stay the same, e.g., 03/15/2007. I've tried General, Number, Date, Text, and Custom but they won't budge.

I've tried extracting their data with DATEVALUE and VALUE. When I run CELL with "format", it gives whatever format I've put on the cell. When I run CELL with "type", is says "l" which means it's a label.

While I've used names a lot, I wasn't familiar with labels so I did some reading. Labels, like names, provide an alternate way to reference a cell. Labels can be defined with Insert->Name->Label which goes to Label Ranges dialogue. Existing label ranges can be removed using that dialog. I tried defining row and column labels, using them as references, and removing them. Works great. But, a label has to show up in the Existing label ranges list on the Label Ranges dialogue in order to be removable. My odd balls don't show up.

If I just had a few and I weren't curious nor wanting to learn, I could probably delete the cell contents and type the dates in manually. However, I have a slew of them, I am curious, and I want to learn so I'd like to know what's going on here and how to handle it straightforwardly.

For the odd balls I have now does anybody know how to turn these labels into dates? For the future, is there some technique I should add to my copy and paste that would insure I get dates instead of labels?

Thank you.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi

Select these cells and go Data>TextToColumns>Next>Next> and select a Date Import Format of MDY and click Finish.
 
Upvote 0
Richard,

Worked perfectly. Thanks so much.

Do you know, is this a common situation, i.e., to have labels like this? Do you know what causes this to happen during a copy and paste from a web page to Excel?
 
Upvote 0
It may result from something as simple as a leading/trailing space (sometimes webpages contain characters that look innocuous but can cause problems such as a non-breaking space which in ascii terms is character 160). I don't think there's particularly any way to avoid it, we just need to be aware that it can happen (so as you have done you can take corrective action).

:)
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,470
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top