[jennieunlisted]

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]

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]

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]

That Dreamboat's always got a trick up his sleave!!!

[Anne Troy]

Dreamboat is a she, not a he.

[Mulderator]

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]

<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]

<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]

<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]

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]

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]

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]

Wow! I know a lot about Excel, but this is the easiest answer I've seen in a while.

divide by 1 = great!

Thanks!