Excel equivalent ov VAL function

[Paul Lautman]

Apologies for cross posting, but I can't figure out which of these 2 groups
I should use.

I wish to get the number part of a string out into a cell.

I can do it with the UDF
Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

but does anyone know of an equivalent using Excel BIFs?

Also, if I put a macro into my personal.xls file, it is available to all my
sheets. How do I make a UDF similarly available?

TIA
Paul

 

[Frank Kabel]

Hi
try
=VALUE(cell_reference)
or force the conversion with
=--cell_reference

You can also put your functions in your personal.xls file: e.g.
Public function foo()
...
end function

 

[Charles Williams]

Hi Paul,

Unfortunately VALUE() only works if it can convert the whole string, as
opposed to VAL() which converts all numeric characters before the first
non-numeric.
If you need the part-string method from VAL I would recommend sticking with
your UDF.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Paul Lautman]

Hi Frank,
I guess I wasn't too clear in my original post.
I already have the function in my PERSONAL.XLS as

Public Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

However, if I try to use it in a sheet I get a #NAME? error.

If I copy the function into a module in the workbook's own project, then it
works fine.

So although the function is in PERSONAL.XLS, it is not available in the same
way that macros are.

Any ideas?

 

[Frank Kabel]

Hi Paul
have a look at the following instructions - they work for me
http://www.mvps.org/dmcritchie/excel/install.htm

 

[Jonathan Rynd]

I already have the function in my PERSONAL.XLS as

Is it in a module?