Insert a number a number in column

[webfort]

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

[Stefi]

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

[webfort]

Hi,

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

Thanks

Ashley

Also what does H2 refer to?

[[email protected]]

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.

[webfort]

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]

Sorry got it working now.

Thanks

So much

[[email protected]]

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

[webfort]

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

[Wendell A. Clark]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[webfort]

hi, thanks for the reply, it keeps saying you have entered to too few arguments for this function?

Thanks

[[email protected]]

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!

[Stefi]

Try this if you want to insert "0":
=TEXT("0"&H2,REPT(0,LEN(H2)+1))
and this, if you want to insert "1":
=TEXT("0"&H2,REPT(0,LEN(H2)+1))

Regards,
Stefi

„webfort” ezt �*rta:

>
> hi, thanks for the reply, it keeps saying you have entered to too few
> arguments for this function?
>
> Thanks
>
>
> --
> webfort
> ------------------------------------------------------------------------
> webfort's Profile: http://www.excelforum.com/member.php...fo&userid=7964
> View this thread: http://www.excelforum.com/showthread...hreadid=543232
>
>