Alphanumeric udf question.

[K]

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.

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

 

[Gary''s Student]

I got some really good responses to a similar question in:

http://www.microsoft.com/communitie...&p=1&tid=a692d62d-25e1-4a79-9820-2dddf0713314

 

[Ron Rosenfeld]

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

 

[K]

How do I make the code to be a 2D??

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

 

[K]

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.

 

[Bob Phillips]

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)))

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Rafael Azevedo]

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

 

[Harlan Grove]

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)

 

[Ron Rosenfeld]

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

It can be done in native Excel, but the formula would be quite complex.

Being "lazy", I have installed on my system Longre's free morefunc.xll add-in
(from http://xcell05.free.fr/

and would then use this formula:

=REGEX.MID(A1,"[A-Z]{2}\d{5}")

The "regular expression" pattern will extract the first substring in A1 that
consists of two capital letters followed by 5 digits.

One could add other parameters; or require two specific letters, or whatever.

If the substring should always be separated from the rest of the string, then:

=REGEX.MID(A1,"\b[A-Z]{2}\d{5}\b") might be more robust.

If your strings might be longer than 255 characters, this approach won't work
but I would just write the function as a VBA routine.
--ron