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

 

Excel-tip-trick-pic1

 

·         Go back to your Excel worksheet and select the cells B1:B5 and type in the formula =NumberOut(A1).

·         And press “CTRL+ENTER”

 

Excel-tip-trick-pic2

 

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!!

 

Do you want to see more? This is related...

Invite your friends to the World of Office.
Did you ever thought about publishing your own eBook or PDF report?
Invite your friends to the World of Office.
Grab the Easy PDF Toolkit, it was never this easy! If you invite your friends and we will receive at least 3 visitors then you will receive a free copy of this amazing kit and that is including all the cool bonuses. Invite your Friends here!


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.”

  1. Kris on February 16th, 2009 7:45 pm

    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.

  2. sdg on April 27th, 2009 6:46 pm

    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.

Leave a Reply