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

 

[VoG]

Maybe this

Sub NoNumber()
Columns("A").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End Sub

 

[adam087]

What would the data in column A look like? What would you like to be left with?

Thanks
Adam

 

[ssingh75]

Thanks!

But i am looking for the function which i can apply for any column in Sheet.

Susheel
Excel,Access - 2007

 

[VoG]

Try

Sub NoNumber()
Dim r As Range
Set r = Application.InputBox("Click in the column", Type:=8)
r.EntireColumn.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End Sub

 

[ssingh75]

Thanks!

But again i am not satisfied...As i said i am looking for the function.

like..Sum, Product, etc.

NoNumber should be as same to apply any cell of Activeworkbooks worksheet.

Susheel
Excel,Access - 2007

 

[VoG]

A function can only return a value to the cell that it is called from. It cannot change the Excel environment.

 

[ssingh75]

I think their is some confusion...Let me explain

Cell A1 contain "Abha123Y9" as text with number

If i use the function say Nonumber then it will only return me Abhay as ans
e.g.
=Nonumber("Abha123y9")
Ans: Abhay

Hope this will help for better understanding.

Susheel
Excel,Access - 2007

 

[VoG]

Try

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

Excel Workbook
	A	B	C
1	Abha123Y9		AbhaY
Sheet3

 

[ssingh75]

Great!!!

This is what i was looking...

Thank you so much..

Susheel
Excel,Access - 2007