Remove any letter from a referenced cell
 

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" wrote:

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

Remove any letter from a referenced cell
 

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

Mike

"FiluDlidu" wrote:

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" wrote:

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

Remove any letter from a referenced cell
 

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.

Remove any letter from a referenced cell
 

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" wrote:

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.

Remove any letter from a referenced cell
 

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" wrote:

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" wrote:

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.

Remove any letter from a referenced cell
 

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


"FiluDlidu" wrote:

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