Remove all Letters or Number from a row or Column.

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

New to the World of Office, XP and Vista Tips & Tricks?

  Here are a few posts the other readers recommend you check out.

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

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

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

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

  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