Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing Apostrophize at Beginning of Cell
I have a spreadsheet where columns D through N have SOME data with
apostrophies before the data (e.g., '777, 'DCH8, 'GlobalExpress). Some data does, and some data doesn't have the apostrophe and they are all mixed together. This becomes a pain when trying to compare cells (e.g., I want to compare replace DCH8 with 'DCH8). When I go to find replace, if I put find ' and replace with nothing, it does not find anything. How can I get rid of all of the ' in front of the data? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing Apostrophize at Beginning of Cell
I would highlight the apostrophe and the copy it the go to the find and
replace and paste the apostrophe that u just copied, it happened to me once that the apostophe that i was typing with my keybord wasn't the same as the one that was on the actual cell that why it wasnt finding anything at first. hope it helps "SeanF74" wrote: I have a spreadsheet where columns D through N have SOME data with apostrophies before the data (e.g., '777, 'DCH8, 'GlobalExpress). Some data does, and some data doesn't have the apostrophe and they are all mixed together. This becomes a pain when trying to compare cells (e.g., I want to compare replace DCH8 with 'DCH8). When I go to find replace, if I put find ' and replace with nothing, it does not find anything. How can I get rid of all of the ' in front of the data? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing Apostrophize at Beginning of Cell
"SeanF74" wrote:
This becomes a pain when trying to compare cells (e.g., I want to compare replace DCH8 with 'DCH8) If you have DCH8 in A1 and 'DCH8 in A2, =IF(A1=A2,TRUE) should return true, as should =IF(A2="DCH8",TRUE). Perhaps your mistake is including the apostrophe in the string when you make a comparison. For example, the following is wrong: =IF(A2="'DCH8",TRUE). The apostrophe is superfluous in that example; but it should not adversely affect your ability to write comparison formulas. On the other hand, in the example '777, the question is: do you want to treat that as a text string or as a number? If the latter, then it would indeed be better to remove the apostrophe. I've forgotten how to do that with Find/Replace, if it can be done. Alternative: use the Text To Column feature, which is under Data in Excel 2003. ----- original message ----- "SeanF74" wrote: I have a spreadsheet where columns D through N have SOME data with apostrophies before the data (e.g., '777, 'DCH8, 'GlobalExpress). Some data does, and some data doesn't have the apostrophe and they are all mixed together. This becomes a pain when trying to compare cells (e.g., I want to compare replace DCH8 with 'DCH8). When I go to find replace, if I put find ' and replace with nothing, it does not find anything. How can I get rid of all of the ' in front of the data? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing Apostrophize at Beginning of Cell
Hi,
Try the =clean(cell ref.) formula in a helper column. Jaleel "SeanF74" wrote: I have a spreadsheet where columns D through N have SOME data with apostrophies before the data (e.g., '777, 'DCH8, 'GlobalExpress). Some data does, and some data doesn't have the apostrophe and they are all mixed together. This becomes a pain when trying to compare cells (e.g., I want to compare replace DCH8 with 'DCH8). When I go to find replace, if I put find ' and replace with nothing, it does not find anything. How can I get rid of all of the ' in front of the data? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete a space in the beginning of a cell... | Excel Discussion (Misc queries) | |||
Strip out the ' at the beginning of cell contents | Excel Discussion (Misc queries) | |||
Finding average beginning at first cell in row 0? | Excel Discussion (Misc queries) | |||
Find the beginning Cell of a Range | Excel Discussion (Misc queries) | |||
Identification of "<" character at beginning of cell | Excel Worksheet Functions |