Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
21-Feb-2000, 06:33 AM #1
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?
Mulderator's Avatar
Member with 51,021 posts.
 
Join Date: Feb 1999
21-Feb-2000, 01:36 PM #2
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.
Anne Troy's Avatar
Computer Specs
Member with 11,731 posts.
 
Join Date: Feb 1999
Location: Allentown, PA
Experience: Intermediate
21-Feb-2000, 07:51 PM #3
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).]
Mulderator's Avatar
Member with 51,021 posts.
 
Join Date: Feb 1999
22-Feb-2000, 11:47 AM #4
That Dreamboat's always got a trick up his sleave!!!
Anne Troy's Avatar
Computer Specs
Member with 11,731 posts.
 
Join Date: Feb 1999
Location: Allentown, PA
Experience: Intermediate
22-Feb-2000, 03:06 PM #5
Dreamboat is a she, not a he.
Mulderator's Avatar
Member with 51,021 posts.
 
Join Date: Feb 1999
23-Feb-2000, 01:45 AM #6
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).]
jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
23-Feb-2000, 07:15 AM #7
<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>

jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
23-Feb-2000, 07:17 AM #8
<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>

jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
02-Mar-2000, 07:44 AM #9
<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>

jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
02-Mar-2000, 07:49 AM #10
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.
Anne Troy's Avatar
Computer Specs
Member with 11,731 posts.
 
Join Date: Feb 1999
Location: Allentown, PA
Experience: Intermediate
02-Mar-2000, 11:13 PM #11
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.
jennieunlisted's Avatar
jennieunlisted jennieunlisted is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Feb 2000
Location: Australia
06-Apr-2000, 06:45 AM #12
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>

mikeshorts's Avatar
mikeshorts mikeshorts is offline
Junior Member with 2 posts.
 
Join Date: Dec 2002
05-Feb-2007, 11:30 AM #13
Wow! I know a lot about Excel, but this is the easiest answer I've seen in a while.

divide by 1 = great!

Thanks!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