I would like a UDF that removed text from cell contents and gave the result for the remaining numbers, etc. For example:
Cell A2 contains: 0.25*Pi*[Dia =](1.5^2)*[h]2
I would like the UDF to remove all instances of [......]
And in Cell B2 enter UDF(A2) and get the result 3.53 (ie, the volume).
This would allow me to keep a record in the cell of what the figures represent.
Help appreciated.
DesM
Sorry, better if Cell A2 contains: [Vol=]=0.25*Pi*[Dia =](1.5^2)*[h]2
DesM
If you used named ranges, you could enter the formula as
=PI() * Dia^2 / 4 * Hgt
... which would be pretty clear.
Entia non sunt multiplicanda sine necessitate
Yes shg and thanks, I realise this but the time to do this is too much (lots of variable values).
I end up doing the quick calc on a piece of paper and filing it. I'm trying to do away with this.
I also know of the way that text can be added to the end of a formula using the "+N(.....)"method but I want to put the text in beside the values in the formula.
try this
Please Login or Register to view this content.
Last edited by kev_; 09-05-2017 at 09:36 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
kev_, this is fantastic!
I had figured out the Do and Loop position problem and had it working before I read your mail above. And yes, your first code line now allows it to work with any A2 content.
I'll be using this twenty times a day! Gone are the scribble notes and the endless naming of cells, and the rows of eg, Dia = xxx, etc.
Help much appreciated.
DesM
You are welcome. Thanks for the rep
I am sure there is a more elegant method to get there using RegEx - but I have yet to master that!
Perhaps someone else will come along and post RegEx alternative
I'm getting greedy now but would it be a big job to get this to work:
Cell A2 contains: 0.25*Pi*[Dia =1.5]^2*[h=2] on the condition that the number would always be the only digit between the "=" and the "]"
One way might be
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Try this:
Array created listing everything to be removed
- VBA removes everything listed in array
- so you could add other dimensions (eg "r" for radius etc)
- just be careful not to remove letters "P" or "I" (or Pi will complain!)
- adding the "=" moved to the end for obvious reason!
Please Login or Register to view this content.
@xlnitwit - RegEx alternative works perfectly against text in original request
How is that amended to match request in post#8?
This should work
Please Login or Register to view this content.
xlnitwit thanks but misunderstanding: the formula that I gave (volume of cylinder) is just an example - it could be any formula and generally much longer than that one.
Are you saying you have one that does not work with that function? If so, what is it?
My apologies, it works perfectly. Thanks very much.
In fact if I put =removetext("0.25*Pi*[Dia =1.5]^2*[h=4]") into a cell, I get the result without having to use 2 cells.
Any way of dispensing with the front and end quotes?
Thanks kindly.
No, I'm afraid not. If you don't use the quotes, Excel will try and interpret the argument and get very confused.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks