Results 1 to 5 of 5

How to remove a label but keep the data

This is a discussion on How to remove a label but keep the data within the Excel Questions forums, part of the Question Forums category; I periodically copy and paste lines of data from a financial web page into Excel. The first field of each ...

  1. #1
    New Member
    Join Date
    Jan 2008
    Location
    Austin, Texas
    Posts
    7

    Default How to remove a label but keep the data

    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.

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,664

    Default Re: How to remove a label but keep the data

    Hi

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

    Using xl2013

  3. #3
    New Member
    Join Date
    Jan 2008
    Location
    Austin, Texas
    Posts
    7

    Default Re: How to remove a label but keep the data

    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?

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,664

    Default Re: How to remove a label but keep the data

    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).

    Richard Schollar

    Using xl2013

  5. #5
    New Member
    Join Date
    Jan 2008
    Location
    Austin, Texas
    Posts
    7

    Default Re: How to remove a label but keep the data

    Thank you.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com