leave numbers only in a range

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Hello,

How can i leave only the numbers contained in a range (like: cell(1,1)= a12/r . I want it as 12 only).

Thank you
Kostas
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
The cell's data may contain anything from alphachr to symbols and control chr. From all these i want to "extract" any number exists. VBA code is better for my usage.
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon ktab

How about this as a custom function :

Code:
Function NoOnly(UsrRng As Range)
NoOnly=""
For n = 1 To Len(UsrRng.Value)
If IsNumeric(Mid(UsrRng.Value, n, 1)) Then
NoOnly = NoOnly & Mid(UsrRng.Value, n, 1)
End If
Next n
NoOnly = Val(NoOnly)
End Function

So if A1 contains : a12/r

Then : =NoOnly(A1)

Would return : 12 (as a value, not text looking like a value).

HTH

DominicB
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Kostas

Here's another take on a custom UDF route using RegExp:

Code:
Function NumOnly(txt As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "\D"
    .Global = True
End With
NumOnly = regex.Replace(txt, "")
End Function

This returns the number as a string. Replace "As String" in the function name to eg "As Long" or "As Double" if you want it as a number.

Best regards

Richard
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
EXCELLENT

(It worked with me like noonly(range("a1")); don't know if anything wrong with me, regional settings(language))

Thank you very much
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Kostas

That's because DOminic was showing you how to use the function within an Excel cell, rather than from within VBA, where, according to his function, a range reference needs to be supplied.

Best regards

Richard
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
I got it. Experimented and found out. But I want to use it in VBA so it works too.
Also your code works fine... But regexp? Is it for regular expressions? Isn't that unix or something? Works like vbscript.regexp I guess. Ancient but valuable. Didn't know you could use it in VB. Every day I learn something new here.

Thank you both
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yes, my code uses the VB Script regular expression engine (via the VBScript RegExp dll). Regular Expressions are the most powerful text serach/matching tool available to a programmer. I know that the RegEx engine in Perl (a predominantly Unix-based language) is often cited as the best and most powerful implementation, but VBScript RegExp 5.5 works very well too.

Richard
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
To expand my question is there a way that, trough VBA, i can extract the numbers/number present just before next non numeric char is present?
With an example it would be more easy:
a23/56,6: result 23
gfgf4-457/4: result:4
\45g98 result:45
2/3 result:2

Thank you
Kostas
 

Forum statistics

Threads
1,137,351
Messages
5,680,991
Members
419,948
Latest member
Sbakker1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top