Remove any letter from a referenced cell
 

On Thu, 20 Mar 2008 12:11:08 -0700, FiluDlidu
wrote:

Hi all,

I would like to know if there is any easy way to filter out the letters off
a reference.

If A1 contains...
aBcD123e-FgH 45i&
I would like a formula that would return the following in B1:
123- 45&

Any idea???

Thanks in advance for any thinking (even fruitless), comments and answers,
Félix

Somewhat different approach to remove the letters:

============================
Option Explicit
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]+"
RemAlpha = re.Replace(str, "")
End Function
============================
--ron

Remove any letter from a referenced cell
 

I'm not sure how exactly it works, but it works.

That's too bad because I would otherwise play on variations of the same
thing and use it for other applications.

Anyways, thanks for your reply!

"Ron Rosenfeld" wrote:
============================
Option Explicit
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]+"
RemAlpha = re.Replace(str, "")
End Function
============================
--ron

Remove any letter from a referenced cell
 

On Thu, 20 Mar 2008 17:52:00 -0700, FiluDlidu
wrote:

I'm not sure how exactly it works, but it works.

That's too bad because I would otherwise play on variations of the same
thing and use it for other applications.

Anyways, thanks for your reply!

"Ron Rosenfeld" wrote:
============================
Option Explicit
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]+"
RemAlpha = re.Replace(str, "")
End Function
============================
--ron


It works by doing a Replace -- it replaces any character in the class {A-Za-z]
-- which means any letter -- with a null string, or nothing.

In other words, it is doing exactly what you requested -- "Remove any letter
from a referenced cell".

In the example Mike gave you, he decided to keep the non-letters that were in
your cell -- digits, hyphens and the ampersand, and then he came back to keep
the <spaces.

It seems simpler to me to just eliminate the letters, since that was what you
had specifically requested.

So that is what that regex does.

--ron

Remove any letter from a referenced cell
 

re.Pattern = "[A-Za-z]+"

Using "IgnoreCase" might be another idea:

Function RemAlpha(str As String) As String
With CreateObject("VbScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "[A-Z]"
RemAlpha = .Replace(str, vbNullString)
End With
End Function

--
Dana DeLouis



"Ron Rosenfeld" wrote in message
...
On Thu, 20 Mar 2008 17:52:00 -0700, FiluDlidu
wrote:

I'm not sure how exactly it works, but it works.

That's too bad because I would otherwise play on variations of the same
thing and use it for other applications.

Anyways, thanks for your reply!

"Ron Rosenfeld" wrote:
============================
Option Explicit
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]+"
RemAlpha = re.Replace(str, "")
End Function
============================
--ron


It works by doing a Replace -- it replaces any character in the class
{A-Za-z]
-- which means any letter -- with a null string, or nothing.

In other words, it is doing exactly what you requested -- "Remove any
letter
from a referenced cell".

In the example Mike gave you, he decided to keep the non-letters that were
in
your cell -- digits, hyphens and the ampersand, and then he came back to
keep
the <spaces.

It seems simpler to me to just eliminate the letters, since that was what
you
had specifically requested.

So that is what that regex does.

--ron

Remove any letter from a referenced cell
 

On Thu, 20 Mar 2008 21:15:53 -0400, "Dana DeLouis"
wrote:

Using "IgnoreCase" might be another idea:

Sometimes I use it, and sometimes not, depending on my mood <g.

I tend to use it more when I'm dealing with discrete words that may or may not
be capitalized, rather than using the set of all available letters.

After all, [A-Za-z] is fewer keystrokes than .ignorecase=true

:-)

But if I have particular words to pick out, that may or may not be in one case,
then it is fewer keystrokes to use the .ignorecase property.
--ron