Function in XL or in VBA for XL that pulls numeric digits from a t

D

Dennis

Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which has the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from string
that appear in characters_to_include, in the same order that they appear in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
 
B

Bernie Deitrick

Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP
 
D

Dennis

Thank you very much for your time and knowledge!

Dennis

Bernie Deitrick said:
Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Dennis said:
Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which has the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from string
that appear in characters_to_include, in the same order that they appear in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
 
D

Dennis

Bernie,

I know enough about this VBA code to be very dangerious. Thus , it is not
intutively obvious to how to do the inverse of your Include() function.

If you have the time and inclination, how would you code the inverse or an
Exclude() function.

EXCLUDE(string,characters_to_exclude)


Bernie Deitrick said:
Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Dennis said:
Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which has the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from string
that appear in characters_to_include, in the same order that they appear in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
 
D

Dana DeLouis

Don't know if you would find this helpful. If you can set a vba library
reference to the reference below, another option might be the following.
If you are calling this often, you would want to set a global variable to
hold the RegExp, so that it is not created with each call.

Function Include(str As String, keep As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "[^" & keep & "]+"
Include = .Replace(str, vbNullString)
End With
End Function

or in your case...

Function NumbersOnly(str As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "\D+"
NumbersOnly = .Replace(str, vbNullString)
End With
End Function


' = = = = = = =
Sub Testit()
Dim s As String
s = "123abc456def7890ghij"
Debug.Print NumbersOnly(s)
Debug.Print Include(s, "43dgz")
End Sub

returns:
1234567890
34dg

HTH
--
Dana DeLouis
Win XP & Office 2003


Dennis said:
Thank you very much for your time and knowledge!

Dennis

Bernie Deitrick said:
Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Dennis said:
Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which has the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from string
that appear in characters_to_include, in the same order that they
appear in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a
text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
Click to expand...
 
D

Dennis

Excellent Dana!

One thing I learn every day - is that I have soooo much to learn.

Thanks.

Dennis

Dana DeLouis said:
Don't know if you would find this helpful. If you can set a vba library
reference to the reference below, another option might be the following.
If you are calling this often, you would want to set a global variable to
hold the RegExp, so that it is not created with each call.

Function Include(str As String, keep As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "[^" & keep & "]+"
Include = .Replace(str, vbNullString)
End With
End Function

or in your case...

Function NumbersOnly(str As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "\D+"
NumbersOnly = .Replace(str, vbNullString)
End With
End Function


' = = = = = = =
Sub Testit()
Dim s As String
s = "123abc456def7890ghij"
Debug.Print NumbersOnly(s)
Debug.Print Include(s, "43dgz")
End Sub

returns:
1234567890
34dg

HTH
--
Dana DeLouis
Win XP & Office 2003


Dennis said:
Thank you very much for your time and knowledge!

Dennis

Bernie Deitrick said:
Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which has
the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from
string
that appear in characters_to_include, in the same order that they
appear
in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include()
function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a
text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that
would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
Click to expand...
 
D

Dana DeLouis

Hi. An "Exclude" function would require a slight modification ...

Function Exclude(str As String, Discard As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "[" & Discard & "]+"
Exclude = .Replace(str, vbNullString)
End With
End Function

If you want to just delete numbers, another option...

Function NumbersDelete(str As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "\d+"
NumbersDelete = .Replace(str, vbNullString)
End With
End Function

HTH
--
Dana DeLouis
Win XP & Office 2003


Dennis said:
Excellent Dana!

One thing I learn every day - is that I have soooo much to learn.

Thanks.

Dennis

Dana DeLouis said:
Don't know if you would find this helpful. If you can set a vba library
reference to the reference below, another option might be the following.
If you are calling this often, you would want to set a global variable to
hold the RegExp, so that it is not created with each call.

Function Include(str As String, keep As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "[^" & keep & "]+"
Include = .Replace(str, vbNullString)
End With
End Function

or in your case...

Function NumbersOnly(str As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =

With New RegExp
.Global = True
.Pattern = "\D+"
NumbersOnly = .Replace(str, vbNullString)
End With
End Function


' = = = = = = =
Sub Testit()
Dim s As String
s = "123abc456def7890ghij"
Debug.Print NumbersOnly(s)
Debug.Print Include(s, "43dgz")
End Sub

returns:
1234567890
34dg

HTH
--
Dana DeLouis
Win XP & Office 2003


Dennis said:
Thank you very much for your time and knowledge!

Dennis

:

Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which
has
the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from
string
that appear in characters_to_include, in the same order that they
appear
in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include()
function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from
a
text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function
that
would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Dennis,

Though Dana will shortly post something terrificly clever, you can use this
until then:

Function Exclude(StrInput As String, _
ExChar As String) As String

Dim i As Integer

Exclude = ""

For i = 1 To Len(StrInput)
If InStr(1, ExChar, Mid(StrInput, i, 1)) = 0 Then
Exclude = Exclude & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Dennis said:
Bernie,

I know enough about this VBA code to be very dangerious. Thus , it is not
intutively obvious to how to do the inverse of your Include() function.

If you have the time and inclination, how would you code the inverse or an
Exclude() function.

EXCLUDE(string,characters_to_exclude)


Bernie Deitrick said:
Dennis,

Put this into a codemodule, and use it like your examples.

Function Include(StrInput As String, _
IncChar As String) As String

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i
End Function

HTH,
Bernie
MS Excel MVP

Dennis said:
Using XL 2003 and 97

In another product (ACL) there is a function called Incude() which
Click to expand...
has
the
following paramaters:
INCLUDE(string,characters_to_include)

INCLUDE( ) returns a string consisting of only those characters from string
that appear in characters_to_include, in the same order that they
Click to expand...
appear
in
string.

Example also from ACL:
INCLUDE("123 any street","0123456789")
="123"

Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function]
INCLUDE("B1","0123456789")
="123456789"
In short, the function can pull all numeric information from a text
cell containing
mixed information.

Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.

If not, thoughts on how to create it?

TIA

Dennis
Click to expand...
Click to expand...
Click to expand...
 
N

Nate Oliver

Hello,
Does XL and/or VBA for XL and/or anyone have any similar function that would
work in XL? If so, I would like to have knowledge of it.
Click to expand...

While this thread is somewhat dated, another potential approach is to use a
byte array. E.g.,

Function retNumeric(ByVal myStr As String) As Double
Dim b() As Byte, i As Long, myNum As String
b = StrConv(myStr, vbFromUnicode)
For i = LBound(b) To UBound(b)
Select Case b(i)
Case 46, 48 To 57
myNum = myNum & ChrW$(b(i))
End Select
Next
retNumeric = myNum
End Function

Sub tester()
MsgBox retNumeric("A123B456C789")
End Sub

You can change the ASCII chars and function data type to adjust the parsed
return.

Regards,
Nate Oliver
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Syntax to evaluate contents of Formula cells 5
Top three XL Efficiencies 9
Using TAPI in Excel VBA 2
Copy/Export/Transfer charts from Excel to power point with VBA cod 1
XL VBA function needed for a list of items 1
Array constants in VBA Module sheet 2
Write data from one cell to another cell 1
Write data from one cell to another cell 0

Top