I found a function which seperates out numbers from letters. I
modified the formula to keep decimal points in. The formula works
well, however, the function came with a line that I don't understand.
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 'not sure what this does?
but 12.3=123 with on
Else
If flag = 1 Then Exit Function
End If
Next i
End Function
-----------------------------
I don't see what the purpose of the ExtractDigits = ExtractDigits * 1
is and I don't get what it is doing. If it is enabled, like it was in
the original formula, then if the cell is 12.3mg it returns 123 while
if it is disabled, then it returns 12.3. How is this line removing the
decimal point?

Thanks,
Andrew V. Romero

PS: Sorry if this message comes thru multiple times, Google groups
seems to be acting up.

Ignore, see the slightly older thread. There was apparently over an
hour delay in posting messages this morning....
-Andrew

Testing your function with the *1 in it does this

? ExtractDigits("abcd12.3efgh")
12.3

so the problem is (besides your post with 4 responses which you ignored and
wasted people's time - however, that is an indicator) that you screwed up
and formatted your cell not to show decimal places.

It works without the *1 because then the function returns a text string and
as many know, a text string is not formatted by a number format.

When you apply the *1, it converts the result to a number which is affected
by the formatting of the cell. Format your cell to general and a light bulb
should come on.

In any event, Ron Rosenfeld suggested a much simpler function which should
work for the situation you describe (but it would also be affected by cell
formatting).

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
I found a function which seperates out numbers from letters. I
modified the formula to keep decimal points in. The formula works
well, however, the function came with a line that I don't understand.
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 'not sure what this does?
but 12.3=123 with on
Else
If flag = 1 Then Exit Function
End If
Next i
End Function
-----------------------------
I don't see what the purpose of the ExtractDigits = ExtractDigits * 1
is and I don't get what it is doing. If it is enabled, like it was in
the original formula, then if the cell is 12.3mg it returns 123 while
if it is disabled, then it returns 12.3. How is this line removing the
decimal point?

Thanks,
Andrew V. Romero

PS: Sorry if this message comes thru multiple times, Google groups
seems to be acting up.

for completeness, I had changed the location of the *1 - but the formatting
is another possibility.

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 Exit Function
End If
Next i
ExtractDigits = ExtractDigits * 1
End Function

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Testing your function with the *1 in it does this

? ExtractDigits("abcd12.3efgh")
12.3

so the problem is (besides your post with 4 responses which you ignored
and
wasted people's time - however, that is an indicator) that you screwed up
and formatted your cell not to show decimal places.

It works without the *1 because then the function returns a text string
and
as many know, a text string is not formatted by a number format.

When you apply the *1, it converts the result to a number which is
affected
by the formatting of the cell. Format your cell to general and a light
bulb
should come on.

In any event, Ron Rosenfeld suggested a much simpler function which should
work for the situation you describe (but it would also be affected by cell
formatting).

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
I found a function which seperates out numbers from letters. I
modified the formula to keep decimal points in. The formula works
well, however, the function came with a line that I don't understand.
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 'not sure what this does?
but 12.3=123 with on
Else
If flag = 1 Then Exit Function
End If
Next i
End Function
-----------------------------
I don't see what the purpose of the ExtractDigits = ExtractDigits * 1
is and I don't get what it is doing. If it is enabled, like it was in
the original formula, then if the cell is 12.3mg it returns 123 while
if it is disabled, then it returns 12.3. How is this line removing the
decimal point?

Thanks,
Andrew V. Romero

PS: Sorry if this message comes thru multiple times, Google groups
seems to be acting up.

Thanks for the information. It is (as Bob Phillips mentioned in my
other post- again sorry for the multple posting, no messages were being
posted for about 2 hrs) that each digit was being multiplied by 1 so
the 12. * 1 = 12, then the next digit came along 123*1=123. I am
pretty familier with typical excel things, such as formatting, but am
just starting to get a grasp of the power of VB in excel. Looking
foward to learning more about it. Hopefully soon I will be able to be
creating my own instead of modifying other's functions.

-Andrew V. Romero