Remove all Letters or Number from a row or Column.

Posted on October 24, 2007 
Filed Under Excel Tips & Tricks

The following Trick is one to remember. This could save u an enormous amount of time. We are going to make an automated process to delete in this case all the letters from a column.

 

To delete all the Letters from an Excel Row follow the following 5 Steps. 

  1. Press “ALT+F11” to open the Visual Basic Editor.
  2. Go to the Insert Menu and open Module.
  3. Type in the function as mentioned below.

Function LetterOut(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

LetterOut = LetterOut & Mid(rng.Value, i, 1)

End Select

Next i

End Function

Delete-Letter-From-Column-pic1

 

  1. Go back to your Excel worksheet and select the cells B1:B5 and type in the formula =LetterOut(A1).
  2. And press “Control+Enter”

Delete-Letter-From-Column-pic2

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


Buzz Up Digg Mixx
Print


Comments

7 Responses to “Remove all Letters or Number from a row or Column.”

  1. Richard on October 26th, 2007 11:47 am

    I needed this about a month ago, I was working on a list with more than 1450 values that I needed to clean. It took me more than 3 day’s, pffff. I told my boss (nicely) that he could do this next time. Maybe I will do this now, I can now impres him with my knowledge of word :p.

  2. Vincent on October 26th, 2007 11:51 am

    With this it can be done in ten minutes. I would do it the next time if I were you. It’s always good to impress you boss!

  3. Vincent on March 9th, 2008 2:23 pm

    Hi Gabriel,

    That’s a good question. I don’t know why I called it “Remove all Letters or Number from a row or Column.” Because I’m not showing you how to remove the letters. Must have been a slip of the tongue or something;) anyway, the article is coming your way. Thanks for commenting.

  4. Gabriel on March 9th, 2008 2:19 pm

    what would be the VBA code if we want to delete the numbers and keep the letters ?

    thanks a lot

  5. Will on October 27th, 2008 7:17 pm

    Thanks a bunch for this thing! It is like something that fell out of my dreams. There is one thing though. When running the function, it picks up symbols also (ie. A1= asdf: 123, =LetterOut(A1) will result in : 123). Is there a way to remove the space and punctuation too? Thanks for the great work!

  6. Effie on December 16th, 2008 6:56 am

    I would like to know the answer to the last comment about removing spaces and punctuation.

  7. Tom on December 22nd, 2008 6:09 pm

    Did just what i wanted excellent, only thing is how do i now calulate the sum of the new column, when using the normal calulation it reads the true value (the formula) as opposed to the displayed value. Any ideas?

    Thanks

Leave a Reply