If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Alphanumeric udf question.
Guys I found a code to extract numeric, decimal or negative values from a
alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. Here is the code for your reference: 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 |
#2
|
|||
|
|||
Alphanumeric udf question.
I got some really good responses to a similar question in:
http://www.microsoft.com/communities...0-2dddf0713314 -- Gary''s Student - gsnu200801 "K" wrote: Guys I found a code to extract numeric, decimal or negative values from a alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. Here is the code for your reference: 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 |
#3
|
|||
|
|||
Alphanumeric udf question.
On Wed, 27 Aug 2008 14:38:03 -0700, K wrote:
Guys I found a code to extract numeric, decimal or negative values from a alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. You could use this array formula (entered with ctrl-shift-enter instead of just with enter): =SUM(IF(LEFT(rng,2)="AL",--MID(rng,FIND(" ",rng)+1,255))) --ron |
#4
|
|||
|
|||
Alphanumeric udf question.
How do I make the code to be a 2D??
"Ron Rosenfeld" wrote: On Wed, 27 Aug 2008 14:38:03 -0700, K wrote: Guys I found a code to extract numeric, decimal or negative values from a alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. You could use this array formula (entered with ctrl-shift-enter instead of just with enter): =SUM(IF(LEFT(rng,2)="AL",--MID(rng,FIND(" ",rng)+1,255))) --ron |
#5
|
|||
|
|||
Alphanumeric udf question.
Ron, the sumproduct I wrote for you is a simplified version of it. I can't
use the sum if because then the formula is getting too long. "Ron Rosenfeld" wrote: On Wed, 27 Aug 2008 14:38:03 -0700, K wrote: Guys I found a code to extract numeric, decimal or negative values from a alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. You could use this array formula (entered with ctrl-shift-enter instead of just with enter): =SUM(IF(LEFT(rng,2)="AL",--MID(rng,FIND(" ",rng)+1,255))) --ron |
#6
|
|||
|
|||
Alphanumeric udf question.
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, _ Optional Take_negative As Boolean) As Variant Dim cell As Range 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 Dim aryNumbers As Variant Dim aryIndex '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' ReDim aryNumbers(1 To rCell.Rows.Count) aryIndex = 1 For Each cell In rCell sText = cell lNum = "" 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 aryNumbers(aryIndex) = CDbl(lNum) aryIndex = aryIndex + 1 Next cell ExtractNumber = Application.Transpose(aryNumbers) End Function and use like so =SUMPRODUCT(--(LEFT(A1:A4,2)="AL"),--(ExtractNumber(A1:A4,,TRUE))) -- __________________________________ HTH Bob "K" wrote in message ... Ron, the sumproduct I wrote for you is a simplified version of it. I can't use the sum if because then the formula is getting too long. "Ron Rosenfeld" wrote: On Wed, 27 Aug 2008 14:38:03 -0700, K wrote: Guys I found a code to extract numeric, decimal or negative values from a alphanumeric value. It works great, my problem is that I want to use it for an array in a sumproduct. For example, AL 8 AL 2 NI 3 AL 2 I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1: A4,,TRUE))) but I'm getting a #value error because the extract number UDF doens't allow me to take an array. You could use this array formula (entered with ctrl-shift-enter instead of just with enter): =SUM(IF(LEFT(rng,2)="AL",--MID(rng,FIND(" ",rng)+1,255))) --ron |
#7
|
|||
|
|||
Alphanumeric udf question.
On Wed, 27 Aug 2008 20:46:00 -0700, K wrote:
How do I make the code to be a 2D?? What do you mean by "be a 2D"? I don't find any reference to 2D formula in help. --ron |
#8
|
|||
|
|||
Alphanumeric udf question.
On Wed, 27 Aug 2008 20:47:00 -0700, K wrote:
Ron, the sumproduct I wrote for you is a simplified version of it. I can't use the sum if because then the formula is getting too long. Well, without more specifications, I can't help. But I would not expect that the SUM(IF construction would be significantly longer than the SUMPRODUCT construction. --ron |
#9
|
|||
|
|||
Alphanumeric udf question.
I have a similar question, but what I am looking for is:
I have several cells with comments and I would like to extract only an alphanumeric part number that follows this format (2letters followed by 5 numbers): ##AAAAA Example: ST12345 The problem is that I am trying to use SEARCH function but if there is any word that contains ST will give me the wrong MID point. Example of a comment: "Change Request Main_20847 - ST11223 Machine Holder/ Blade Tip Grinder" I am looking for extracting only ST11223 from the text above, but the word "Request" also contains ST on it. I can do that in MS Access, but I can't figure it out in Excel. Thanks Rafael Azevedo "Ron Rosenfeld" wrote: On Wed, 27 Aug 2008 20:47:00 -0700, K wrote: Ron, the sumproduct I wrote for you is a simplified version of it. I can't use the sum if because then the formula is getting too long. Well, without more specifications, I can't help. But I would not expect that the SUM(IF construction would be significantly longer than the SUMPRODUCT construction. --ron |
#10
|
|||
|
|||
Alphanumeric udf question.
Rafael Azevedo wrote...
I have a similar question, but what I am looking for is: In this case it would have been best if you had started a new thread/ topic and perhaps included a link to this thread rather than replying in this thread. I have several cells with comments and I would like to extract only an alphanumeric part number that follows this format (2letters followed by 5 numbers): .... The problem is that I am trying to use SEARCH function but if there is any word that contains ST will give me the wrong MID point. Example of a comment: "Change Request Main_20847 - ST11223 Machine Holder/ Blade Tip Grinder" I am looking for extracting only ST11223 from the text above, but the word "Request" also contains ST on it. .... Would it be correct to say the 'ST' you want to find would always be preceded by a space and followed by 5 decimal numerals then another space? If so, regular expressions would be easiest, but they require either VBA or add-ins. But there's a way to do this using just built- in functions and a defined name. Define seq referring to the formula =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1)) Then, if the record you're parsing were in cell C3, try the following array formula. =MID(C3,MATCH(9,MMULT(--(ABS(CODE(MID(" "&UPPER(C3)&REPT(" ",255), seq+{0,1,2,3,4,5,6,7,8},1))-{32,83,84,52.5,52.5,52.5,52.5,52.5,32}) {1,1,1,5,5,5,5,5,1}),{1;1;1;1;1;1;1;1;1}),0),7) If you're willing to go the add-in/reguar expression route, download and install Laurent Longre's MOREFUNC.XLL add-in from http://xcell05.free.fr/morefunc/english/index.htm and use its REGEX.MID function as follows. =REGEX.MID(C3,"\bST\d{5}\b",1,0) |
|
Thread Tools | |
Display Modes | |
|
|