Remove any letter from a referenced cell

F

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
 
P

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
 
M

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
 
F

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.
 
M

Mike H

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

Mike

FiluDlidu said:
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 said:
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
Click to expand...
Click to expand...
 
F

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.
 
M

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
 
F

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

Mike H said:
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 said:
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.
Click to expand...
Click to expand...
 
F

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
 
R

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
Click to expand...

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
 
F

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!
 
R

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!

Ron Rosenfeld said:
============================
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
Click to expand...
Click to expand...

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
 
D

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



Ron Rosenfeld said:
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 said:
============================
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
Click to expand...
Click to expand...

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
Click to expand...
 
R

Ron Rosenfeld

Using "IgnoreCase" might be another idea:
Click to expand...

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
 
Top