hidden formulas
Posted by Efendi on February 07, 2002 6:30 AM
1. I would like to make all formulas hidden and I tried to do it but it does not work. What I did: I clicked on "Hidden formulas" in Format - Cells - Protection. Then I put the password in Tools - Protection - Lock sheets. It does not work - why?
2. In one column I have some datas in various forms, for example "num. of check 00124", or "00124 num. of check", or "number 00124 - check" and like this. I need to extract just number from it - how to do it by some quick way?
Thank in advance:-)
Posted by Adam S. on February 07, 2002 2:34 PM
Hiya,
To your first question, I am not sure why your formula's are still visible. It sounds like your procedure listed above was fine. I was able to successfully hide all of my formulas by
-Selecting the relevant cells with formulas
-Format\Cells\Protection\<checked hidden>
-Tools\protection\protect sheet (all 3 options checked)
If you did not have "contents" checked that that would allow the formulas to be visible - but that's all I can think of for that issue.
For your second question, you listed 3 examples of the varying text data forms with an imbedded number:
-"num. of check "00124
-00124"num. of check"
-"number "00124"-check"
I doubt a formula to extract "00124" would be very short. If the first number of the string is always a 0 and is 5 characters long then you could use a combination of Find\Mid to isolate the number.
Otherwise, you may want to consider just making a copy of the range and repeating a series of Edit\Replace's. From above I'd start with replacing:
-"check" with ""
-"number" with ""
-"num." with ""
-"-" with ""
-"of" with ""
(and) -" " with ""
And basically just keep replacing any remaining text/non-numeric characters that are left with "". (Hopefully that means <10 or so edit\replace's).
Formula-wise, you could also use this Array formula (hit Ctrl+shift+enter instead of just enter after typing it- that adds the {}'s):
{=MID(A2,MATCH(0,(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)*1),0),LEN(A2)-SUM((ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)*1)))+0}
-This removes non-numeric characters from a string, it won't work with abc13-xyz345blu for example, but should work with your examples above.
(I didn't develop that monster btw, that was pulled out John Walkenbach's Excel 2000 Formula book- page 349, chapter 13 on array formula's. I'm just a passerby who happens to read a lot.)
Hope that helps
Adam S.
