Problems with Find & Replace

Hi All,

My spreadsheet contains bank account numbers that contain "-" and spaces. To get rid of these characters I use Edit > Find > Replace.

When I do this the last digit of the 16 digit number changes to a zero rather than show the correct number. The first 15 digits of the account number are fine. Its just the last digit that seems to be affected.

Any ideas?

Regards
Garry

Removal of the '-' means that Excel now considers this to be a numeric, for which it holds only the first 15 digits.
Setting the cell to TEXT format before your edit should help.

--

Originally Posted by Gazzr

Hi All,

My spreadsheet contains bank account numbers that contain "-" and spaces. To get rid of these characters I use Edit > Find > Replace.

When I do this the last digit of the 16 digit number changes to a zero rather than show the correct number. The first 15 digits of the account number are fine. Its just the last digit that seems to be affected.

Any ideas?

Regards
Garry

Use a help column and this instead

=SUBSTITUTE(A2,"-","")


where A2 holds the account, then copy as long as needed, then paste special
as values

when you use edit replace you trigger a calculation and make the text string
into a number and excel has only 15 digits precision everything after that
will be truncated to a zero so if you need to enter account numbers that are
for instance 16 digits precede the entry with an apostrophe or preformat as
text.

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Gazzr" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> My spreadsheet contains bank account numbers that contain "-" and
> spaces. To get rid of these characters I use Edit > Find > Replace.
>
> When I do this the last digit of the 16 digit number changes to a zero
> rather than show the correct number. The first 15 digits of the account
> number are fine. Its just the last digit that seems to be affected.
>
> Any ideas?
>
> Regards
> Garry
>
>
> --
> Gazzr
> ------------------------------------------------------------------------
> Gazzr's Profile:
> http://www.excelforum.com/member.php...o&userid=31075
> View this thread: http://www.excelforum.com/showthread...hreadid=530795
>

Excellent,

Problem solved.

Thanks
Garry