Remove any letter from a referenced cell

[FiluDlidu]

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

 

[Pete_UK]

There are numerous examples in the newsgroups of user-defined functions to
strip characters from text - do a bit of Google-ing !!

Hope this helps.

Pete

 

[Mike H]

Hi,

Alt + f11 to open VB editor. Right click 'this workbook' insert function and
paste this in

Function extractthings(rng As range)
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "[\d\-\&]"
End With
Outstring = ""
Set Collection = RegExp.Execute(rng)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
extractthings = Outstring
Next
End Function

then in b1 insert the formula

=extractthings(a1)

Mike

 

[FiluDlidu]

Hi Mike,

This sounds very promising, but I only have "user form", "module" and "class
module" in the "insert" sub-menu available when I right-click on 'this
workbook'...

Which one should I choose or what should I do to get to where you think I
should be???

Thanks.

 

[Mike H]

apologies, it's insert MODULE and paste it in there

Mike

Hi Mike,

This sounds very promising, but I only have "user form", "module" and "class
module" in the "insert" sub-menu available when I right-click on 'this
workbook'...

Which one should I choose or what should I do to get to where you think I
should be???

Thanks.

Hi,

Alt + f11 to open VB editor. Right click 'this workbook' insert function and
paste this in

Function extractthings(rng As range)
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "[\d\-\&]"
End With
Outstring = ""
Set Collection = RegExp.Execute(rng)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
extractthings = Outstring
Next
End Function

then in b1 insert the formula

=extractthings(a1)

Mike

 

[FiluDlidu]

Thank you so much Mike!

I've been wondering for a few years now how to create a function in Excel
and cursing at the impossibility to use regular expressions.

You have way more than answered my question: you also showed me
possibilities I was eager to know but didn't know where to look for and how
to get started.

 

[Mike H]

Your welcome.

I noticed after you had included the space in your required output. If you
do want spaces output then change the pattern to
..Pattern = "[\d\-\&\?\"" ""]"

Mike

 

[FiluDlidu]

Perhaps a bit off-subject, but it's my thread so I guess I can do what I want
from it (can't I?)...

How would you cut out anything beyond the first set of, say numbers, in say
the following string (?):
aBc123Def45
(from the preceding I would like to get "123" only, and drop "45")

Is it too much asking??

Your welcome.

I noticed after you had included the space in your required output. If you
do want spaces output then change the pattern to
.Pattern = "[\d\-\&\?\"" ""]"

Mike

Thank you so much Mike!

I've been wondering for a few years now how to create a function in Excel
and cursing at the impossibility to use regular expressions.

You have way more than answered my question: you also showed me
possibilities I was eager to know but didn't know where to look for and how
to get started.

 

[FiluDlidu]

Found it!

Function trimfuss(rng As Range)
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "\d{1,}"
End With
Outstring = ""
Set Collection = RegExp.Execute(rng)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
trimfuss = Outstring
Exit For
Next
End Function

 

[Ron Rosenfeld]

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

 

[FiluDlidu]

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]

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!

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

 

[Dana DeLouis]

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

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!

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

 

[Ron Rosenfeld]

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