VBA fuctions

[mevikasjain]

Create a custom vba function in cell which will extract only the numbers from cell shaded with yellow (the result should be 342356 in the below case)


a3b423fgh56


PLs help me...

 

[VoG]

Try like this

Function nos(r As Range) As Long
Dim i As Long, s As String
For i = 1 To Len(r.Value)
    If IsNumeric(Mid(r.Value, i, 1)) Then s = s & Mid(r.Value, i, 1)
Next i
If s = "" Then
    nos = 0
Else
    nos = CLng(s)
End If
End Function

Excel Workbook
	A	B
1	a3b423fgh56	342356
Sheet2

 

[mikerickson]

http://www.mrexcel.com/forum/showthread.php?t=560021

 

[Rick Rothstein]

Here is a function (it can be used as a UDF if needed) that will do what you asked...

Function Digits(ByVal S As String) As Long
  Dim x As Long
  For x = 1 To Len(S)
    If Not Mid(S, x, 1) Like "#" Then Mid(S, x) = " "
  Next
  Digits = Replace(S, " ", "")
End Function

 

[Michael M]

Posted a number of times before, it's assignment time !!

http://www.mrexcel.com/forum/showthread.php?t=560077

 

[Rick Rothstein]

Posted a number of times before, it's assignment time !!

In that case, I hope the OP chooses to use my posted function... his/her instructor will surely know they didn't come up with that code on their own.

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] ExtractDigits(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Application.Volatile
    [COLOR="Blue"]Dim[/COLOR] m [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Global = [COLOR="Blue"]True[/COLOR]
        .Pattern = "\d+"
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] m [COLOR="Blue"]In[/COLOR] .Execute(Str)
            ExtractDigits = ExtractDigits & m.Value
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]