converting an unusual text string to numerical entry

Wrong formula ....see this message

The formula did not copy correctly...it was missing the leading apostrophe..The correct formula is below...The other apostropes must remain to make the formula valid

'='$PCQCHART$!TA_SRV'|LIVECHART!'DAILY;1012,1013;1003=''ABAX'',9004=1,9005=1'

Re: Wrong formula ....see this message

Hi Bill

Highlight your formulas, Push Ctrl+H and put '= in the Find what box and = in the Replace with box, push Replace all. Should do the trick.

Dave

• OzGrid Business Applications

Re: Wrong formula ....see this message

>Highlight your formulas, Push Ctrl+H and put '=
>in the Find what box and = in the Replace with box,
>push Replace all. Should do the trick.

There you have it! As I indicated in an earlier
posting dealing with these apostrophe's are not
as easy as you might think. An Edit Replace...
doesn't recognize their existence.

Re: Wrong formula ....see this message

Bill, for discussion purposes let's say that this
text string resides in cell A1.

1. Insert a new column if necessary; otherwise,
enter the formula, =A1, into cell B1.
2. With cell B1 selected perform a Copy/Paste Special
Values...
3. Select column B:B and perform an Edit Replace...
to replace instances of = with =. This forces Excel
to recognize the contents of cell B1 as a formula.
4. Once you've made this conversion the text represent-
ation of your formula in cell A1 can be eliminated.

Re: Wrong formula ....see this message

What are you talking about Mark? My suggestion works perfectly.

Dave

OzGrid Business Applications

Re: Wrong formula ....see this message

Bill
If you might have the need to do this more than once, you may wish to use a macro (apologies to Mark W for my suggesting such a thing!).
The following should work, I hope :-

Sub ConvertToValue()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
On Error GoTo e
For Each cell In rng
If cell.HasFormula = True Then
Resume Next
Else: cell = cell.Value
End If
Next cell
e:
End Sub

Re: Wrong formula ....see this message

Forgot to mention :-
Make your selection before running the macro, in your case Column A.

Alternative suggestion

Alternative suggestion :-
A might be quicker (i.e. quicker run time) to record a macro of Mark W's method (or Dave's if it works).
It should be noted, however, that under Mark W's method, if your selection contains some formulas that actually work as formulas (i.e. no leading apostrophe) then these formulas will be removed and replaced with the value only.
Celia

Re: Wrong formula ....see this message

>What are you talking about Mark? My suggestion
>works perfectly.

No, it doesn't. Open a new worksheet and enter
'=B1 into cell A1. Now, use Edit Replace... to
replace '= with = . You'll get the following
message:

"Microsoft Excel cannot find the data you're
searching for. If you are certain the data
exists in the current sheet, check what you
typed and try again."

Well, you can try 'til the cows come home, but
you'll never succeed.

Since you're unwilling...

Since you're unwilling to trust someone who has been
using Microsoft Excel for over 15 years:

http://support.microsoft.com/support/kb/articles/Q124/7/39.asp

Re: Wrong formula ....see this message

Celia, here's Microsoft's approach:

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

Re: Wrong formula ....see this message

Celia, I'm just dumbfounded that any of us are
even discussing what to do with apostrophed
text. This practice became extinct (along with
the dinosaurs), unnecessary (and inadvisable)
with the advent of Excel's Text format. Its
very existence is only justified for backward
compatibility with Lotus 1-2-3.

Re: Wrong formula ....see this message

Mark W
I think you've lost sight of the original question. I'm merely suggesting a solution to that question.
Celia

Re: Wrong formula ....see this message

Sub RemoveApostrophe() For Each CurrentCell In Selection

Mark W
I presume that you are not suggesting this code since it is incomplete. Try selecting Column A and running the code. It will loop through all 65536 cells.
Celia

Re: Wrong formula ....see this message

Mark

Copy the formula posted by Bill into cell A1 then do an edit replace.

Dave

Is saying you were wrong that had Mark ?

9820.html

Post Script

