Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace changes cell format ??????
I have a column in spreadsheet containing codes - they are 4-digit
numbers some of them contain leading zeros. So column is formatted as Text to preserve those leading zeros. I want to mark some of those codes by adding extra prefix - eg. '0041' changes to 'xx0042'. But when I try to remove to remove that extra string later by Find/Replace Excel reformats the cell to remove leading zeros: xx0042 changes to 42. How can I prevent that? TIA for any pointers, JT |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace changes cell format ??????
Highlight the cells, CTRL-H, then:
Find What: xx Replace with: ' (i.e. apostrophe) Replace All. Hope this helps. Pete On Jan 23, 6:11*am, Woland99 wrote: I have a column in spreadsheet containing codes - they are 4-digit numbers some of them contain leading zeros. So column is formatted as Text to preserve those leading zeros. I want to mark some of those codes by adding extra prefix - eg. '0041' changes to 'xx0042'. But when I try to remove to remove that extra string later by Find/Replace Excel reformats the cell to remove leading zeros: xx0042 changes to 42. How can I prevent that? TIA for any pointers, JT |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace changes cell format ??????
I assume you are manually applying the prefix so you have 2 choices:-
1. When applying the pefix don't delete the ' sign. It will be visible in the cell though. When you do a replace xx with nothing and the leading zeroes will be retained. 2. If having the ' visible is an issue then delete it, apply your prefix and when you replace try replacing xx with ' Mike "Woland99" wrote: I have a column in spreadsheet containing codes - they are 4-digit numbers some of them contain leading zeros. So column is formatted as Text to preserve those leading zeros. I want to mark some of those codes by adding extra prefix - eg. '0041' changes to 'xx0042'. But when I try to remove to remove that extra string later by Find/Replace Excel reformats the cell to remove leading zeros: xx0042 changes to 42. How can I prevent that? TIA for any pointers, JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace # with new line in a cell. | Excel Discussion (Misc queries) | |||
find and replace for date format | Excel Discussion (Misc queries) | |||
Find comment replace cell value | Excel Discussion (Misc queries) | |||
Find & Replace text format | Excel Discussion (Misc queries) | |||
find replace format | Excel Discussion (Misc queries) |