Any Excel experts here??
- Thread starter snoopol
- Start date
OK so to kind of ilustrate what I'm doing. I created the data for the NBA players. I used the players game logs from the NBA.COM. I imported that to excle file so I could easily update this. Then I also created a table which makses simple calculations likve average etc. So I assigned values from query to the cells in the table. For example C17=E50, C18=E51, C19=E52 and so on. The problem is that NBA.COM has players game logs done month by month. So in my table that has the values from the query thatwas imported there are symbols like PTS, REB AST between the actual numbers. I would like to get rid of those and replace it with the average from the table but I can't figure it out.
Thanks in advance for any help.
Thanks in advance for any help.
Get Numbers From Alphanumeric Text in Excel
This UDF will extract the numeric portion from a alphanumeric Text String. See Also Sort Alphanumeric Text
The Code
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com
'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
ExtractNumber = CDbl(lNum)
End Function
To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).
It should be noted that the ExtractNumber Function has 2 optional arguments (Take_decimal and Take_negative). These are both False if omitted. See the table below to see how alphanumeric text is treated.
Alphanumeric Text Formula Result
a-bg-12909- =ExtractNumber(A1,,TRUE) -12909
a-bg-12909- =ExtractNumber(A2) 12909
a.a1.2... =ExtractNumber(A3,TRUE) 1.2
a.a1.2... =ExtractNumber(A4) 12
a.a-1.2?. =ExtractNumber(A5,TRUE,TRUE) -1.2
abg1290.11 =ExtractNumber(A6,TRUE) 1290.11
abg129013Agt =ExtractNumber(A7) 129013
abg129012 =ExtractNumber(A8) 129013
This UDF will extract the numeric portion from a alphanumeric Text String. See Also Sort Alphanumeric Text
The Code
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com
'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
ExtractNumber = CDbl(lNum)
End Function
To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).
It should be noted that the ExtractNumber Function has 2 optional arguments (Take_decimal and Take_negative). These are both False if omitted. See the table below to see how alphanumeric text is treated.
Alphanumeric Text Formula Result
a-bg-12909- =ExtractNumber(A1,,TRUE) -12909
a-bg-12909- =ExtractNumber(A2) 12909
a.a1.2... =ExtractNumber(A3,TRUE) 1.2
a.a1.2... =ExtractNumber(A4) 12
a.a-1.2?. =ExtractNumber(A5,TRUE,TRUE) -1.2
abg1290.11 =ExtractNumber(A6,TRUE) 1290.11
abg129013Agt =ExtractNumber(A7) 129013
abg129012 =ExtractNumber(A8) 129013
Hey marine thanks for your help. Looks kind of complicated to me. Any chance I could contact you via MSN or other messenger or maybe email???
- Nov 13, 2009
- 3,322
- 116
- 0
do a quick google search on what you want done, probably something along the lines of
"remove text from numbers in excel table"
and you'll find oodles of results and variations to help you get done what you want.
the easiest way is probably going to be writing a macro and doing it yourself.
I kind of figured it out. Probably there's a better way of doing that but it works so I'm fine with that. Thanks for your input.
