Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apostrop
I used some code I found in the newsgroup to remove the leading, hidden
apostrophe that identifies cell contents as a string data type -- but it's too slow. Is there a faster method than the "FOR EACH ... {remove prefix character} .... NEXT" I'm using? A common scenario for me is 10 worksheets, each with 200,000 cells of data each. In addition, there is one column on each tab from which I do NOT want to remove the prefix character. I'm not sure search & replace will work for me because I have legit apostrophes in my data. Thanks in advance. David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apostrop
What does [Quickly!] mean?
-- Don Guillett Microsoft MVP Excel SalesAid Software "David Mueller" wrote in message ... I used some code I found in the newsgroup to remove the leading, hidden apostrophe that identifies cell contents as a string data type -- but it's too slow. Is there a faster method than the "FOR EACH ... {remove prefix character} ... NEXT" I'm using? A common scenario for me is 10 worksheets, each with 200,000 cells of data each. In addition, there is one column on each tab from which I do NOT want to remove the prefix character. I'm not sure search & replace will work for me because I have legit apostrophes in my data. Thanks in advance. David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apostrop
With rngToFix
.Formula = .Formula End With will work with formulas and/or constants. If a lot of formulas, turn calc to manual first. With rngToFix .Value= .Value End With for constants It can take an unterruptable long time if you are doing that against a huge sheet, so you might want to loop through smaller range areas and run it in sections.. -- Tim Zych SF, CA "David Mueller" wrote in message ... I used some code I found in the newsgroup to remove the leading, hidden apostrophe that identifies cell contents as a string data type -- but it's too slow. Is there a faster method than the "FOR EACH ... {remove prefix character} ... NEXT" I'm using? A common scenario for me is 10 worksheets, each with 200,000 cells of data each. In addition, there is one column on each tab from which I do NOT want to remove the prefix character. I'm not sure search & replace will work for me because I have legit apostrophes in my data. Thanks in advance. David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apostrop
If you have paste space you can remove lead apostrophes nearly
instantaneously. For example, enter: '=1+2 in ALL the cells in column A select the column and copy Then select the top cell in an un-used column (say column B) and: Edit PasteSpecial values You will see that column B replicates the contents of column A except all the leading apostrophes are gone. If desired, you can copy back onto column A. This technique is effective if you have large blocks of apostrophic cells that can be copy / pasted in this fashion. -- Gary''s Student - gsnu200751 "David Mueller" wrote: I used some code I found in the newsgroup to remove the leading, hidden apostrophe that identifies cell contents as a string data type -- but it's too slow. Is there a faster method than the "FOR EACH ... {remove prefix character} ... NEXT" I'm using? A common scenario for me is 10 worksheets, each with 200,000 cells of data each. In addition, there is one column on each tab from which I do NOT want to remove the prefix character. I'm not sure search & replace will work for me because I have legit apostrophes in my data. Thanks in advance. David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apos
Wow! That's quick! Thanks.
"Tim Zych" wrote: With rngToFix .Formula = .Formula End With will work with formulas and/or constants. If a lot of formulas, turn calc to manual first. With rngToFix .Value= .Value End With for constants It can take an unterruptable long time if you are doing that against a huge sheet, so you might want to loop through smaller range areas and run it in sections.. -- Tim Zych SF, CA "David Mueller" wrote in message ... I used some code I found in the newsgroup to remove the leading, hidden apostrophe that identifies cell contents as a string data type -- but it's too slow. Is there a faster method than the "FOR EACH ... {remove prefix character} ... NEXT" I'm using? A common scenario for me is 10 worksheets, each with 200,000 cells of data each. In addition, there is one column on each tab from which I do NOT want to remove the prefix character. I'm not sure search & replace will work for me because I have legit apostrophes in my data. Thanks in advance. David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Leading Spaces | Excel Discussion (Misc queries) | |||
Removing Hyperlinks Quickly | Excel Discussion (Misc queries) | |||
Removing leading/trailing spaces | Excel Discussion (Misc queries) | |||
Removing leading 0s | Excel Discussion (Misc queries) | |||
Removing leading spaces | Excel Programming |