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

 

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


Buzz Up Digg Mixx
Print


Comments

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

  3. Dan on August 19th, 2009 5:55 pm

    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?

  4. Ryan Kennedy on March 30th, 2010 5:46 am

    That removed the spaces as well, which is a problem.

  5. Will Simpson on June 1st, 2010 4:37 pm

    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

Leave a Reply