leave numbers only in a range

[ktab]

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

 

[Peter_SSs]

ktab

You haven't told us much about the data or whether you want a formula or VBA. However, see if this thread will help.

http://www.mrexcel.com/board2/viewtopic.php?t=173337

 

[ktab]

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]

Good afternoon ktab

How about this as a custom function :

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]

Hi Kostas

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

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]

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]

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]

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]

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]

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