Hi,

I was wondering if any one could help me, I have some data which was
inputted incorrectly and I need to amend it, there seems to be a
pattern, its missing the number 1 before the number eg 31456 should be
131456. There are over 1000 records and would take a long time to do
manually, could anyone help me?

Thanks

Ashley


--
webfort
------------------------------------------------------------------------
webfort's Profile: http://www.excelforum.com/member.php...fo&userid=7964
View this thread: http://www.excelforum.com/showthread...hreadid=543232

Say wrong numbers are in Column H, insert this formula in a helper column:

=VALUE(1&H2)
Drag it down as required!
Then copy the helper column and PasteSpecial/Values into column H.

Regards,
Stefi


€žwebfort€� ezt Ã*rta:


Hi,

I was wondering if any one could help me, I have some data which was
inputted incorrectly and I need to amend it, there seems to be a
pattern, its missing the number 1 before the number eg 31456 should be
131456. There are over 1000 records and would take a long time to do
manually, could anyone help me?

Thanks

Ashley


--
webfort
------------------------------------------------------------------------
webfort's Profile: http://www.excelforum.com/member.php...fo&userid=7964
View this thread: http://www.excelforum.com/showthread...hreadid=543232

IF the value is 100000 lower than it should be, then you can do it in
one step - put 100000 in one blank cell, copy it, then select the other
cells as a block - now, edit paste special, use the ADD option to add
this value.

hi thanks.

This is the set up and what I am doing:-
B416 BALDWIN C.A. 12617 this should be B416 BALDWIN C.A. 112617

I am tried the formula earlier and it just leaves a 1 in there and when
i do the paste special nothing happens.

Any help would be great

Thanks


--
webfort
------------------------------------------------------------------------
webfort's Profile: http://www.excelforum.com/member.php...fo&userid=7964
View this thread: http://www.excelforum.com/showthread...hreadid=543232

Hi,

Thanks for getting back to me, what do you mean by helper column?

Thanks

Ashley

Also what does H2 refer to?


--
webfort
------------------------------------------------------------------------
webfort's Profile: http://www.excelforum.com/member.php...fo&userid=7964
View this thread: http://www.excelforum.com/showthread...hreadid=543232

By helper column we simply mean a blank column that you can use as
tempory storage space - and H2 is the cell reference to the value in
column H row 2

Hi thanks, can this be done the same with a zero, I have tried but i
won't display it, I have tried changing the format as well.

Thanks

As

--
webfor
-----------------------------------------------------------------------
webfort's Profile: http://www.excelforum.com/member.php...nfo&userid=796
View this thread: http://www.excelforum.com/showthread.php?threadid=54323

Do the text version e.g.:
=text("0"&H2)

or alternately
=text("'0"&H2)
note the preceding apostrophe ' before the 0

--
Wendell A. Clark, BS
Nurses Unlimited, Inc.
432-550-1700 x126
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.


"webfort" wrote in
message ...

Hi thanks, can this be done the same with a zero, I have tried but it
won't display it, I have tried changing the format as well.

Thanks

Ash


--
webfort
------------------------------------------------------------------------
webfort's Profile:
http://www.excelforum.com/member.php...fo&userid=7964
View this thread: http://www.excelforum.com/showthread...hreadid=543232

Zeros are different, in that 0100 is still only 100 - but you CAN do it
with formatting the cells - in this example, format the cells as a
custom type 0000 - this will force Excel to display 4 digits -
obviously extend this formatting as necessary for the number of digits
you need to see!