In fact, if either there are no formulas without leading apostrophes in your selection or you do not wish to retain such formulas (as assumed in Mark W's method), the desired result can be achieved with a one-line macro :-

Selection = Selection.Value

Celia

Re: Wrong formula ....see this message

I've been reading the exchange between Dave and Mark. I appriciate you guys and have learned a great deal from both of you. I often follow your suggestions to see how they work. This time, Dave's suggestion (Copy the formula posted by Bill into cell A1 then do an edit replace.) worked fine.

Re: Wrong formula ....see this message

Oh yeah?(sorry, but did enjoy being able to slip that one in). Try doing it on a formula with a leading apostrophe that has not been copied from a web site!
Celia

Re: Wrong formula ....see this message

Celia, please understand that this isn't my code.
The credit goes to Microsoft. You're right about
it examining all cells in a selection and; there- fore,
it would inadvisable to use A:A as the selection.
If I had apostrophed-text scattered all over
column A:A (which would never happen in one of my
worksheets). I'd perform an Edit Go To... Special...
Constants before executing this macro.

Re: Wrong formula ....see this message

Oh yeah? -- Thanks Celia, I enjoyed that one. I did copy it from the web site.
Bruce

Re: Wrong formula ....see this message

Hey -- I have a copywrite on that phrase! :p

Re: Wrong formula ....see this message

Oops, make that "copyright"

Re: Wrong formula ....see this message

I like that "Copyright". Do you have a copywrite on copyright also?

One more suggestion: Bill said he had 10,000 cells to modify. Why not select the range, press F2, then home, then delete (to get rig of the apostrophe), then hit control-enter?

Re: Wrong formula ....see this message

Well, let's try that.

1. Enter '=B1 into cell A1
2. Enter '=C1 into cell A2
3. Select cells A1:A2, press F2, remove the
apostrophe, and press Ctrl+Enter.

Does cell A2 contain =C1?

Re: Wrong formula ....see this message

Well, let's try that. 1. Enter '=B1 into cell A1

You get the "Copyright" award. I was wrong.

Re: Wrong formula ....see this message

Well, actually your proposal would have worked if
all 10,000 formulas were identical and didn't contain
any relative references. It's just that Ctrl+Enter
behaves like a paste or fill down... adjusting relative
references... And I figured it was very unlikely
that anyone would put 10,000 identical formulas in
a worksheet.

Re: Wrong formula ....see this message

Celia, please understand that this isn't my code.
:

Mark W
But surely you would not mess around manually with Edit/GoTo/Special/Constants when the macro can be written to handle the situation.
I do not know whether using the Intersect method or code for Edit/GoTo/Special/Constants would be quicker. If there are a lot of blank cells within the range set by the Intersect method, then Intersect would probably be slower

There was an error in the macro I posted and it does not work. It should have been :-
Sub ConvertToValue()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
On Error GoTo e
For Each cell In rng
If cell.HasFormula = False Then
cell = cell.Value
End If
Next cell
e:
End Sub

Using code re SpecialCells, the macro would be :-

Sub ConvertToValue()
Dim rng As Range, cell As Range
Set rng = Selection.SpecialCells xlCellTypeConstants, 23)
For Each cell In rng
cell = cell.Value
Next cell
End Sub

We are certainly getting a lot of mileage out of this one. All of this just to remove an apostrophe. I think it’s wonderful. How did mankind ever have the time or inclination to build a rocket for moon travel when there are serious matters such as this to attend to?
I might also add that for someone who previously claimed that the very existence of this discussion was dumbfounding, you are contributing quite a lot to it.

Celia

Re: Wrong formula ....see this message

> But surely you would not mess around
> manually...

Celia, actually, I would. What if I only wanted to execute a macro on 2 non-contiguous cells? I'd need a macro for that contigency too. All I really need to know is how to make a non-contiguous cell selection (Ctrl+click) and then use the general purpose macro supplied by Microsoft. I'd much rather rely on Excel knowledge and augment it (when absolutely necessary) with a small inventory of well crafted, general purpose macros. I consider a macro to be a modular building block that when combined with worksheet knowledge and other macros accomplish one's objective.

