Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 : |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Digits Questions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I extract the last three digits from each cell in column? | Excel Discussion (Misc queries) | |||
To extract the number if there is niumeric in right most digits | Excel Discussion (Misc queries) | |||
how to extract digits from a row of numbers | Excel Worksheet Functions | |||
I need to extract the first 3 digits from a cell (ex: AAG12345)? | Excel Worksheet Functions | |||
how do I extract hex digits in a cell and convert to binary | Excel Worksheet Functions |