[Quickly!] Removing PrefixCharacter (i.e. leading, hidden apostrop

[David Mueller]

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

 

[Don Guillett]

What does [Quickly!] mean?

 

[Tim Zych]

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..

 

[David Mueller]

Quickly remove the prefix character, as opposed to the cell-by-cell method
which is too slow when workbooks contain 200,000+ cells of data

Or do you mean "how quickly" ... I'd live with a couple minutes considering
it's about 20+ minutes going cell-by-cell.

What does [Quickly!] mean?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

 

[Gary''s Student]

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.

 

[David Mueller]

Wow! That's quick! Thanks.

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..