Then there's what I'll call the "Castaway" consideration -- in honor of Tom Hank's latest movie. If someone becomes so dependent on someone else's macros (if you've seen the movie think swiss army knives here) then they'll never be able to fend for themselves when they encounter a situation that is sufficiently different from their prior circumstances. So I guess you could say that I'd much rather teach worksheet survival skills.

Re : Automate or not

No, you wouldn't need another macro. The one macro covers all situations. > All I really need to know is how to make a non-contiguous cell selection (Ctrl+click) and then use the general purpose macro supplied by Microsoft. I'd much rather rely on Excel knowledge and augment it (when absolutely necessary) with a small inventory of well crafted, general purpose macros. I consider a macro to be a modular building block that when combined with worksheet knowledge and other macros accomplish one's objective.

Well, is this not exactly what the suggested macro does?
If Microsoft’s macro had included code that rendered user selection unnecessary, would you have deleted that part of the code and forced the user to manually select?
When do you decide when a macro is "absolutely necessary"?
My approach is to use macros to automate actions when this reduces work and makes things easier. This also is a part of "Excel Knowledge". > Then there's what I'll call the "Castaway" consideration -- in honor of Tom Hank's latest movie. If someone becomes so dependent on someone else's macros (if you've seen the movie think swiss army knives here) then they'll never be able to fend for themselves when they encounter a situation that is sufficiently different from their prior circumstances. So I guess you could say that I'd much rather teach worksheet survival skills.

Very commendable I'm sure, but we are talking here about whether to manually make a selection or have the macro do it. It’s not exactly a matter of life or death. Forcing a user(of whatever skill level) to manually select cells does not teach worksheet survival skills - it just creates unnecessary work.
I am sure that there is a vast army of Excel users like myself who have learnt a great deal about VBA by using macros supplied by others – your approach would deny a lot of such learning.
I think you are being too theoretical in applying your philosophy. It could be argued that use of Excel, or even the computer itself, is not "absolutely necessary" - but we use them in an attempt to make things easier.

Celia

Re: Re : Automate or not

Celia, I appreciate your youthful enthusiasm, but please don't put words in my mouth. Not once did I disparage "Automation". Believe me -- I'm not a Ludite.

I have no doubt that you have learned a great deal about VBA, but that's not the same as learning Excel. While I'm sure that you know a great deal about Excel that's not always the case for others. I've seen cleverly written do loops for what could be accomplished with a single Go To... Special command. I've seen users that become so dependent on macros that they couldn't even find a needed command in a pull-down menu. I've seen users like Brian (who posted on Sunday) that are so dependent on a "fix" supplied by others that they never even bother to learn how to use Excel's Help system or the precise terminology needed to describe their difficulty.

Re: Re : Automate or not

Celia, I appreciate your youthful enthusiasm, but please don't put words in my mouth. Not once did I disparage "Automation". Believe me -- I'm not a Ludite. I have no doubt that you have learned a great deal about VBA, but that's not the same as learning Excel. While I'm sure that you know a great deal about Excel that's not always the case for others. I've seen cleverly written do loops for what could be accomplished with a single Go To... Special command. I've seen users that become so dependent on macros that they couldn't even find a needed command in a pull-down menu. I've seen users like Brian (who posted on Sunday) that are so dependent on a "fix" supplied by others that they never even bother to learn how to use Excel's Help system or the precise terminology needed to describe their difficulty.

Mark W
First of all, your description of my perceived enthusiasm is accepted with thanks. I just wish that the same description could be applied to my actual age.
You may well not disparage automation - but that is the message I feel you are putting out whether intentional or not.
Regarding your other comments, I think you are getting carried away again (youthful enthusiasm?).
Whilst your comments have merit up to a point, in the context of the current topic they are merely philosophical and have strayed a long way from the original question.

All that we are discussing is the removal of a leading apostrophe from a formula - not such a big deal.
You originally suggested a non-macro solution which was fine provided the requirement was a one-off.
Your non-macro solution, however, involves too many steps if there is a need to do it regularly.
(Of course, if you want to insist on avoiding macros unless "absolutely necessary" then using your solution is fine - but why not use whatever tools available to produce a better method?)

I suggested a macro solution. You then suggested an alternative macro solution (from Microsoft).
The only difference between the two is that MS's
could take a long time to run if the user were to select many blank cells outside of the worksheet's used range, whilst mine automatically reduces the user's selection to cells within the used range. I don't see how any of your comments justify using the MS one as a matter of preference.

I hereby retire from this discussion and will leave the last word to you. I think we've just about flogged this to death.

Celia