Please help me before I go nuts!

Ok, I've been formatting data in excel which came off a
report generated by a (Sorry for swearing....) DOS program.

basically, I have a heap of numbers (1600+) which are this
format '00045454 now, I need to get rid of the '000 prefix
to compare it to other data which has the same set of
numbers but without the prefix. I've successfully used
the replace all command up until now when it suddenly
won't work and tells me it can't find the ' in any cell.
It removed all the extra 000's but I'm not left with a
number format of '45454 and have to manually go through
and delete the '. Funny thing is, before the ' is
removed, the cell alignment is to the left, and the ' is
not visible. If I click on the cell the ' appears in the
formula bar (and in the cell when I double click) once I
remove the ' manually, the cell beomces right aligned!

I have to manually do over 1600 cells and it's reaking
havoc on my eyes, can someone help please??

You might try putting a 1 in an empty cell, then right-click on it and
choose Copy...........then highlight your problem column and do Right-click
Paste special . Multiply..........

Or, in the column next to your problem column (A), put =A1+0 and copy it
down........then Copy paste special Values the whole column to get rid
of the formulas.........


Vaya con Dios,
Chuck, CABGx3


"leah" wrote in message
...
Ok, I've been formatting data in excel which came off a
report generated by a (Sorry for swearing....) DOS program.

basically, I have a heap of numbers (1600+) which are this
format '00045454 now, I need to get rid of the '000 prefix
to compare it to other data which has the same set of
numbers but without the prefix. I've successfully used
the replace all command up until now when it suddenly
won't work and tells me it can't find the ' in any cell.
It removed all the extra 000's but I'm not left with a
number format of '45454 and have to manually go through
and delete the '. Funny thing is, before the ' is
removed, the cell alignment is to the left, and the ' is
not visible. If I click on the cell the ' appears in the
formula bar (and in the cell when I double click) once I
remove the ' manually, the cell beomces right aligned!

I have to manually do over 1600 cells and it's reaking
havoc on my eyes, can someone help please??

You are my hero!!...thank you

-----Original Message-----
You might try putting a 1 in an empty cell, then right-
click on it and
choose Copy...........then highlight your problem column
and do Right-click
Paste special . Multiply..........

Or, in the column next to your problem column (A), put
=A1+0 and copy it
down........then Copy paste special Values the whole
column to get rid
of the formulas.........


Vaya con Dios,
Chuck, CABGx3


"leah" wrote in
message
...
Ok, I've been formatting data in excel which came off a
report generated by a (Sorry for swearing....) DOS
program.

basically, I have a heap of numbers (1600+) which are
this
format '00045454 now, I need to get rid of the '000
prefix
to compare it to other data which has the same set of
numbers but without the prefix. I've successfully used
the replace all command up until now when it suddenly
won't work and tells me it can't find the ' in any cell.
It removed all the extra 000's but I'm not left with a
number format of '45454 and have to manually go through
and delete the '. Funny thing is, before the ' is
removed, the cell alignment is to the left, and the ' is
not visible. If I click on the cell the ' appears in
the
formula bar (and in the cell when I double click) once I
remove the ' manually, the cell beomces right aligned!

I have to manually do over 1600 cells and it's reaking
havoc on my eyes, can someone help please??


.

Blush, Blush, you're welcome..........

Vaya con Dios,
Chuck, CABGx3


"leah" wrote in message
...
You are my hero!!...thank you

-----Original Message-----
You might try putting a 1 in an empty cell, then right-
click on it and
choose Copy...........then highlight your problem column
and do Right-click
Paste special . Multiply..........

Or, in the column next to your problem column (A), put
=A1+0 and copy it
down........then Copy paste special Values the whole
column to get rid
of the formulas.........


Vaya con Dios,
Chuck, CABGx3


"leah" wrote in
message
...
Ok, I've been formatting data in excel which came off a
report generated by a (Sorry for swearing....) DOS
program.

basically, I have a heap of numbers (1600+) which are
this
format '00045454 now, I need to get rid of the '000
prefix
to compare it to other data which has the same set of
numbers but without the prefix. I've successfully used
the replace all command up until now when it suddenly
won't work and tells me it can't find the ' in any cell.
It removed all the extra 000's but I'm not left with a
number format of '45454 and have to manually go through
and delete the '. Funny thing is, before the ' is
removed, the cell alignment is to the left, and the ' is
not visible. If I click on the cell the ' appears in
the
formula bar (and in the cell when I double click) once I
remove the ' manually, the cell beomces right aligned!

I have to manually do over 1600 cells and it's reaking
havoc on my eyes, can someone help please??


.

Easiest way is to copy a blank general formatted cell, then paste special and
Add


"CLR" wrote in message
...
: You might try putting a 1 in an empty cell, then right-click on it and
: choose Copy...........then highlight your problem column and do Right-click
: Paste special . Multiply..........
:
: Or, in the column next to your problem column (A), put =A1+0 and copy it
: down........then Copy paste special Values the whole column to get rid
: of the formulas.........
:
:
: Vaya con Dios,
: Chuck, CABGx3
:
:
: "leah" wrote in message
: ...
: Ok, I've been formatting data in excel which came off a
: report generated by a (Sorry for swearing....) DOS program.
:
: basically, I have a heap of numbers (1600+) which are this
: format '00045454 now, I need to get rid of the '000 prefix
: to compare it to other data which has the same set of
: numbers but without the prefix. I've successfully used
: the replace all command up until now when it suddenly
: won't work and tells me it can't find the ' in any cell.
: It removed all the extra 000's but I'm not left with a
: number format of '45454 and have to manually go through
: and delete the '. Funny thing is, before the ' is
: removed, the cell alignment is to the left, and the ' is
: not visible. If I click on the cell the ' appears in the
: formula bar (and in the cell when I double click) once I
: remove the ' manually, the cell beomces right aligned!
:
: I have to manually do over 1600 cells and it's reaking
: havoc on my eyes, can someone help please??
:
:

Hi Leah,
FYI, the ' is used to force the cell data to be text. That's also why the alignment changed. In the general format text is aligned to the left and numbers are aligned to the right. It's a great technique for identifying numbers that aren't really numbers.

Regards,
Mark Graesser

Boston MA

----- leah wrote: -----

You are my hero!!...thank you

-----Original Message-----
You might try putting a 1 in an empty cell, then right-
click on it and
choose Copy...........then highlight your problem column
and do Right-click
Paste special . Multiply..........
Or, in the column next to your problem column (A), put
=A1+0 and copy it
down........then Copy paste special Values the whole
column to get rid
of the formulas.........
Vaya con Dios,
Chuck, CABGx3
"leah" wrote in
message
...
Ok, I've been formatting data in excel which came off a
report generated by a (Sorry for swearing....) DOS
program.
basically, I have a heap of numbers (1600+) which are
this
format '00045454 now, I need to get rid of the '000
prefix
to compare it to other data which has the same set of
numbers but without the prefix. I've successfully used
the replace all command up until now when it suddenly
won't work and tells me it can't find the ' in any cell.
It removed all the extra 000's but I'm not left with a
number format of '45454 and have to manually go through
and delete the '. Funny thing is, before the ' is
removed, the cell alignment is to the left, and the ' is
not visible. If I click on the cell the ' appears in
the
formula bar (and in the cell when I double click) once I
remove the ' manually, the cell beomces right aligned!
I have to manually do over 1600 cells and it's reaking
havoc on my eyes, can someone help please??
.