Remove all the Numbers From a Row or Column.
Posted on March 10, 2008
Filed Under Excel Tips & Tricks, Reader Questions
Hi guys, I hope you all had a great weekend! I did, we hooked up with some old friends for my best friend’s birthday and we just had a great Saturday night. We had a quiet Sunday, my girl and I went to an exhibition and when we came back I had some work to do. The article that I published yesterday about using Microsoft Outlook 2000 and later as your RSS reader was one of my tasks. If you had time to grab a copy let me know okay, I’m curious how the Newsgator plugin worked for you.
Today’s Tip and Trick is requested by Gabriel and was in regard to a comment he made at the article Remove all Letters or Number from a row or Column. He asked: “what would be the VBA code if we want to delete the numbers and keep the letters?”. So Gabriel here’s your Excel Tutorial.
An Excel Tutorial to Remove all the Numbers from a Row or Column.
· Press “ALT+F11” to open the Visual Basic Editor.
· Go to the Insert Menu and open Module.
· Type in the function as mentioned below.
Function NumberOut(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
Case Else
NumberOut = NumberOut & Mid(rng.Value, i, 1)
End Select
Next i
End Function

· Go back to your Excel worksheet and select the cells B1:B5 and type in the formula =NumberOut(A1).
· And press “CTRL+ENTER”

So that’s it Gabriel. Good luck with it and thanks for your comment. I’ll see you around okay. I hope you all have a great Monday and I’ll see you all tomorrow with the next Tip and Trick. Cheers guys!!
Comments
5 Responses to “Remove all the Numbers From a Row or Column.”
Leave a Reply






Suppose i have “Joseph John 237″ in the cell and with the above formula it is reduced to “JosephJohn”. I wish for it to be “Joseph John” instead. Many thanks for the above trick. Saved me a lot of time.
Great function. Very helpful.
Similar to Kris, I would like to keep the spaces – I tried to change the code from “Case 0 To 64, 123 To 197″ to “Case 0 To 31, 33 To 64, 123 To 197″ (I looked up the ascii for space and found out it was 33), but this returned an error.
Any help you can offer would be greatly appreciated.
The tip is exactly what I want to do, but my problem is, how do I save the macro? Obviously I’m missing a step, because when I attempt to use the NumberOut formula, I’m told it is an “ambiguous name.” What am I missing?
That removed the spaces as well, which is a problem.
Try the below, this removes numbers and also commas, but you should be able to edit to remove / retain what you want
Public Function RemNum(wsText As String) As String
wsOutput = ""
For i = 1 To Len(wsText)
If Not Mid(wsText, i, 1) Like "[0-9]" And Not Mid(wsText, i, 1) = "," Then wsOutput = wsOutput & Mid(wsText, i, 1)
Next i
RemNum = wsOutput
End Function