Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- Gazzr ------------------------------------------------------------------------ Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075 View this thread: http://www.excelforum.com/showthread...hreadid=530795 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with Find & Replace
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. -- Gazzr Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=530795 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with Find & Replace
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with Find & Replace
Excellent, Problem solved. Thanks Garry -- Gazzr ------------------------------------------------------------------------ Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075 View this thread: http://www.excelforum.com/showthread...hreadid=530795 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find and replace the " | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Worksheet Functions | |||
find and replace in workbooks | New Users to Excel | |||
find and replace path name in Excel cells containing hyperlink | Excel Discussion (Misc queries) | |||
Find and REPLACE within a selection, or column- not entire sheet/. | Excel Worksheet Functions |