Create new function in Excel

[ssingh75]

Good noon to All,

Can anyone help me to create a function to remove number from column A in Excel.
e.g., Create a new function called "NoNumber"


Susheel
Excel,Access - 2007

 

[ssingh75]

Thanks again!

How can i revert it to get only number.

e.g.
=Onlynumber("Abha123y9")
Ans: 1239

Susheel
Excel,Access - 2007

 

[VoG]

Try

Function OnlyNumber(r As Range) As Long
Dim i As Long, x As String
For i = 1 To Len(r.Value)
    If Asc(Mid(r.Value, i, 1)) >= 48 And Asc(Mid(r.Value, i, 1)) <= 57 Then x = x & Mid(r.Value, i, 1)
Next i
OnlyNumber = Val(x)
End Function

 

[ssingh75]

Great!!

You are really Genius>>>>>

Susheel
Excel,Access - 2007

 

[Peter_SSs]

Here's an alternative you may also wish to consider. Whilst the written code is not as short, it doesn't require looping through each character in each cell.

It handles both cases by the use of an optional second argument in the fuction. If the second argument is TRUE or 1 or omitted, the function returns the digits from the cell value. If the second argument is FALSE or 0 the function returns the non-digits from the cell value.

I'm not sure if it will be possible with your data or what result you would want, but this function returns a null string if the target cell contains no digits. See rows 2 & 3 in my sample below. VoG's code returns 0 for that type of cell.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> Separate(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> Numbers <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN>)<br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        RegEx.Global = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    RegEx.Pattern = IIf(Numbers, "\D", "\d")<br>    t = RegEx.Replace(s, "")<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    t = <SPAN style="color:#00007F">CLng</SPAN>(t)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    Separate = t<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>


Example of use in the sheet. All formulas copied down.

Excel Workbook
	A	B	C	D	E	F
1	Abha123y9	1239	1239	1239	Abhay	Abhay
2
3	abc d				abc d	abc d
4	575	575	575	575
Separate Text & Digits