1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

(Solved) Excel: remove/add apostrophes at beginning of long list of numbers

Discussion in 'Business Applications' started by jennieunlisted, Feb 21, 2000.

Thread Status:
Not open for further replies.
Advertisement
  1. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    I'm using VLookup & need my reference numbers on the current and the lookup pages to have that left apostrophe at the beginning of both sets of reference numbers or otherwise to have no apostrophe there at all. Without consistency I can't perform the lookup.

    Is there a quick way to add or remove those apostrophes in a long list of numbers?
     
  2. Mulderator

    Mulderator

    Joined:
    Feb 20, 1999
    Messages:
    51,021
    Yes--just write a quick little macro that edits the cell,hit home to go to the beginning, delete the apostrophe, and then down. Give it a shortcut like Ctrl-D or something, then you can just keep clicking all the way down. You could write a more complicate one that loops until a condition is met, but by the time you do that, you'll be finished with the editing.
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    Put the number one (1) in any blank cell, on this sheet or any other. Copy the cell with the one in it. Select the cells that contain your data (but I don't suggest selecting entire rows or columns!). Hit Edit-Paste special and select multiply or divide. This forces Excel to see numbers (that are formatted as text) as numbers and the apostrophe is removed. Numbers that are already numbers are not affected because they don't change in value when multiplied or divided by one.

    This is a common occurrence when data is retrieved or exported from mainframe database applications, as well as if the cells were formatted as text prior to entry of the numbers.

    [This message has been edited by Dreamboat (edited 02-21-2000).]
     
  4. Mulderator

    Mulderator

    Joined:
    Feb 20, 1999
    Messages:
    51,021
    That Dreamboat's always got a trick up his sleave!!!
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    Dreamboat is a she, not a he. [​IMG]
     
  6. Mulderator

    Mulderator

    Joined:
    Feb 20, 1999
    Messages:
    51,021
    Hmmmmm!!! Now that makes you much more intersting, Dreamboat! Now I understand the moniker!!!

    [This message has been edited by Mulder (edited 02-23-2000).]
     
  7. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Dreamboat:
    Put the number one (1) in any blank cell, on this sheet or any other. Copy the cell with the one in it. Select the cells that contain your data (but I don't suggest selecting entire rows or columns!). Hit Edit-Paste special and select multiply or divide. This forces Excel to see numbers (that are formatted as text) as numbers and the apostrophe is removed. Numbers that are already numbers are not affected because they don't change in value when multiplied or divided by one.

    This is a common occurrence when data is retrieved or exported from mainframe database applications, as well as if the cells were formatted as text prior to entry of the numbers.

    [This message has been edited by Dreamboat (edited 02-21-2000).]
    <HR></BLOCKQUOTE>

     
  8. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Mulder:
    Yes--just write a quick little macro that edits the cell,hit home to go to the beginning, delete the apostrophe, and then down. Give it a shortcut like Ctrl-D or something, then you can just keep clicking all the way down. You could write a more complicate one that loops until a condition is met, but by the time you do that, you'll be finished with the editing.<HR></BLOCKQUOTE>

     
  9. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Mulder:
    Yes--just write a quick little macro that edits the cell,hit home to go to the beginning, delete the apostrophe, and then down. Give it a shortcut like Ctrl-D or something, then you can just keep clicking all the way down. You could write a more complicate one that loops until a condition is met, but by the time you do that, you'll be finished with the editing.<HR></BLOCKQUOTE>

     
  10. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    Thanks for your help, Mulder & Dreamboat. I successfully used Dreamboat's approach though truth is I'd rather add the apostrophe simply because some of our project numbers start with zeros which I would prefer to remain. The zeros get deleted when you multiply or divide the list by 1.
     
  11. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    If your project number always has, for instance, 7 digits:

    Select the cells, hit Format Cells and select the Number tab. On left column, select Custom, on right, type in "0000000" (seven zeroes, no quotes). Even if you lose the leading zeroes, you can reformat it that way and it will add zeroes to the front of anything less than 7 digits.
     
  12. jennieunlisted

    jennieunlisted Thread Starter

    Joined:
    Feb 5, 2000
    Messages:
    11
    Thanks Dreamboat. You've solved yet another of my problems! Sorry about not responding sooner. I'm new at this internet thing, & thought this query hadn't made it successfully into cyberspace.

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Dreamboat:
    If your project number always has, for instance, 7 digits:

    Select the cells, hit Format Cells and select the Number tab. On left column, select Custom, on right, type in "0000000" (seven zeroes, no quotes). Even if you lose the leading zeroes, you can reformat it that way and it will add zeroes to the front of anything less than 7 digits.
    <HR></BLOCKQUOTE>

     
  13. mikeshorts

    mikeshorts

    Joined:
    Dec 10, 2002
    Messages:
    2
    Wow! I know a lot about Excel, but this is the easiest answer I've seen in a while.

    divide by 1 = great!

    Thanks!
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/36214