Remove all the Numbers From a Row or Column.
Posted on March 10, 2008
Filed Under Excel Tips & Tricks, Reader Questions
| New to the World of Office, XP and Vista Tips & Tricks? |
Here are a few posts the other readers recommend you check out. |
|
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!!


If you liked this article or you thought it was helpful then you would do us a real big favour by hitting one of the below icons. Thank you!! That’s really cool and we really appreciate your support.
Comments
2 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.