Type ValUnit
tValue As Double
tUnit As String
End Type

Sub test()
Dim i As ValUnit
Dim s As String
s = " 49.0000RLS"
i.tValue = dNum(s)
i.tUnit = sStr(s)
Debug.Print i.tUnit
Debug.Print i.tValue
End Sub

Function dNum(s As String) As Double
Dim re
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[0-9.]+"
If re.test(s) Then _
dNum = CDbl(re.Execute(s)(0))
End Function

Function sStr(t As String) As String
Dim re
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[A-z]+"
If re.test(t) Then _
sStr = CStr(re.Execute(t)(0))
End Function

A1=" 49.0000RLS")
B1=dNum(A1)
C1=sStr(A1)

regards
r

--
Come e dove incollare il codice:
http://www.rondebruin.nl/code.htm

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Rick's nickname" wrote:

Using Excel 2003.

I have a module with the following code:

Type ValUnit
tValue As Double
tUnit As String
End Type

Function SeparateValueFromUnit(ByVal strInput As String) As ValUnit

Dim fromwhere As Integer
fromwhere = InStr(trim(strInput), ".") + 4
SeparateValueFromUnit.tValue = Left(trim(strInput), fromwhere)
SeparateValueFromUnit.tUnit = Mid(trim(strInput), fromwhere + 1)

End Function

If I go to the immediate window and enter:
? separatevaluefromunit(" 49.0000RLS").tunit
I get the correct response.

If I go to a cell in the worksheet and enter: (H2 Holds "
49.0000RLS")
=SeparateValueFromUnit(H2).tUnit
I get an error: "The formula you typed contains an error".

How do I return just one element (tUnit, or tValue) to this cell?