Excel equivalent ov VAL function

  • Thread starter Thread starter Paul Lautman
  • Start date Start date
P

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
 
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
 
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
 
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?
 
Back
Top