I found a nice function and modified it to take out digits (and a
decimal) from a cell however don't get one line. The whole function is
------------------------------
Function ExtractDigits(cell As String) As Variant
'extract 1st continuous set of digits
'David McRitchie, 2001-09-26
Dim i As Long, flag As Long
flag = 0
ExtractDigits = ""
For i = 1 To Len(cell)
If Mid(cell, i, 1) = "0" And _
Mid(cell, i, 1) <= "9" Or _
Mid(cell, i, 1) = "." Then
flag = 1
ExtractDigits = ExtractDigits & Mid(cell, i, 1)
ExtractDigits = ExtractDigits * 1
Else
If flag = 1 Then Exit Function
End If
Next i
End Function
---------------------------------
This seems to work, however if the cell is 12.3mg it will display the
result as 123. If I comment out the ExtractDigits = ExtractDigits * 1
then it returns it as 12.3 which is what I want. So what is this
ExtractDigits = ExtractDigits * 1 doing? I can't figure out why it
changes 123 to 12.3.

Thanks,
Andrew V. Romero

multiplying a text value by a number forces it to become a number
HS


wrote in message
oups.com...
:I found a nice function and modified it to take out digits (and a
: decimal) from a cell however don't get one line. The whole function is
: ------------------------------
: Function ExtractDigits(cell As String) As Variant
: 'extract 1st continuous set of digits
: 'David McRitchie, 2001-09-26
: Dim i As Long, flag As Long
: flag = 0
: ExtractDigits = ""
: For i = 1 To Len(cell)
: If Mid(cell, i, 1) = "0" And _
: Mid(cell, i, 1) <= "9" Or _
: Mid(cell, i, 1) = "." Then
: flag = 1
: ExtractDigits = ExtractDigits & Mid(cell, i, 1)
: ExtractDigits = ExtractDigits * 1
: Else
: If flag = 1 Then Exit Function
: End If
: Next i
: End Function
: ---------------------------------
: This seems to work, however if the cell is 12.3mg it will display the
: result as 123. If I comment out the ExtractDigits = ExtractDigits * 1
: then it returns it as 12.3 which is what I want. So what is this
: ExtractDigits = ExtractDigits * 1 doing? I can't figure out why it
: changes 123 to 12.3.
:
: Thanks,
: Andrew V. Romero
:

It changes it to numeric.

This is what you want

Function ExtractDigits(cell As String) As Variant
'extract 1st continuous set of digits
'David McRitchie, 2001-09-26
Dim i As Long, flag As Long
flag = 0
ExtractDigits = ""
For i = 1 To Len(cell)
If Mid(cell, i, 1) = "0" And _
Mid(cell, i, 1) <= "9" Or _
Mid(cell, i, 1) = "." Then
flag = 1
ExtractDigits = ExtractDigits & Mid(cell, i, 1)
Else
If flag = 1 Then
ExtractDigits = ExtractDigits * 1
Exit Function
End If
End If
Next i
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I found a nice function and modified it to take out digits (and a
decimal) from a cell however don't get one line. The whole function is
------------------------------
Function ExtractDigits(cell As String) As Variant
'extract 1st continuous set of digits
'David McRitchie, 2001-09-26
Dim i As Long, flag As Long
flag = 0
ExtractDigits = ""
For i = 1 To Len(cell)
If Mid(cell, i, 1) = "0" And _
Mid(cell, i, 1) <= "9" Or _
Mid(cell, i, 1) = "." Then
flag = 1
ExtractDigits = ExtractDigits & Mid(cell, i, 1)
ExtractDigits = ExtractDigits * 1
Else
If flag = 1 Then Exit Function
End If
Next i
End Function
---------------------------------
This seems to work, however if the cell is 12.3mg it will display the
result as 123. If I comment out the ExtractDigits = ExtractDigits * 1
then it returns it as 12.3 which is what I want. So what is this
ExtractDigits = ExtractDigits * 1 doing? I can't figure out why it
changes 123 to 12.3.

Thanks,
Andrew V. Romero

While I get in theory what you are saying that the *1 changes it from
text to a number, but why does it seem to behave odd? If you run that
formula on a cell that only has 12.3, it returns 123. 12.3 is totally
different than 123 so if ExtractDigits = 12.3, then I don't see why
ExtractDigits * 1 would be 123?

Thanks,
Andrew V. Romero

Because it does it one digit at a time, so the sequence goes

extract 1 and append it, giving text "1"
multiply by 1 gives numeric 1

extract 2 and append it, giving text "12"
multiply by 1 gives numeric 12

extract the dot and append it, giving text "12."
multiply by gives numeric 12

extract the 3 and append it, giving text "123"
multiply by 1 gives 123

As you can see the behaviour is not so odd, it is just being transformed
back and forth between text and numeric.

The change I made takes each character at a time, ignoring anything not
numeric and not a dot, and creates a string of this. In the example above,
it creates a string "12.3", which is multiplied by 1 at the end, thereby
giving the correct numeric value.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
While I get in theory what you are saying that the *1 changes it from
text to a number, but why does it seem to behave odd? If you run that
formula on a cell that only has 12.3, it returns 123. 12.3 is totally
different than 123 so if ExtractDigits = 12.3, then I don't see why
ExtractDigits * 1 would be 123?

Thanks,
Andrew V. Romero

Ahh, I see, that makes sense. Thanks for going thru that explaination,
I look forward to learning more about visual basic programming. That
was really driving me crazy, and as usual, it was something fairly
simple.

Thanks,
Andrew V. Romero